Checking if txtbox is null and DCount parameter

Greetings,

I have the following code assigned to previous record navigation button (See 
below) :-

How do I use the Is not null ??
Testing the code the Me.txtAwardId is populated but during the DCount 
execution I get the error "The expression you entered as a query parameter 
produced the error: 'StrAward'

Can anyone assist? Thanks


VBA code attached to button.

DoCmd.RunCommand acCmdRecordsGoToPrevious

Dim SAwards As Integer
Dim StrAward As String

'IsNotNull (Forms![frmCreateAward]!txtAwardId

   StrAward = Me.txtAwardId

   SAwards = DCount("[StudentID]", "tblStudent", "[AwardId] = StrAward")
    Me.lblAwardStudentCount.Caption = "Total number of Awards enrolled on 
the Award is : " & SAwards

'End If

Me.lblRecordNumber.Caption = "Award Record number: " & [CurrentRecord] 


0
Rob
2/18/2008 7:14:11 PM
access 16762 articles. 3 followers. Follow

3 Replies
445 Views

Similar Articles

[PageSpeed] 23

The syntax for the DCount criteria is:
DCount("[StudentID]", "tblStudent", "[AwardId] = " " " & StrAward & " " " ")
or
DCount("[StudentID]", "tblStudent", "[AwardId] = ' " & StrAward & " ' ")
Note that the sapeces between the quote marks are for clarity here, and are 
not included in the actual expression.

If you are trying to test first to see if txtAwardID is not null:

