Refer to value from Query in VB-code

[Lizzard]

I´m trying to use a value created in a query in a calculation in a VB- subrutine...

How do I get a value from my query to a variable that I can use in VB?

Thanx in advance!

/Lizzard

 

[A real begineer]

Some people will disagree with this approach, however, it works well in short queries, where the data produced is limited.

Dlookup("[myField]","[myQuery]")

 

[D-Fresh]

If you're looking for a specific value based on criteria you could also use this...

dim MyDB as database
dim MyRecs as recordset
dim MySQL as string

MySQL = "SELECT [FieldName] FROM [QueryName] Where [FieldCriteria]='" & txtCriteria & "'"

set MyDB = currentdb
set MyRecs = MyDB.openrecordset(MySQL)

YourVariable = MyRecs!FieldName

MyDB.close
set MyRecs = nothing
set MyDB = nothing


'Note that I used the tic marks(') in the SQL statement. This is if your criteria is a string. If your criteria is a number, then just omit the tick marks('). Hope this helps.

Doug

 

[Lizzard]

Thanx alot guys!

Works excellent now!!

/Lizzard