Advanced DLookup ?

[bigstav]

Hi, i am using the following DLookup in the On_Load() event of my form :-

txtNextBillDue = DLookup("[NextBillDue]", "[tbl_BillitReportHistory]", "[SiteRef]='" & Forms![frm_Site]![AccountNo] & "'")

It works fine but the problem is that there are multiple values for the "NextBillDue" result field and i want to select only the Max value of this.

The TextBox "txtNextBillDue" on my Form is unbound - is there a way to do this with DLookup or can i do it another way ?

Thanks for your help,

Mark.

mark.davies@npower.com

 

[boblarson]

One way -

Create a grouping query that pulls the max value of NextBillDue for everyone and then reference that in the DLookup instead of the tbl_BillitReportHistory table itself.

 

[bigstav]

Many thanks Bob, never thought of that - works a treat !

 

[boblarson]

Happy to help