Need help with a lookup please

[Buns1976]

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



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

Thank You!

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

[ranman256]

IF the text box on the form is called BOOKNUMBER
and IF the field BOOKNUMBER is a string
then

DLookUp("POSSOLD","LotterySales","BOOKNUMBER='" & me.BookNumber & "'")

you use brackets on fields in queries, you don't need them in code.
string fields need quotes , numeric ones do not.
you don't use dlookup in a query.

[Buns1976]

Thanks ranman,
My point was that I need to look up the "BOOKNUMBER" with the LAST "POSDATE" in the LotterySales table and return the "POSSOLD" number from LotterySales table to the "POSSold" field in my "Inventory"form.

Thank you!

[Micron]

Does that mean you have the solution you need?
Wondering why you don't just calculate this in your query and bind the form control to that field instead of DLookup...

[Buns1976]

Hi,
No I don't have the solution! Not that proficient in Access so not sure what you mean by "Calculating this in your query"?

Thanks!

Does that mean you have the solution you need?
Wondering why you don't just calculate this in your query and bind the form control to that field instead of DLookup...

[Micron]

you can create an expression whose result becomes a calculation in a field that you alias, such as

Expr1: Max(POSDATE)

However, upon reflection I don't think that will give you what you want as that will simply be the Max of all the dates in that field, regardless of what's in any of the other fields that matter. Likely to do this in a query would require a subquery, so best to stick with the road you're on. However, you wrote

I need to look at the most recent "POSDATE" field

You could mean on the most recent date for each POSSOLD
or
you only want a match IF the most recent date happens to belong to a BOOKNUMBER that is on the form record. If the max date doesn't belong to that value, then return 0.

You should post a data sample and the expected results. I suggest you throw together 2 little "tables" in Excel, centre and copy the data and paste it here. You'll get a little html table whose properties you can adjust from the toolbar after "Go Advanced" is clicked in your post.

[Buns1976]

Ok here you go!

"POSSOLD in "LotterySales" table should match "POSSold" in the "Inventory" form by date!
As you can see it keeps returning "2" in the form because its the FIRST value in the table!

LotterySalesTable
POSDATE	BOOKNUMBER	POSSOLD
3/10/2019	702216	2
3/11/2019	702216	3
3/12/2019	702216	4
InventoryForm
InventoryDate	BookNumber	POSSold
3/10/2019	702216	2
3/11/2019	702216	2
3/12/2019	702216	2

[Micron]

That looks like your result, which I didn't ask for. I asked for data and expected result, not what you say is the wrong result.
Based on

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

you want to return 4. 4 because it exists on the most recent date of 03/12/2019
Plus, a DLookup can only return one value from one field in one record, so why show me three 2's? I'm guessing you want it to return 4 but you still haven't revealed the desired outcome. At least, not that I can ascertain.

[Buns1976]

Micron,
The expected result is

"POSSOLD in "LotterySales" table should match "POSSold" in the "Inventory" form by date!

Was that not clear?
It's obvious looking at the tables in the previous post that we want POSSOLD in the LotterySales table to match POSSold in the Inventory form by date is it not?

Under my user name it says "Novice" which by definition means "a person new to or inexperienced in a field or situation" so if I knew what I was doing, I'm fairly certain I wouldn't
be in this forum asking for help. We are just looking for a way to make this work.

Thanks!

[Micron]

I observe the rankings when I reply but it doesn't mean a whole lot. You can make it anything that fits as you can probably tell from mine. I've seen it happen where people who post here that can't be taught, don't follow directions or provide answers to questions and create a thread on almost a daily basis migrate from novice to VIP. It's based on the volume of posts and not much else. You could be an expert who didn't bother to alter the default level, although I try to read from the problem what someone's level is - at least in regard to the portion of Access that they're posting about.

Based on your question, I gathered novice, which is why I asked for both data and expected results. A novice wouldn't necessarily know that the same characters can be different data types, such as 03/15/2019 being date or text. Or they don't understand the impacts of OR versus AND when trying to get data based on conditions. I could go on, but the point is that examples of data and desired results usually remove the ambiguity of statements like yours; ambiguity that exists because the person may not know there is more than one way to interpret those statements. So no, it was not clear. If it was I wouldn't have sought examples. Many "experts" here, including myself (if I may be so bold) have replied based on an interpretation only to have another expert point out an alternative interpretation of the question. We find out who's correct when the OP finally replies.

I wish I had a dollar for every time I've solved exactly what was asked for only to find out they didn't understand that what they were wanting wasn't expressed properly. You can stand on your message if you prefer, and I might be able to decipher your meaning eventually. However I'm going to say that after 5,100 posts I've reached the point where I'd rather bow out than beg, justify or argue. It has taken me far longer to compose this than unsubscribing from the thread, which is the easier option, so I hope you appreciate the a little bit of insight that I might have been able to provide about forum member rankings and why people ask for certain information.

I wish you good luck with your issue. I'm sure someone else will take over and don't think the solution will be all that difficult in the end.

[Micron]

I see you cross posted at mr excel and were called out on it. Also see that you didn't acknowledge it and continued to post. Also see that you're confusing everyone there, which is what I was trying to avoid. Also see you being added to my ignore list now. You can reply as you wish, but I won't see it.