Can you use SQL in VBA code? (1 Viewer)

[whitespace]

Hello, I have a query that has a number of fields one of which is checking to see if one value is in a list. To do this I created the following function in vba..

Function tot(a As Variant) As Long
    If a = "1" Or a = "2" Or a = "2A" Or a = "2B" Or a = "2C" Or a = "3" Or a = "4" Or a = "4+" Or a = "5" Or a = "6" Or a = "7" Or a = "D" Or a = "8" Or a = "E" Or a = "A" Or a = "B" Or a = "N" Or a = "W" Or a = "D" Or a = "T" Then tot = 1
End Function

and then in the query I simply use EnTot: tot([English Level])

I get the correct answer, the problem is this is unbelievably slow, so after help on this very forum I tried using SQL and thus in query designer I used:

EnTot: Sum(IIf([English Level] In ("1","2","3","4","5","6","7","8","E","A","B","N","W","D","T","Q"),1,0))

This works miles faster and is perfect, however... Because there are a number of queries, databases and users that need to use this 'Tot' function/procedure it is impractical to have to change every query if I need to include "X" for example (as I would have to do if using the SQL version).

Is it possible to have a function in vba that somehow uses SQL to run as fast as the SQL version but obviously having the advantage of being a function that all the queries can use, instead of recreating it in every query. Does this make sense?

Any help is much appreciated.

 

[workmad3]

Dim rs as DAO.recordset
Dim sql as string
<Set up your sql statement here and store it into the string sql>
Set rs = CurrentDB.OpenRecordset(sql)

then you should have no trouble getting the value out of the recordset

hope that helps

 

[whitespace]

thanks workmad3, but i couldn't get it to work I amended it a bit but here is the code I used..

Function tot2(a As Variant) As Long
Dim rs As adodb.Recordset
Dim sql As String
'<Set up your sql statement here and store it into the string sql>
sql = "EnTot: Sum(IIf(" & a & " In (""1"",""2"",""3"",""4"",""5"",""6"",""7"",""8"",""E"",""A"",""B"",""N"",""W"",""D"",""T"",""Q""),1,0))"
Set rs = CurrentDb.OpenRecordset(sql)

End Function

Also, I'm not actually returning anything here - I don't think I'm doing it right. Can I just say that I have seen code to create a recordset in VBA but I don't want to recreate the whole query in VBA, just the part that checks the value in a and returns 1 or 0 if it is in the specified list?? Is this possible??

My other version (the Tot function) works fine, it's just too slow. Thanks again for any help.

 

[workmad3]

hmm...

ok, try something like this as the sql statement

sql = "SELECT COUNT(*) FROM <tablename> WHERE " & a & " IN ('1','2','3','4','5','6','7','8','E','A','B','N','W','D','T','Q')"

This will find all the records in the table that has your criteria in fieldname a, and count how many records there are, which is equivelant to adding one on each time you have a record that matches the criteria.