Trouble with DCount Using a Variable

[JustinPinette]

Hello all! I was hoping one of the experts here might be able to help me.

I am trying to create a number generator that counts the number of entries in a table where the criteria in the form of entries in a textbox are met. This is the code that I have so far:

Dim Ent As StringDim Modl As String
Dim Typ As String
Dim Root As String
Dim RICEWID As Integer


'Check to see if there is any missing information
If Not IsNull(Me![Entity]) And Not IsNull(Me![Module]) And Not IsNull(Me![Type]) Then
    
'Stick field values into the variables
    Modl = Me![ModuleTB]
    Typ = Me![TypeTB]
    Ent = Me![EntityTB]
    Root = [Modl] & "-" & [Typ] & "-" & [Ent]
    
    RICEWID = DCount("[Entity]", "RICEW_ID_GEN", "[Entity]=" & Ent)
    'RICEWID = DCount("[Module]", "RICEW_ID_GEN", "[Entity] = 'HH'")
    
    MsgBox ([Root])
    MsgBox ([RICEWID])
    
Else


'Missing information escape message
    MsgBox ("You done screwed up.")
    
End If
End Sub

On the line that the RICEWID variable is defined, the DCount function works fine as long as I pass it a string "[Entity] = 'HH'" but as soon as I remove the constant and insert the variable, I get Runtime Error 2471, despite the fact that the variable ='HH'. To me it looks like I'm passing the same info into the DCount criteria but clearly I'm missing something. Any help would be greatly appreciated.

Justin

[CJ_London]

you need to include single quotes

RICEWID = DCount("[Entity]", "RICEW_ID_GEN", "[Entity]='" & Ent & "'")

[JustinPinette]

That worked! Excellent, thank you. I understand the use of single and double quotes however I don't understand what it did in this instance. Would you mind giving me a brief explanation?

Also, how would I set it up to check the number of records with a combination of Modl, Typ & Ent? I assumed it would be something like:

"[Entity]=' " & Ent & " ' " & "[Module]=' " & Modl & " ' " & "[Type]=' " & Typ & " '"

...but this doesn't work. Nor does it work if I replace the "&" with "+".

[CJ_London]

if you are comparing text values, you need to use quotes. [Entity] is a text field, Ent is a text value. you don't use anything for number values and you use # for dates
with regards this

"[Entity]=' " & Ent & " ' " & "[Module]=' " & Modl & " ' " & "[Type]=' " & Typ & " '"

there are a number of issues

if you enter a line of code

debug.print "[Entity]=' " & Ent & " ' " & "[Module]=' " & Modl & " ' " & "[Type]=' " & Typ & " '"

you will see in the immediate window how that is interpreted. Then compare that with what a sql criteria looks like and you should see what is missing. Assuming all your fields are text it should look like

"[Entity]='" & Ent & "' AND [Module]='" & Modl & "' AND [Type]='" & Typ & "'"

Also note that you are using spaces between the quotes so if Ent=abc then this

"[Entity]=' " & Ent & " ' " &

will be interpreted as

[Entity]=' abc '

which is probably not what you want.

Finally Type is a reserved word - using it can cause unexpected errors as it can be misinterpreted to mean something else. Google 'access reserved words' to find out more

[RuralGuy]

I marked this thread as *Solved* for you using the *Thread Tools* at the top of the thread.
https://www.accessforums.net/showthread.php?t=1828

[JustinPinette]

Thanks for all of the help and sorry for the delay getting back to this.