[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

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

5 Solutions
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You refer to a Subform like this:

[Forms]![frmViewOrder]![SubFormOrderStatus]. FORM![cboOrderStatusState]

Also, I'm not sure what  "& Last([txtLastUpdated])" is supposed to do. AFAIK, there is no "Last" keyword or function in Access, although you may have a custom function named that
Helen FeddemaCommented:
It might be better to work on this expression in a query, used as the record source of the form.  That would let you use a Totals query to get the last record.  It is certainly easier to work on the syntax in a query as opposed to the control source of a text box on a form.
latzo4Author Commented:
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]))
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

Also if this text box and controls are all actually on the same form / subform you could simplify it bu using the Me operator


=DLookUp("[StatusName]","lkpOrderStatusState","[OrderStatusStateID]=" & Me.[cboOrderStatusState] & "And [LastUpdated]= Max(" & Me.[txtLastUpdated] & "))"

Sorry as LSM posted you will need to change your references

=DLookUp("[StatusName]","lkpOrderStatusState","[OrderStatusStateID]=" & [Forms]![frmViewOrder]![SubFormOrderStatus].form![cboOrderStatusState] & "And [LastUpdated]= Max(" & [Forms]![frmViewOrder]![SubFormOrderStatus].form![txtLastUpdated] & "))"
latzo4Author Commented:
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] &  & "#)")

latzo4Author Commented:
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].

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now