counting something and putting it into a variable

[gibsonn]

I am trying to use the code below to put the result into a varible:
strSQL = "SELECT COUNT(*) FROM adult_key_pro WHERE adult_key_pro.key_inc_id = 12;"
The problem is that the variable has nothing in it when I stop the code using a breakpoint.

Any help would be appreciated.

 

[Mile-O]

Can you detail further as - from that snippet - all I can see is you assigning a literal string to a string variable.

 

[gibsonn]

Thanks for replying. The only other piece of code that relates to this Dim strSQL As String. I relise I may be way of the mark here, but what I am trying to do is count how many records a in a table that have the an id of 12 say. Once I know that I can use it later on to maybe delete a record.

 

[ColinEssex]

what I am trying to do is count how many records a in a table

Could you use the DCount function?

Col

 

[Mile-O]

Thanks for replying. The only other piece of code that relates to this Dim strSQL As String. I relise I may be way of the mark here, but what I am trying to do is count how many records a in a table that have the an id of 12 say. Once I know that I can use it later on to maybe delete a record.

lol, you are way off the mark. You need to use a domain aggregate function - in this case, DCount().

Dim lngTotal As Long
lngTotal = DCount("[key_in_id]", "adult_key_pro", "[key_in_id] = 12")

I would be wary of hard-coding the value 12 in as I'm sure you'd want to do counts of other records where the [key_in_id] field may be 5, or 10, etc.

Another couple of methods that you can use are DAO (A97) and ADO (>= A2000) - there are loads of articles on these in the archives.

 

[gibsonn]

Easy when you know how. thank you for your help