Run query from VBA

[AJ]

I have a query in my DB that returns 1 value. I would like to run it in VBA
and assign it to a variable. I thought this would be easy but cannot seem to
figure out how. Can it be done without a full recordset?
Example:
HV_QUERY = (results from query)

Thanks in advance.

 

[Wayne-I-M]

There are other methods but a simple work-around would be to create a form
based on the query and place it in your main form as a subform (don't link
with master and child). Requery the subform and use

Me.somecontrol = Forms!MainFormName!SubFormName.Form!ControlOnSubForm

You can set the visible to no if you want for the new sub

Good luck

 

[John W. Vinson]

I have a query in my DB that returns 1 value. I would like to run it in VBA
and assign it to a variable. I thought this would be easy but cannot seem to
figure out how. Can it be done without a full recordset?
Example:
HV_QUERY = (results from query)

Thanks in advance.

HY_QUERY = DLookUp("[fieldname]", "[queryname]")

VBA has no way to know if the query returns one field or 255, or one record or
32844126 records. Therefore you can't just refer to the query name!

DLookUp will retrieve the first record (in this case the only record) if you
leave out the optional third parameter.

John W. Vinson [MVP]