Dlookup function with max condition

[jaryszek]

Hi Guys,



I am trying to retrieve SchemaName from table t_currentschema where Date is the newest one.

How can i do this?

I tried:

select DLookup("[UserSchema]", "t_currentschema", "[UserInputDate] =" & max(UserInputDate)

but this is not working.

Please help,
Jacek

[ranman256]

DMax(...)

dlookup not needed since DMax IS the dlookup.

[jaryszek]

Hi,

thank you.

I tried to use:

SchemaName = DLookup("[UserSchema]", "t_currentschema", "[UserInputDate]=#" & DMax("[UserInputDate]", "t_currentschema") & "#")

where Dmax =



i am getting error:





please help,
Jacek

[isladogs]

To use dates in expressions, you need mm/dd/yyyy or yyyy-mm-dd format
In your case, also add the time part - mm/dd/yyyy hh:nn:ss

SchemaName = DLookup("UserSchema", "t_currentschema", "UserInputDate=#" & Format(DMax("UserInputDate", "t_currentschema"), "mm/dd/yyyy hh:nn:ss") & "#")

You might be better doing this in two steps - get the latest UserInputDate then get the schema name

 Dim dteDate As Date    
dteDate = DMax("UserInputDate", "t_currentschema")
SchemaName = DLookup("UserSchema", "t_currentschema", "UserInputDate=#" & Format(dteDate, "mm/dd/yyyy hh:nn:ss") & "#")

[orange]

Perhaps

SELECT UserSchema from t_currentschema
where userInputDate = (select Max(userInputDate) from t_userSchema)

[jaryszek]

Hi Guys,

thank you.
Works like a charm.

TempDate = DMax("[UserInputDate]", "t_currentschema")


SchemaName = DLookup("[UserSchema]", "t_currentschema", "[UserInputDate]=#" & Format(TempDate, "yyyy-mm-dd hh:nn:ss") & "#")

very strange syntax

Best,
Jacek