asked on

Subform Dlookup with multiple criteria

I had this dlookup working then my dbase closed and did not save the changes.

I couldn't remember how I had the last part of the following syntax, It returns #Error.

I'm trying to have a text box lookup the status of the most recent updated record (txtLastUpdated).

=DLookUp("[StatusName]","lkpOrderStatusState","[OrderStatusStateID]=" & [Forms]![frmViewOrder]![SubFormOrderStatus]![cboOrderStatusState] & "And [LastUpdated]=" & [Forms]![frmViewOrder]![SubFormOrderStatus] & Last([txtLastUpdated]))

Open in new window

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

latzo4

ASKER

Trying retrieve the record where the [LastUpdated] field is equal to the subform's [txtLastUpdated] text box

I had this working yesterday but don't remember the second part of the syntax after the "And", I tried dmax, max, last and still does not get the most recent date.

=DLookUp("[StatusName]","lkpOrderStatusState","[OrderStatusStateID]=" & [Forms]![frmViewOrder]![SubFormOrderStatus]![cboOrderStatusState] & "And [LastUpdated]=" & Max([Forms]![frmViewOrder]![SubFormOrderStatus].[txtLastUpdated]))

SOLUTION

ldunscombe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ldunscombe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ldunscombe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

latzo4

ASKER

I'm still getting #Error

I tried:

=DLookUp("[StatusName]","lkpOrderStatusState","[OrderStatusStateID]=" & [Forms]![frmViewOrder]![SubFormOrderStatus].form![cboOrderStatusState] & "And [LastUpdated]= Max(" & [Forms]![frmViewOrder]![SubFormOrderStatus].form![txtLastUpdated] & ")")

and tried:

=DLookUp("[StatusName]","lkpOrderStatusState","[OrderStatusStateID]=" & [Forms]![frmViewOrder]![SubFormOrderStatus].form![cboOrderStatusState] & "And [LastUpdated]= Max(#" & [Forms]![frmViewOrder]![SubFormOrderStatus].form![txtLastUpdated] & & "#)")

latzo4

ASKER

I'd like to combine both of these, (each of these work independently):

=DLookUp("[StatusName]","lkpOrderStatusState","[OrderStatusStateID]=" & [Forms]![frmViewOrder]![SubFormOrderStatus].[Form]![cboOrderStatusState])

combine this as the second criteria

=DMax("LastUpdated","tblOrderStatus","OrderID=" & [Forms]![frmViewOrder]![SubFormOrderStatus].[Form]![txtOrderID])

Basically the I'm trying to return the status of the most recent record date [LastUpdated].

latzo4

ASKER

Thanks for the help, I'm going to post a new question with more details to see the best approach.