Newbee -- where's DoCmd.RunQuery?

[rcoutts]

Hi,

I have a simple query that returns a single record from the table that matches a given employee number and has the Maximum value in the date field. I'm running the query from a VB Module and want to get load the date value into the module. Currently I'm running the Query through DoCmd.OpenQuery, but that just shows me the record. I looked at DoCmd.RunSQL, but it seemed like I was heading in the wrong direction with that approach.

What's the best way to load the value into my module of the field in the record that my query finds? If there's a alternative non-query appoach to finding this single field, I'm all ears.

Thanks!
Rich

 

[JohnLowell]

Try the DMax function. It's similiar to writing a select statement, but without the keywords.

Let me know how this goes

John

 

[tlbroadbent]

Use the DMax function.

Dim MaxDate datetime

MaxDate = DMax("[DateMax]", "qryEmployeeMaxDate", "[EmpNo]=" & varEmpNo)

qryEmployeeMaxDate is the query name.
[DateMax] is the name of the date column returned by the query.
EmpNo is the name of trhe employee number column in the query.
varEmpNo is a variable containing the employee number you want to search for. You could use a text, combo or list box value instead of a variable. Just substitute Me.ControlName for varEmpNo in the third parameter of DMax. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous

 

[rcoutts]

Thanks! DMax did the trick. It was interesting to see that I could run DMax on the query results, though I ended up just filtering the original table.

This forum's really great. I don't know what I'd do without it.