Using DCount() in Macro

[Emily]

Hi,
I couldn't figure out why I keep getting missing operator syntax error on my Dcount function within my macro. I believe the syntax is correct, could someone please help. I am using Access 2010.

I tried both under the macro and both version have the same error!!

If DCount("*","Password ID","security id =" & [Form]![UserId])=1

or
If DCount("[security id]","Password ID","security id =" & [Form]![UserId])=1

 

[SteveSchapel]

Emily,

I believe the problem is with this bit:
[Form]![UserId]

If the UserId control is on the same form that your macro is running on, then it should be:
..."security id =" & [UserId])

If the UserId control is on another form, then it should be:
..."security id =" & [Forms]![NameOfOtherForm]![UserId])

Or in that case, this would also be ok:
..."security id = [Forms]![NameOfOtherForm]![UserId]")

 

[Emily]

Hi Steve,

thanks for your reply. I have changed the syntax but i am still getting an error message.

" Syntax errro( missing operator) in query expression 'security id = 22'.
for the following statement.

If ( DCount("*","Password ID","security id =" & [UserId])=1 ) then

The macro is place under the on click event of the control property not on the form property. Seems like it is picking up the UserId value but can't evaluate the Dcount function! Please help!!! thanks.

 

[Emily]

Thanks Steve, I got it fixed..it was the quote " that I needed.

DCount("*","Password ID","security id =" & "[UserId]" )=1

Then it works!!

 

[SOS]

Thanks Steve, I got it fixed..it was the quote " that I needed.

DCount("*","Password ID","security id =" & "[UserId]" )=1

Then it works!!

Strange that it would work. I would think it would not be working right because your field of security id has a space and should have square brackets. And the quotes should NOT be around [UserID]:

It SHOULD be:

DCount("*","Password ID","[security id] =" & [UserId] )=1

or if security id is text:

DCount("*","Password ID","[security id] =" & Chr(34) & [UserId] & Chr(34) )=1

 

[Emily]

Oh yes, the exact syntax that I got it to work is as followed

DCount("*","Password ID","[security id] =" & "[UserId]" )=1

It has to have the square brackets and the quotes. thanks for you help!!

 

[trishcollins]

Just reviewing this thread and hoping that SOS can help. I have a similar situation. I am using DCount() in a macro in an if then statement to check for no records. If records, then print preview, otherwise, message to user and return to form.

It's not that simple because although I am using the same Query, I have a potential of three different "Where Conditions" depending on what they have selected in the form, and the Where Conditions are for different fields (one is on due date, one is on closed date, and on is on opened date.

So, I started by putting assigning the contents of the Where Condition to a variable (called "Where_Var) using the settempvar. I have placed the variable in quotes, for example:

SettempVar
Name: Where_Var
Expression: = "[NBS Request Due Date] Between Date() And Date()+Val([Forms]![NSB CCRR Query Form]![Number_of_Days]"

Now the tricky part. Before I runmenucommand printpreiview, I want to check to ensure there are records. So I added an if then statement using the Dcount funtion

If DCount("*","[NBSS CCRR Activity Records]","Where_Var")=0 then....

I have run the macro in single step and it sets the Where_Var without a problem, but when it gets to the dcount function statment, the macro fails.

I have tried the "Where_Var" without quotes, with double-quotes, with []. Nothing works.

Any suggestions?

 

[trishcollins]

Getting closer. I found I needed to actually reference the variable using the [TempVars] prefix. It is now evaluating the dcount without an error, but it's evaluating incorrectly -- in other words, it's evaluting the condition as false, when I know it's true (no records meet the criteria).

I will get this yet, but if anyone has any suggestions, it would be appreciated.