DCount is going to make me cry

[HarrySpencer]

Hello

Please can someone give mesome advice on my DCount statement. I'm using the statement that will count the number of records (people) with an age of 18 who are connect to a certain establishment. The age only needs to be calculated at certain times of the year so it is stored in the table as a number. There are only two tables Establisment and Person.

I've looked at so many examples of DCount on this forum and MSDN but haven't been able to get this to work. The closest I've got is a Type Mismatch error.

Count18Years = DCount("[Age]", "[Person_tb]", "[Age]" = 18 And "[EstabID]" = EstabRecordsNumber)

Please can anyone help.

Cheers, Harry

 

[namliam]

Hello

Please can someone give mesome advice on my DCount statement.

Best advice: Dont use it or minimize its use as much as possible.

It has its uses, but it is limited in its use.

Like here, you dont want to use DCount, you want to use a group by query...

DCount("[Age]", "[Person_tb]", "[Age]" = 18 And "[EstabID]" = EstabRecordsNumber)

the error here is that the criteria is 1 complete string.... Thus the proper syntax should be:
DCount("[Age]", "[Person_tb]", "[Age] = 18 And [EstabID] = " & EstabRecordsNumber)

Couple of issues,
AGE = 18 only pickes up 18, dont you need anyone over 18? or something?
AGE is a number field? Assuming you have a Date of Birth field, AGE should be a value that is calculated on the fly in a query... Search the forum for such functions.

Good luck !

 

[gemma-the-husky]

note that if may help if you use this sort of thing

sub mysub
dim s as string

on error goto fail

s = "[Age]" = 18 And "[EstabID]" = EstabRecordsNumber
msgbox(s)

Count18Years = DCount("[Age]", "[Person_tb]", s)

exit sub

fail:
 msgbox("Error: " & err.description)
end sub

then
a) the on error statement will stop your prog crashing, and tell you why its going wrong
b) putting the sql string into a string variable will let you see what it contains first

the point is that the final string needs to look like this

"[Age] = 18 And [EstabID] = 23"

(where the 23 represents the value of estabrecordsnumber
if it isnt formatted correctly, it might still work, but it probably wont give you what you expect

 

[HarrySpencer]

thank you that's help me get it working. I only need to calculate certain ages so will just repeat for those.

Many thanks again.

Harry

 

[namliam]

Even if you only need certain ages... Hardcopy calculations is a big no-no...