Query output in a parameter

[khurram7x]

How could i get output of a Query in a VBA variable please?? i'm trying as below but i'm getting error:

Dim output As Integer
vari = DoCmd.OpenQuery("qryCompare", acViewNormal, acReadOnly)

Output of a query is a single MAX value.

Thanks.

 

[marlan]

Hi,

There are a few ways:
1 - if all you need is one value, use DLookup function: http://www.techonthenet.com/access/functions/domain/dlookup.php.
For output of a set of data, look into (that is, google) Recordset. In Access, I find Dao.Recorset to be quite simple and fine to use.
try this: http://www.everythingaccess.com/tutorials.asp?ID=VBA-Traps:-Working-with-Recordsets
or this: https://msdn.microsoft.com/en-us/library/office/ff820966.aspx

If you want to stick to the trivial, go with Dlookup.

ATB

 

[marlan]

missed this one: http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners

 

[Galaxiom]

Since you are looking for the Max value consider whether the DMax function might do the job. It is quite similar to DLookup but returns the Max value.

 

[khurram7x]

Thanks Glaxiom, DLookUp solved my immediate problem.

Marlan, thank you for RecordSet references. Next chapter in my VBA is on SQL and i've seen RecordSet discussed there. I've quickly seen your references and they're packed with valuable information. I'll get back on them once I go through my next VBA lesson.