Dlookup with Dmax criteria - Microsoft Access Forum


	Back to Access Forum Comments List

Dlookup with Dmax criteria

Michelle Koos

3 years ago

Educated by Richard, Google and the experts and advanced users here on Richard's site, no formal training.  This one is probably easy, but I cant' seem to find the answer despite having searched for literally days.  Of everything I have found, Richard's videos on syntax error and Dlookup with multiple criteria to got me this far.

I have a table with rental equipment (EquipmentT).  I have a table with customers (CustomerT).  I have a union table to log rental history (CustomerID, EquipID, StartDate, EndDate).

I would like to make use of an unbound text box to see, at a quick glance, what piece of equipment a customer currently has rented.  There is only one piece of equipment rented to any one customer at a time - at least for now. The code behind the unbound text box is my problem.

CustomerID and EquipID are auto numbers.
StartDate and EndDate are dates.

I selected String for each because honestly, I don't have a good understanding of those or integer or fill in the blank with any other option.  Variant and string are my go to because but both seem to work in every case I have used them in so far.

I successfully used Dmax to return to the most recent date, but this is as far as I am able to get without getting a syntax or data mismatch error.  I think the date might be my issue, but I can't find the solution.  I tried MRD As Date, but I think that took me farther from my goal as I don't really understand what that mean.

    Dim MRD As String 'most recent date
    Dim RU As String 'rented unit

    MRD = DMax("[StartDate]", "RentalHxT", "CustomerId = " & CustomerID)
    RU = DLookup("[EquipID]", "RentalHxT", "StartDate = """ & MRD & """ AND CustomerID= """ & CustomerID & """")

    MsgBox "Answer to Most Recent is " & MRD
    MsgBox "Answer to rental unit is " & RU

The error I keep getting is Data Type Mismatch.

I use the message boxes right now just to test my return data.  The end goal is to have text that would read "Current Rental: " with the current rental information.  I did find a piece of code that deals with recordsets that got me to a line of text that reads "Current rental: None" when there is no rental history.  I am not sure if this would help me get to the EquipID or not.  I don't understand recordsets anymore than I do variants or strings.

The alternate plan is to include a rental history sub form, but liked the cleaner look of the form without it.

Thanks in advance to anyone who can help me get through this roadblock.

Kevin Yip @Reply

3 years ago

In your DLookup, this portion:

     "StartDate = """ & MRD & """ ......

should be:

     "StartDate = #" & MRD & "# ......

This is because MRD is not a string, so it shouldn't be enclosed with quotes.  It is a date, which should be enclosed with "#".  This is a VBA rule for how literal date values should be expressed.  E.g.:

    Dim MyDate As Date
    MyDate = #1/19/23#

You don't write:

   MyDate = "1/19/23"

which will return a type mismatch error also, because a date, not a string, is expected.

"#" signs are needed for dates because if you write:

    MyDate = 1/19/23

Access will interpret it as 1 divided by 19 divided by 23, i.e. a math expression.

Michelle Koos OP @Reply

3 years ago

Kevin, thank you for not just responding, but also taking the time to break this down for me so I can actually understand the 'why' behind what is working and what isn't.

Before posting my question, I played around with "#" based on some of Richard's videos, but I couldn't get it to flow.  At least I was partly on the right track.  I thought maybe leaving it as a string with the quotes would resolve the issue.  You taking the time to explain how Access interprets a date as a math expression was a light bulb moment (dim, but still a light bulb).

With your guidance, I changed that DLookup part.  It returned with an error.  I think I may have something out of place (crazy how an extra space  or comma or whatever can make all the difference sometimes).  I tried resolve it, but I am coming back with the "Data type mismatch in criteria expression" error (Run-time error '3464' if you need that part too).  Would you mind reviewing it?

     Dim MRD as Date 'changed from the original type String
     Dim RU As String 'rented unit

          MRD = DMax("[StartDate]", "RentalHxT", "CustomerId = " & CustomerID)
          RU = DLookup("[EquipID]", "RentalHxT", "StartDate = #" & MRD & "# AND CustomerID= """ & CustomerID & """")

Double-double quotes and I don't get along very well.  One of my thoughts was that I needed to remove part of them after making the modification you pointed out.  That didn't help and only lead to and expression error.

I thank you again for your time and help.

	Kevin Robertson @Reply 3 years ago
CustomerID is a number therefor doesn't need quotes around it.

	Michelle Koos OP @Reply 3 years ago
Success!!!!! Thank you so much!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen