Need help with a lookup please

[Buns1976]

Hi Everyone,
I need some help with the lookup I attached below. The "LotterySales" table also has an "InventoryDate" field.

Since the "LotterySales" table will have numerous entries with the same "BOOKNUMBER", I need to
look at the most recent "InventoryDate" field in the "LotterySales" table and return "POSSOLD" if it exists
on the most recent "InventoryDate". If not then return "0".

I hope that makes sense?

DLookUp("POSSOLD","LotterySales","BOOKNUMBER=" & [BookNumber])

Thank you!

 

[welshgasman]

I must admit, I was wondering what the "3" was for, until I assumed it was just a placeholder for Me.BookNumber ?

 

[xenou]

I dont' have your database so I used a very small table with a few records in it for testing. You will need to adapt for your actual situation (whatever that is - I assume you have a form or report based on what I see I really don't know). I couldn't use "BookNumber = [BookNumber]" because there is nothing in my database that that name would point to. so I used "BookNumber = 3".

 

[Joe4]

Cross-posted here: http://www.accessforums.net/showthread.php?t=76035

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Buns1976]

Hi,

The "syntax error in date" is the error message that was displayed when I tried to RUN it in a query as you suggested!

[COLOR=#333333]select Nz(DLookUp("[POSSOLD]", "[LotterySales]", "BOOKNUMBER=" & "3" & " AND InventoryDate=#" & DMax("[InventoryDate]", "[LotterySales]", "BOOKNUMBER=" & "3") & "#"),0) as Result[/COLOR]

Data Types are Date/Time for InventoryDate and Number for BOOKNUMBER & POSSOLD!

Thanks!

 

[Buns1976]

Xenou,

In the table and form below you can see what is happening. On 3/10/2019 POSSOLD in the Table and POSold in the form match.
On 3/11/2019 and 3/12/2019 they do not because the date is not part of the array.

Also this is a trimmed down version. There will be approximately 60 BOOKNUMBERs we would lookup and return POSSOLD each day!


I attached a Dropbox link to the database below.

https://www.dropbox.com/s/qocxjdd20n83626/LOTTERY1.accdb?dl=0


[TABLE="width: 341"]
<tbody>[TR]
[TD]TABLE[/TD]
[TD]LotterySales[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]POSDATE[/TD]
[TD="align: center"]BOOKNUMBER[/TD]
[TD="align: center"]POSSOLD[/TD]
[/TR]
[TR]
[TD="align: center"]3/10/2019[/TD]
[TD="align: center"]702216[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]3/11/2019[/TD]
[TD="align: center"]702216[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]3/12/2019[/TD]
[TD="align: center"]702216[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FORM[/TD]
[TD]Inventory[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]InventoryDate[/TD]
[TD="align: center"]BookNumber[/TD]
[TD="align: center"]POSSold[/TD]
[/TR]
[TR]
[TD="align: center"]3/10/2019[/TD]
[TD="align: center"]702216[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]3/11/2019[/TD]
[TD="align: center"]702216[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]3/12/2019[/TD]
[TD="align: center"]702216[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[xenou]

I'm not sure what you mean when you say the date is not part of the array. I see dates in your data.

Are you adding additional criteria to your original question? Maybe you need to restate the question. Also there is no explanation of why the syntax fails yet. If you have dates and numbers there should be no syntax problem (and as I said it works fine for me).

I don't quite understand the data you posted. Why did you post something that looks like a table that is called "Form"? It's not really clear how you are using this formula. I cannot download dropbox files at work. My formula works on one table. I don't really know what is going on with this Form + Table setup now.

 

[Buns1976]

Hi,

The form I posted above in in Datasheet view as example where the number from POSSALES should go in the form by DATE. I made an error in my original post which said

The "LotterySales" table also has an "InventoryDate" field.

. The LotterySales table DOES NOT have a field named InventoryDate, the form does. The DATE field in the LotterySales table is POSDATE. Sorry about that!

I modified the code but still do not have it correct. When I run it in the test query, it shows error which highlights "as". If I could figure out how to post a screenshot it would be helpful.

select Nz(DLookUp("[POSSOLD]", "[LotterySales]", "BOOKNUMBER=" & " AND POSDATE=#" & DMax("[POSDATE]", "[LotterySales]", "BOOKNUMBER=" & "#"),0) as Result

Thank you!

 

[xenou]

The last formula you posted would be invalid because you don't have BookNumber being equal to anything.

select Nz(DLookUp("[POSSOLD]", "[LotterySales]", "BOOKNUMBER=" & " AND POSDATE=#" & DMax("[POSDATE]", "[LotterySales]", "BOOKNUMBER=" & "#"),0) as Result

That's basically a criteria that says "booknumber = "

And as you would immediately know in any other context, you can't have a query that is "select * from LotterySales where BookNumber = " and not have a booknumber.

You should practice with other DMax and DLookup functions so you can get the hang of it. You should be able to get a result for the DMax function separately and independently (that's the inner DMax function). Then get a result for the outer DLookup function using a hard coded date. Then (and only then) put them together with the DMax nested inside the DLookup.

Probably you should also avoid using nested D- functions too. I do that about once every 3 years, and then only if I have too. It's not worth the pain.