Dlookup Error

[Jonno]

Could someone identify why the following simple Dlookup function is giving me errors?

Dim intxy As Variant
Dim intxz As Variant

intxy = Forms!form1!vx

intxz = DCount("[id]", "comments", "[ncrtype] = 'I' & [ncrno] = intxy")

I have tried various syntax adjustments but I get either a type mismatch error or "The object does not contain the automation object intxy"

I have had a look at previous posts for this, but I'm not sure which way to solve this.

 

[Mile-O]

• & is an operator used for concatenation. A Dlookup function uses the logical And.
• You are only confusing yourself by using the naming convention for integers to name an integer. If you are dealing with whole numbers then dimension them as Integers. If you insist on using variants (they eat up more memory) use the var prefix. i.e. varXz
• Remember, anything with "" is a string and is just a textual representation so having "[ncrno] = intxy" is literally what it says. If intxy held the value 5 it wouldn't matter in that statement as it is still "[ncrno] = intxy" - this should be "[ncrno] = " & intxy" - note the concatenation this time.

So, we should now have:

Dim intXy As Integer 
Dim intXz As Integer

intXy = Forms!form1!vx 

intXz = DCount("[id]", "comments", "[ncrtype] = 'I' And [ncrno] = " & intXy)

And just as an extra comment - you should try and get some meaningful names into your variables, fields, objects, etc.

Form1 ?
vx ?
intxy ?
intyz ?

 

[Jonno]

Thanks for the tips and advice - as you can see I am "Dabbling" in VB at the moment, I am trying to learn as I go along with the time I have spare. I think a more logical approach to naming is a good idea.

Thanks for the time, I appreciate the help people have offered me via this forum.

Jon