storing SQL results in a var

[shrive22]

Does anyone know how to store the results from an sql statement in vb code in a variable. The resluts from the query are going to change based upon what the user chooses on my form??

thanks

 

[Russell Hauf]

Look up the database functions in Help (DMax, DCount, DSum, etc.). I'm sure that they will be what you're looking for. If you don't think that they are, please post example SQL.

HTH,

Russell

 

[shrive22]

this is what I would like

dim x as string
x = "SELECT field1 FROM table"

somehow i want to get the results from the query into a variable

my sql statement will only be returning on value because I will have a where clause that limits it to the primary key fieild.

thanks for the help

 

[Russell Hauf]

If your WHERE clause will limit it to one record, then you can use DMax. Give it a try - if it doesn't work, show me what you tried and I'll see if I can help.

-rh

 

[shrive22]

Im not sure what the DMax is?? I searched for it in access help and got nothing??

 

[Russell Hauf]

It is a database function (maybe look up Database functions in Help - I was able to find DMax when I opened Help and used the Index - I typed in "DMax" and the word was there). If you still can't find it, try this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac10/html/acmthDMax.asp

 

[shrive22]

How would the dmax function work when I dont want the maximum value of the domain i just want to test the expression
??

 

[Russell Hauf]

Because if your WHERE clause will only return one record, then the MAX of that record will be...yep, that one record!

 

[Russell Hauf]

So say you have something like this for your query:

SELECT fldTotal FROM tblOne WHERE fldMyDate = #04/09/2003#

If this returns one record, then you could use DMax like this:

varSomething = DMax("[fldTotal]", "tblOne", "[fldMyDate] = #04/09/2003#")

Make sense? Hope so,

Russell

 

[shrive22]

Thanks a lot for the help. Ill try it out.