Dmin with module-variables?

[Willem!]

Hi,

I have the following problem. I want to use the DMin-function and I want to fill the domain- and criteria-section with a variable which is declared in the module.

So, I want to do something like this:


I have three queries qryProductFood, qryProductTools, qryProductElectronics. All queries hold a column 'RecordDate' and a colum 'ProductID' (and some other data). I want to find the oldest date from a certain product in the query with the function DMin.

(...)
Dim intProduct As Integer
Dim strType As String
Dim dtMin as Date

intProduct = Form_MAIN.ProductNumber.Value
strType = Form_MAIN.ProductType.Value

dtMin = DMin("RecordDate", "qryProduct & strType", "[ProductID]=intProduct")

Is this possible? I cannot get it to work, I cannot find code-snippets on internet which uses something like I want to do...

 

[Ron_dK]

Well, efectively this should work :

Expr1: DMin("RecordDate","Products Query")

If you could make one query ( in stead of the three you have) , which should have the record date(s) and Product ID from all tables.


Hth

 

[Willem!]

Thanx Ron for your reply. I cannot make other queries though, I really need it to work in my VBA-code...

...and I just find out how to do it. The code should be this:

Dim intProduct As Integer
Dim strType As String
Dim dtMin as Date

intProduct = Forms!Form_MAIN!ProductNumber
strType = Forms!Form_MAIN!ProductType

dtMin = DMin("RecordDate", "qryProduct" & strType, "[ProductID] = " & intProduct)

Especially the last sentence is of importance.

Thanx anywayz Ron!