Dlookup Function Criteria

[matethreat]

Hello,

I want to look up a value in a table with the DLookup function, but I am a little unsure of how to write the criteria.

DLookup("Price", "[History]", "ItemNumber='" & Me.ItemNumber & "'")

in this code I want it to find the price of that item the last time it was completed.

So I will have a table (History) that looks like this

ItemNumber Date Price
0001 14/06/2004 25.00
0001 24/06/2004 27.00

The code the way it is doesn't necessarily find the last time it was created and I am not sure how to incorporate the date into the code.

Any help is appreciated.

Thanks

Aaron

 

[scriverb]

Try using the following. I included an additional criteria expression which uses the DMax function to select the Max date which is necessary to select the correct record:

DLookup("[Price]", "[History]", "[ItemNumber] = '" & Me.ItemNumber & "' and [Date] = " & DMax("[Date]", "[History]", "[ItemNumber] = '" & Me.ItemNumber & "'"));"

Let me know if this works for you.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[matethreat]

I tried your suggestion and I was getting errors in the code with the ;" on the end of the code. So, I removed them and did not get any errors, but I am not getting any information in my text box either.

I am not sure what is going on.

 

[scriverb]

Sorry about that. Change it to this:

& "'"))

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[gol4]

If indeed itemnumber is a number field and not text then there is not a need to surround it with quotes. That would also cause an error. If it is text then pardon my interruption.

 

[scriverb]

No problem. I was first checking to see if the original indication of text was correct. Because the original select had the single quotes I was giving the benefit of the doubt especially because the matethreat was indicating that it was finding records but not necessarily the correct one.

Thanks for the input though.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[matethreat]

Thank You for your replies. I didn't actually need the DLookup function to get the information I was looking for. The DMax function gave me the information on it's own.

Thanks For the help

Aaron

 

[scriverb]

Just curious. Please post the DMax code that you used here so we may all see it.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[matethreat]

Here is the code I used.

Me.Text60 = DMax("Date", "[History]", "ItemNumber='" & Forms!JobInfo!ItemNumber & "'")

Thanks, Again

 

[scriverb]

Your original posting stated that you wanted to find the "Price" of the item the last time it was completed. What you have shown here is finding the last Date it was completed. There is no indication of the Price here. The DLookup function that I provided above will in fact give you the Price of the record with the last date it was completed.

I would look carefully at what you are doing here unless you have changed you mind. You see the DLookup function that I provided uses the DMax function within it to select the correct record.

Post back with any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[matethreat]

You are right that is what I am looking for, and I think I was just glad to get a number that looked right showing up, but now that I look at the code it will not be correct. The code you gave me earlier was not working.

Me.Text112 = DLookup("Price", "[History]", "ItemNumber='" & Forms!JobInfo!ItemNumber & "' AND Date = #" & DMax("Date", "[History]", "ItemNumber='" & Forms!JobInfo!ItemNumber & "'"))

The Dmax function on its own works and the dlookup function without the dmax function in it works on it's own. But, when you combine the 2 it doesn't like it.

I am getting the following error:

Syntax error in date in query expression 'Item Number='208010' and date=#17/12/2003'

It looks like I need a # at the end of the expression but everything I have tried has failed.

 

[scriverb]

Give this a try:

Me.Text112 = DLookup("Price", "[History]", "ItemNumber='" & Forms!JobInfo!ItemNumber & "' AND Date = #" & DMax("Date", "[History]", "ItemNumber='" & Forms!JobInfo!ItemNumber & "'") & "#")

Now I am a little concerned about the date that is being returned here. I am seeing from the error message a dd/mm/yyy y format. I have never had much luck with that format in expressions but let's see how it works and we can address that issue later.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[matethreat]

Hi Bob,

I could have sworn I tried that one, but quite obviously I didn't, because it seems to be working. But I have a really odd problem with it. For certain item numbers it doesn't seem to be grabbing the information. It works about 80% of the time.

?????

 

[matethreat]

The Really odd thing is for a test I have done a dcount of the matching records with the item number and date to see if it's finding the record and it is finding one matching record for those numbers. So I am really unsure why it is not returning the data only part of the time.

 

[scriverb]

Like I said previously I noticed that I noticed your date in the dd/mm/yyyy configuration. I have had problems with this previously. Do you have your default settings for date format and entry in this configuration?

Me.Text112 = DLookup("Price", "[History]", "ItemNumber='" & Forms!JobInfo!ItemNumber & "' AND Date = #" & Format(DMax("Date", "[History]", "ItemNumber='" & Forms!JobInfo!ItemNumber & "'"), "mm/dd/yyyy") & "#")

If so give the above a try and see if you get better results.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]