Why won't my query work (1 Viewer)

[Geoff Codd]

I've inserted the following into my query now it won't work, and comes up with the error "Microsoft can not find the name 'Present Date' you entered in your expression

DMax("[Cost_Per_Unit]","[Invoice Recharging (External) Costs]","[Company] = [Forms]![Invoice Recharging (External) Invoice Creation]![Company] And [Utility_Type] = [Forms]![Invoice Recharging (External) Invoice Creation]![Utility_Type] And [Effective_Date]<=[Present Date]") AS [Price_Per _Unit]

Any idea's are appreciated
Thanks
Geoff

 

[The_Doc_Man]

In that syntax, [Present Date] MUST be a field in your table. If it IS such a field, then I don't know why it doesn't work, either. But perhaps you wanted to use today's date?

Today's date is Date() function.

How you use it depends on the format of the [Effective Date] field.

Basically, you have to get both fields into the same format.

Like, MAYBE use Format(Date,"ShortDate") or something like that on BOTH fields. Or compare both of them as raw date fields if that is what they both happen to be. But it will make a difference as to how the [Effective Date] field was originally stored, too.

 

[Pat Hartman]

The_Doc_Man,
when comparing two fields of date/time datatype, their respective display formats are irrelevant since Access uses their internal values for the compare operation. And their internal values are serial numbers NOT text strings. The only time that format becomes an issue is if one or both of the dates is a string data type as it would be from a textbox on a form.