Date Query Question

[jedooley]

I have this query which is selecting the current days inventory. Can somoene tell me how I can write an SQL statement so it selects not just the current day but also the most recent days inventory for fields which are not entered on that date.

I have inventory locations but one might be from Mon, another might be from Tue, etc. I imagine it is the where clause needs something added to it.

WHERE ((([Inventory].[InvDate])=[Enter Date:]))

 

[KenHigg]

Between #[Start date:]# and #[End date:]#


???
kh

 

[KenHigg]

My bad:

WHERE ((([Inventory].[InvDate]) Between [startdate] And [enddate]));

???
kh

 

[jedooley]

The between Date thing won't work.

I want the most current Inventory level regardless if it was today. So I want 1 reading per location. some might have Mon levels, some might have wed for example.

 

[KenHigg]

So location 1 will have a date range, location 2 will have it's own date range etc?

kh

 

[jedooley]

I need the most current record.

For ex.

Location 1 Mon 45
1 Tue 10
Location 2 Tue 10
location 3 today 55

A date range won;t work because I don't want it to select duplicate records

 

[KenHigg]

WHERE ((([Inventory].[InvDate])=DMax("InvDate","Inventory")));


???
kh

 

[jedooley]

That is progress, but it is just returning records for the most current date entered which is today. I need it to do that plus return records for the other locations for the most current date entered, which is not today.

thanks for helping. I am sure there is a way to do this.

 

[KenHigg]

SELECT Inventory.Location, Max(Inventory.IncDate) AS MaxOfInvDate
FROM tblMain
GROUP BY Inventory.Location;

???
kh

 

[jedooley]

Nope that does not work, That returns all records. I should have between 10-12 records returned

See attachment