Dmax giving me a headache (1 Viewer)

[Geoff Codd]

I have two tables one has the following fields

TABLE1
Name
Type
Date
M1_Present
M1_Previous

The other tables has the fields

TABLE2
Company
Utility_Type
Invoice_No
Invoice_Date
Date_From
Date_To
Invoice_Amount

I also have a form which contains two combo boxes, enabling the user to select the company name and the utility type

What I need to do is to filter the records in Table1, First of all I set the criteria in Name field as that of the Company Name in my form and did the same with the Utility_Type.

Now I need to only show the records where the Date is greater than the Max of the Date_To field in TABLE2.

For this I'm trying to set the criteria to me the greater than the max of Date_To where Name = Company and Type = Utility_Type

For this I am trying to use the DMax fuction but this is where my problems start What I've got so far is

>DMax("[Date_To]","[Table1]", "[Company] = [Forms]![Invoice Recharging (External) Invoice Creation]![Company] & [Utility_Type] = [Forms]![Invoice Recharging (External) Invoice Creation]![Utility_Type]")

Any help with this will be appreciated
Thanks
Geoff

 

[simongallop]

Shouldn't it be DMax("Date_To","Table2 ... as that is the Date_To field that you are looking at. Also change the & to AND as it is a sql statement NOT a variable append

HTH

 

[Jon K]

Assuming Utility_Type is a text field, the DMax() function is:-

>DMax("[Date_To]","[Table2]", "[Company] ='" & [Forms]![Invoice Recharging (External) Invoice Creation]![Company] & "' and [Utility_Type] ='" & [Forms]![Invoice Recharging (External) Invoice Creation]![Utility_Type] &"'")

 

[Geoff Codd]

Thanks for your help the both of you.
geoff