Basic Syntax Question

[Crookshanks]

Goodmorning,

I am working on an application using DAO. Now I want to user some SQL statements in my code, but I am not sure what the best way is to do so. I just need a start ...

For example I want to do the following:
I have a table where I want to count how many times the string "Liq" occurs in a certain Field. It is of course a bit 'stupid' to read all the lines and using a counting variable.

Q: Hoe do I use SQL in Visual Basic for the Query above (and give the result to a variable?)

Hope that anyone can help me started using SQL. Some good links with basic (easy) info would be helpfull too.

Thanks in Advance,

 

[RoyVidar]

[tt]dim strSql as string
dim rs as dao.recordset
dim lngMyCount as long
strsql="select count(*) as mycount from sometable where somefield ='lic'"
set rs=currentdb.openrecordset(strsql)
if not rs.bof and not rs.eof then
lngMyCount = rs.fields("mycount").value
end if
rs.close
set rs=nothing[/tt]

Changing from using domain aggregate functions to dao recordsets, will probably improve the efficiency of your code. The domain aggregate functions can be a recourse drag on linked tables. Here's some ready made replacements having the same syntax as the DA, xcept for starting with a T (TLookup, TSum...) Domain Aggregate Functions Replacements

Else, when collecting criteria from form controls/variables, just be sure to concatenate the values into the sql string, and mind the delimiters consider only the where clause of the above statement:

[tt]...where sometext='" & me("txtText").value & "'"
' single quotes for text
...where somedate=#" & me("txtDate").value & "#"
' hash for dates
...where somenum=" & me("txtNum").value
' none for numerics[/tt]

- is this in the direction you need?

Roy-Vidar

 

[Crookshanks]

Yes, pretty much what I needed. I did not realize that the result of the query is (always?) a recordset. Thanks!

 

[PHV]

A simpler way, but slower:
lngMyCount = DCount("*", "[name of table]", "[name of field] Like '*Liq*'")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Crookshanks]

Thanks both to you for your help so far, I have one additional Question. The [name of table] is a table in another database, this is not a problem when using a query.

Q: What is the correct syntax for pointing to another database when using the DSum of DCount functions?

Thanks in advance!

 

[PHV]

I'm afraid you have to play with a linked table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244