How to assign value get from sql statement to variable

[spard]

Actually i need to count the image number by using sql statement and the value get from that statement need to assign variable named imageCount.
Here is code i write but didn't get output.So can anybody plz help me what is wrong with this code:

Sub countImage()
Dim cnn
Dim rs
Dim strSql As String
Dim imageCount As Integer

On Error Resume Next
strSql = "SELECT COUNT(*) AS 'counter' FROM IMAGES WHERE ([IMAGES].[IMAGE TYPE]='RADIOLOGY IMAGES' AND IMAGES.[PAT #]=" & Me![PAT #] & ");"
Set cnn = CurrentDb
Set rs = cnn.OpenRecordset(strSql)
With rs
If Not .EOF Then
imageCount = !counter(Note:I think this part is wrong need attention here)
End If
.CLOSE
End With
Set rs = Nothing
End Sub


Please reply me ass soon as possible.I really appreciate if sombody solve my problem.

Thank You..

 

[pbaldy]

Actually I think the problem is here; you don't want the single quotes:

strSql = "SELECT COUNT(*) AS counter FROM..."

 

[The_Doc_Man]

Don't do this directly with SQL. Look at the DCount function.

The call might be this simple:

ImageCount = DCOUNT( "*", "IMAGES", "[IMAGE TYPE] = 'RADIOLOGY IMAGES' AND [PATNUM] = " & Me![PATNUM] & """" )

A couple of words of advice. First, NEVER use special characters in field names. PAT# is a very bad choice. Second, "Image Type" as a field name is not a good choice because spaces in field names are usually going to cause trouble down the road.

A DCount "synthesizes" an SQL statement for you and transfers the information automatically. The first argument is what is counted. The second argument is the table or query holding your data. The third argument contains the WHERE clause (without the word WHERE). The table name is not needed to qualify the fields in the criteria expression because the fields are members of the domain (2nd argument).

What you were trying to do by hand is actually infernally difficult because SQL and VBA actually don't get along that well in terms of data exchange. The data movement tends to be uni-directional. You can substitute or build SQL strings from VBA but getting data back from SQL without a recordset is a royal pain. However, using the domain aggregate functions, you can just let Access do the hard work for you.