DCount criteria issue

[Malcy]

Hi
I am trying to set up a DCount but am getting compile error saying DOSETTE is variable not defined.

varLabCount = DCount("lngPtId", "qryDispLab", "strSupLast Is Null Or strSupLast<>" & "'" & DOSETTE & "'")

I thought I had set the inverted commas and ampersands correctly for text but clearly not. Have tried various options but they are almost infinite and not getting anywhere.
Any help would be much appreciated.
Thanks in advance

 

[uncle-lai]

Try replace

"'" & DOSETTE & "'"
with
"'DOSETTE'"

Peter


edited:
Oh, I think it should be like this:
"strSupLast Is Null Or strSupLast<> 'DOSETTE'")

Hope I am not pointing at the wrong direction.

 

[cable]

Is DOSSETTE a variable or not?

If it is and its a string then you've got the critera right, if its not then use uncle-lai's 2nd example.

 

[Malcy]

Thanks guys
I followed Peter's suggestion and the first one worked through a treat.
The answer is no DOSETTE was a string but not a variable
Am beginning to despair of all these inverted commas. Will I ever get the hang of it?!?
Best wishes

 

[cable]

best way to start is to setup a variable to hold the string.

so in your example:
Dim sTemp as String
sTemp="strSupLast Is Null Or strSupLast<>" & "'" & DOSETTE & "'"
varLabCount = DCount("lngPtId", "qryDispLab", sTemp)

You could then pause on the last line and do a ? sTemp in the debug window to check your string.

Also if its a long string your building, do it in bits, you gain nothing by cramming it on to one line.

One other minor thing, I dunno if this is normal or recommended, but I don't name my table fields like I do normal programming variables, ie no type prefix. I find this helps when you do a lot of dynamic sql building.

 

[Malcy]

Thanks Cable
That looks a good way of doing it. Bit easier to follow too!
Will try to remember to work them that way in future
As far as the prefixing goes I seem to recall seeing suggestion ages ago that it was a good idea to build field names this way so you would always know what they were. I have quite often found it helpful so just do it now without thinking.
I suppose we all have our foibles and since it works for me ...
Not at all sure what is recognised as best practice. In most Microsoft examples they don't do this and I find that a little strange!
Thanks again and best wishes

 

[Mile-O]

"'" & DOSETTE & "'"

My personal preference would be to replace the ' with ""

SO that we get:

"""" & DOSETTE & """"

It's more foolproof for variables.

 

[cable]

agh, no I hate those multiple "'s, so confusing in a single glance as to whats going on, esp near the ends of strings.

I use the ' or chr(34) method.

 

[Mile-O]

agh, no I hate those multiple "'s, so confusing in a single glance as to whats going on, esp near the ends of strings.

I use the ' or chr(34) method.

You could always create a public constant, as Pat Hartman has suggested before.

Public Const Q As String = """"

" & Q & DOSETTE & Q & "

 

[cable]

yea, that's quite neat...although some of it is how """" is used when you want one ", 3 or 5 I could 'get', but 4 is just illogical

 

[Mile-O]

4 is just illogical

One to open the string, two to represent one (otherwise the string would terminate), and one close the string.