If Not IsNull(Me.txtAwardID) Then
    strAward = Me.txtAwardId
    SAwards = DCount("[StudentID]", "tblStudent", "[AwardId] = """ & 
StrAward & """")
    Me.lblAwardStudentCount.Caption = "Total rewards: " & SAwards
End If

Some lines of your code seem to have an apostrophe in front, which causes 
the line of code to be ignored.  I don't know if it is a typo or what, but 
since I see it I am making the comment.

"Rob W" <Whllrob@aol.com> wrote in message 
news:OnTWQKmcIHA.4712@TK2MSFTNGP04.phx.gbl...
> Greetings,
>
> I have the following code assigned to previous record navigation button 
> (See below) :-
>
> How do I use the Is not null ??
> Testing the code the Me.txtAwardId is populated but during the DCount 
> execution I get the error "The expression you entered as a query parameter 
> produced the error: 'StrAward'
>
> Can anyone assist? Thanks
>
>
> VBA code attached to button.
>
> DoCmd.RunCommand acCmdRecordsGoToPrevious
>
> Dim SAwards As Integer
> Dim StrAward As String
>
> 'IsNotNull (Forms![frmCreateAward]!txtAwardId
>
>   StrAward = Me.txtAwardId
>
>   SAwards = DCount("[StudentID]", "tblStudent", "[AwardId] = StrAward")
>    Me.lblAwardStudentCount.Caption = "Total number of Awards enrolled on 
> the Award is : " & SAwards
>
> 'End If
>
> Me.lblRecordNumber.Caption = "Award Record number: " & [CurrentRecord]
> 

0
BruceM
2/18/2008 7:26:04 PM
Thanks for the feedback will now act upon it.

Oh yes, I was aware I was commenting code out, purpose was to test other 
lines of code bypassing the syntax errors in the others.

Thanks again.
Rob

"BruceM" <bamoob@yawhodotcalm.not> wrote in message 
news:ONNKeQmcIHA.1204@TK2MSFTNGP03.phx.gbl...
> The syntax for the DCount criteria is:
> DCount("[StudentID]", "tblStudent", "[AwardId] = " " " & StrAward & " " " 
> ")
> or
> DCount("[StudentID]", "tblStudent", "[AwardId] = ' " & StrAward & " ' ")
> Note that the sapeces between the quote marks are for clarity here, and 
> are not included in the actual expression.
>
> If you are trying to test first to see if txtAwardID is not null:
>
> If Not IsNull(Me.txtAwardID) Then
>    strAward = Me.txtAwardId
>    SAwards = DCount("[StudentID]", "tblStudent", "[AwardId] = """ & 
> StrAward & """")
>    Me.lblAwardStudentCount.Caption = "Total rewards: " & SAwards
> End If
>
> Some lines of your code seem to have an apostrophe in front, which causes 
> the line of code to be ignored.  I don't know if it is a typo or what, but 
> since I see it I am making the comment.
>
> "Rob W" <Whllrob@aol.com> wrote in message 
> news:OnTWQKmcIHA.4712@TK2MSFTNGP04.phx.gbl...
>> Greetings,
>>
>> I have the following code assigned to previous record navigation button 
>> (See below) :-
>>
>> How do I use the Is not null ??
>> Testing the code the Me.txtAwardId is populated but during the DCount 
>> execution I get the error "The expression you entered as a query 
>> parameter produced the error: 'StrAward'
>>
>> Can anyone assist? Thanks
>>
>>
>> VBA code attached to button.
>>
>> DoCmd.RunCommand acCmdRecordsGoToPrevious
>>
>> Dim SAwards As Integer
>> Dim StrAward As String
>>
>> 'IsNotNull (Forms![frmCreateAward]!txtAwardId
>>
>>   StrAward = Me.txtAwardId
>>
>>   SAwards = DCount("[StudentID]", "tblStudent", "[AwardId] = StrAward")
>>    Me.lblAwardStudentCount.Caption = "Total number of Awards enrolled on 
>> the Award is : " & SAwards
>>
>> 'End If
>>
>> Me.lblRecordNumber.Caption = "Award Record number: " & [CurrentRecord]
>>
> 


0
Rob
2/18/2008 7:56:37 PM
Thanks for the advice, all code runs without errors.

"Rob W" <Whllrob@aol.com> wrote in message 
news:eflq%23hmcIHA.6080@TK2MSFTNGP05.phx.gbl...
> Thanks for the feedback will now act upon it.
>
> Oh yes, I was aware I was commenting code out, purpose was to test other 
> lines of code bypassing the syntax errors in the others.
>
> Thanks again.
> Rob
>
> "BruceM" <bamoob@yawhodotcalm.not> wrote in message 
> news:ONNKeQmcIHA.1204@TK2MSFTNGP03.phx.gbl...
>> The syntax for the DCount criteria is:
>> DCount("[StudentID]", "tblStudent", "[AwardId] = " " " & StrAward & " " " 
>> ")
>> or
>> DCount("[StudentID]", "tblStudent", "[AwardId] = ' " & StrAward & " ' ")
>> Note that the sapeces between the quote marks are for clarity here, and 
>> are not included in the actual expression.
>>
>> If you are trying to test first to see if txtAwardID is not null:
>>
>> If Not IsNull(Me.txtAwardID) Then
>>    strAward = Me.txtAwardId
>>    SAwards = DCount("[StudentID]", "tblStudent", "[AwardId] = """ & 
>> StrAward & """")
>>    Me.lblAwardStudentCount.Caption = "Total rewards: " & SAwards
>> End If
>>
>> Some lines of your code seem to have an apostrophe in front, which causes 
>> the line of code to be ignored.  I don't know if it is a typo or what, 
>> but since I see it I am making the comment.
>>
>> "Rob W" <Whllrob@aol.com> wrote in message 
>> news:OnTWQKmcIHA.4712@TK2MSFTNGP04.phx.gbl...
>>> Greetings,
>>>
>>> I have the following code assigned to previous record navigation button 
>>> (See below) :-
>>>
>>> How do I use the Is not null ??
>>> Testing the code the Me.txtAwardId is populated but during the DCount 
>>> execution I get the error "The expression you entered as a query 
>>> parameter produced the error: 'StrAward'
>>>
>>> Can anyone assist? Thanks
>>>
>>>
>>> VBA code attached to button.
>>>
>>> DoCmd.RunCommand acCmdRecordsGoToPrevious
>>>
>>> Dim SAwards As Integer
>>> Dim StrAward As String
>>>
>>> 'IsNotNull (Forms![frmCreateAward]!txtAwardId
>>>
>>>   StrAward = Me.txtAwardId
>>>
>>>   SAwards = DCount("[StudentID]", "tblStudent", "[AwardId] = StrAward")
>>>    Me.lblAwardStudentCount.Caption = "Total number of Awards enrolled on 
>>> the Award is : " & SAwards
>>>
>>> 'End If
>>>
>>> Me.lblRecordNumber.Caption = "Award Record number: " & [CurrentRecord]
>>>
>>
>
> 


0
Rob
2/18/2008 9:59:16 PM
Reply:

Similar Artilces:

Count number of checked boxes on a form
I would like to add a textbox (txtCheckedForms) that shows the number of checkbox fields are checked on a form. Is there a simple way of doing this? The only thing I've come up with so far is something like this... If chkField1 = True then varCheckedForms = varCheckedForms + 1 if chkField2 = True then varCheckedForms = varCheckedForms + 1 .... ifchkField50 = True then varCheckedForms = varCheckedForms + 1 txtCheckedForms = varCheckedForms The problem is, I have about 50 checkbox fields on my form. That would take a long time and a lot of space! Also, the fields aren't actually ...

checking duplicate values on data entry
Dear all, In my table design,Because I need to accept No value , I can not use NO duplicate index for my field , but I shouldn't let duplicate values in it. to develop the need I check the value in the data entry form using "Find first" of the record set. On my own computer it is OK and no duplicate value can be entered. but users CAN add duplcate value.(mine is office xp but some use office2003). what should I check? and if there are better solutions please let me know. tnx in advance. The version of Access should make no difference. How are you using FindFirst a...

When box is checked Yes, how to make the entire column turn grey??
Please explain how to do this. Thanks What box? d wrote: > Please explain how to do this. Thanks Use a linked cell (in an out of the way location) and then use that value as the rule for conditional formatting. The details would depend on how you made the checkbox and what version of excel you're using. d wrote: > > Please explain how to do this. Thanks -- Dave Peterson "Bob I" wrote: > What box? > > d wrote: > > > Please explain how to do this. Thanks > > Im trying to make a entire row shade to gray when it is completed. So...

Check if a number is within a certain range
Hi I'm trying to check if a number is within a certain range. EG: say i get a result of 9% in a cell, and in the next cell i want to check if that falls between the range of 0.1% and 10% and if it does then apply a formula how do i do this? Anyone know please? =IF(AND(B7>=0.1%,B7<10%),1,2) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com <ainese@gmail.com> wrote in message news:3082da2f-eef2-4a5f-9dfa-e27d348595b3@c58g2000hsc.googlegroups.com... > Hi > > I'm trying to check if a number is within a certain range. > > EG: &...

Check Boxes
I've read many of the post regarding check boxes and have tried some of the macros. I still cant seem to line up multiple boxes evenly. I end up zooming to 200 and manually doing it. I've even tried the draw tab to align, but only get one way. Hi, Here's a neat trick. If the checkboxes are from the 'Control' toolbox then they must be in design mode. Enable the 'drawing' toolbar "View|Toolbars" and you'll see a white arrow. Drag a box around all the checkboxes and they become selected. then on the drawing toolbar click:- Draw|Align or distrib...

Typed Datasets and ObjectDataSource
Hi, VS2008 We've got a CRUD app of about 300 pages with very little business logic. As such we intend to mostly use typed datasets with a few custom objects thrown in. Will be using Gridview, Detailsview, Formview, Listview etc on the UI. We need to connect the datasets to the UI through the BLL. Everyone seems to use ObjectDataSource for this. The very obvious limitation of this is that data modifications have to pass values as scalar parameters, rather than as typed datarows. This seems pretty horrible to me and I am no OO purist! So my real question is what did Micros...

Voided Checks
Is there a report that shows voided checks with the void posting date as apposed to the original check date? You can create a smartlist query to do this. Alternatively you can do a small report writer customization to add this field to the check information report. And finally, you can always perform inquiries against cheques listed in the check information report, which will show the void date. Smartlist would be the best approach. "Lynne" wrote: > Is there a report that shows voided checks with the void posting date as > apposed to the original check date? ...

Different check stub layout for different checkbooks
We are using GP 7.5. We have three different checkbooks that we will be issuing checks from and I wanted to know if we could have three different checks and 3 differenct check stub layouts. Can you also customize the check distribution report three different ways and save it as another report name?? If someone could advise, I would appreciate. We have one account set up and want to add the two others but need to know if this is possible. thanks barb There are enough check layouts to have three different checks, which specific report are you looking at? Leslie "BK" wr...

Error "OMAETEXT is null or not an object" when opening GP.
I have a couple users get the error message "null - MenuElement.setDisplayState: 'oMAEText' is null or not an object and they have to click "ok" to this error 4 or 5 times until the Great Plains page loads...but clicking on any of the links returns the error "null - CAPPSession.navigateToLink: Automation server can't create object" end user devices are all XP w/ SP2 running IE6 with all the latest Windows updates. The only fix so far has been to rename their windows profile with "_old" and have them log in and create a new windows profile ...

Count If or DCount -SLOW!!
I am creating a formula in a query in access and I have used the dcount formula on a field that I am trying to find the count of how many times a Job number shows up. This bogs down my computer tremendously and the database is only 10,000 records. The formula is as follows: CountOfJobNumber: DCount("*","Vert Duplicates","[col_CC_UT] = '" & [col_CC_UT] & "'") I know that this also takes a while in excel with the countif function but there is a shortcut if I sort the job numbers in ascending and I only do a count on 30 records at a ...

Check out important package from M$ Corporation
--pmhqydroiwovyei Content-Type: multipart/related; boundary="bqczrdpgsrgeanr"; type="multipart/alternative" --bqczrdpgsrgeanr Content-Type: multipart/alternative; boundary="eypewpmx" --eypewpmx Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to maint...

Parameters on a Graph not showing up in text box
I have a report with a crosstab query on a graph with parameters. I need the parameters (which is division and department) to show up on the graph. I added a text box in and built an expression. =" " & [Department] & " / " & [Division] The Department and Divsion will not pass through onto the graph report. I get =name? error. Any suggestions? ...

Transparent Check/Radio box on a XP Themed application
Hi, I have an MFC application which use the XP theme. I created a class for drawing Check/Radio boxes tranparently (to avoid the gray rectangle of those buttons): HBRUSH CUiButton::CtlColor(CDC* pDC, UINT nCtlColor) { HBRUSH hbr = NULL; pDC->SetBkMode(TRANSPARENT); // return hollow brush to preserve parent background color hbr = (HBRUSH)::GetStockObject(HOLLOW_BRUSH); return hbr; } This looks fine in a Non-Themed application (The rectangle of the Check/Radio controls, recieves the color of its parent). But on a Themed application I get a black rectangle. Can someon...

Direct Deposit
Customer just upgraded to GP10 and they are using Payroll with Direct Deposit. In doing a test on an employee - they have the following setup $50 to Savings Net to Checking Direct Deposit is set up as void checks. Entered a payroll transaction and the employee nets about $5....an actual check prints for the net; AND the ACH file is updated for the net as well. Why??? What are we missing in verifying? I did a similar test on my system and got a void check.... ...

Check for Increment
I have a query setup that has a result of the following Account, TransDate RefCode I have grouped by account, date I need to check for each account (3 account total) that the ref code last digit increments by 1. When the account changes, the number will change so I need for it to reset, the numbers will still increment by 1 but when the account changes it may initially jump numbers. If increments by more than 1 number I need to return the results, or create a table so I know to investigate. Is this possible. sample data below Account RefCode 39236W 0743811-025 3923...

Vlookup that checks against two values
I have a sheet that contains a column of Product numbers, a comlumn of customer numbers, and a column for orders. In another sheet I have the a similar format with the same columns. I need to check I need to move the values of the order to the second sheet but I must check against both the product number and the customer number. excel does not allow you to do the following (gives the #NAME? error). VLOOKUP(A1 AND B1, ANOTHERSHEETA1:C1,3,false) What is the best way to do this? John Saved from a previous post: If you want exact matches for just two columns (and return a value from a third),...

Money 2002,printing checks
I am using Money 2002 as our family checkbook and I am needing a question answered about printing checks.I always have to have two or three checks entered into the register to be printed before the program will even let me print checks.If I don't have enough checks to be printed it shows me an error saying I don't have any accounts with checks to be printed. See http://www.bollar.org/msmoney/#Q91. "Terry" <anonymous@discussions.microsoft.com> wrote in message news:07a101c392b7$3fb394f0$a101280a@phx.gbl... > I am using Money 2002 as our family checkbook and ...

LDAP server = null?
I keep getting a windows opening, which seems to be looking for an LDAP server. The name is "null", the user is "null", port is "3268", and I am absolutely clueless. Any help? remove the ldap/active directory address book from your account settings in microsoft outlook. (not required for home users) -- Neo [MVP Outlook] Due to the Swen virus, all e-mails sent to this account will be deleted w/out reading. ps - if you supply what version of outlook you are using, detailed steps can be provided on how to remove. "Michael S." <any@where.com> ...

check for double data
Hello everyone! I have a list of data where with every new input a new row is create in my list, this is done with the use of a macro. The list exists of several columns. I want to keep track of double inputs. Here is how it looks like A B C 1 APPLE 15/6/2004 SMITH 2 PEER 15/6/2004 PETER 4 PEER 17/6/2004 PETER 5 ORANGE 06/7/2004 JOHN 6 ORANGE 06/7/2004 JOHN etc.. As you can see in the list, I have a double input on row 5 and 6. I would like to see it in a bi...

How do you ensure that null-valued elements are not serialized?
I am using complex types in order to support serialization/deserialization of floating point numbers, since floating points can't be null. I've seen how to suppress attributes that are "not specified", such as having a float member called Value, and a bool member called ValueSpecified. This instructs the XML Serializer to omit that attribute altogether if it wasn't "Specified". But how can I tell it to omit the XML element altogether? Here's the problem: I deserialize an object that looks like this: <person> <name>Joe<...

Could not complete the operation one or more parameter values invalid
I am using Outlook XP and Windows XP with a mailbox on Exchange 5.5. In any folder in Outlook I click on the Find button on the standard toolbar and type in a word in the 'Look for' box (that I know is there) and I choose All mail folders in the 'Search In' box and an error message appears saying 'Could not complete the operation one or more parameter values invalid' I have tried the same thing on several computers with the same results. I have uninstalled and reinstalled. I have tried the same thing on Outlook 2003 and it works fine. I try to search in a specific fold...

Supressing select parameter dialog box
hi. A very minor bug really, but it's really annoying me. Can anyone tell me what can be done to stop the select pparametre message coming up every time the report starts? Obiously, I've had this before, but I can't find anyway round it this time. Some sort of code would proberly do it. (I've seen code that subpresses warnings, so I guess the same can be done here.) -- Message posted via http://www.accessmonster.com Remove the parameter from the query. Instead, use the WhereCondition of OpenReport. Here's a couple of examples: Print the record in the form at: h...

entering null values
I am trying to do a conditional statement, and I can't quite get it right. This is my formula: =IF(G8>8, G8-8, null) I don't know how to leave the cell blank if the condition is false. With the present formula I just get the "#NAME error. Mike Try: =IF(G8>8, G8-8,"") -- Rgds Max xl 97 ---------------------------------------------------- Use xdemechanik <at>yahoo<dot>com for email ----------------------------------------------------- "news.microsoft.com" <*> wrote in message news:#k6txvn5DHA.1504@TK2MSFTNGP12.phx.gbl... > I am t...

How to check region ?
How do I check / confirm which region my version of money is for ? Subject: Re: Money 2001 to 2004 upgrade From: "Bob Peel, MVP" <Bob_Peel@kiandra.freeserve.co.uk> Sent: 8/9/2003 7:46:26 AM Nope. Your problem is that you must stick within geographic region when upgrading. i.e. US > US or UK > UK. So if you are currently using a UK version of M2001 then you must wait until the UK version of M2004 is released - the US version on sale and as a trial won't do. -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.com/de...

Am I supposed to see the OUTPUT parameter's value in SQL Mgmt Stud
Hi, I have a stored procedure with an OUTPUT parameter. When I execute the procedure in SQL Server Mgmt Studio, am I supposed to see the OUTPUT parameter's value? I'm troubleshooting an issue and at this point I'm not sure if it's the application code or the stored procedure. When I execute the stored procedure, it does what it's supposed to do i.e. insert a new record, but I do not see the value of the output param -- unlike when I return values through a SELECT statement. Is this normal or am I supposed to see the value returned by the output param? -...