max date without future date

[crich21]

I can't seem to find any threads pertaining to future dates in queries. I have attached a db that I need to have the query show the most current date that is not a future date. I tried using the max function but I am getting multiple records. I am not understanding the other threads about using 2 queries. I tried this but evertime I add the PartID to any query I get multiple results. Can anyone help with this one. I am sure it is very simple but I have been struggling with it for over 2 hours now.

 

[Jon K]

To filter records for AppDate <=Date() in a Totals query, you need to use Where instead of Group By.


I have added a series of two queries in your database:
"qryOne" is a Totals query that finds the max AppDate (excluding future dates) for each PartID from table tblCostHistory.

"qryTwo" links qryOne back to the table to return the original records.


You can combine the two query SQL statements into one, but Access has the habit of changing some brackets to square brackets [ ], sometimes making it difficult for you to edit the SQL statement subsequently.

As an illustration, I have included a query "qryUsing a one-query approach", which should return the same results as "qryTwo". (In Access 97, you can't open this query in query Design View.)


Hope it helps.

 

[crich21]

Thank you Jon K

Yes that helps, I was unaware of the purpose of the where function. Every thread I read was only about the max function. Thank you very much.

 

[crich21]

One More Thing

This db is driving me crazy. I incorporated a tblcosthistory. Which worked fine until I realized that I couldn't change the effective date of a price change. The problem I have is this:

I need to be able to change the cost of a part from the frmvendors. However the price comes from a table that is created by a query. Good design? I have no idea. I couldn't think of another way. So when the price is updated I need to be able to update the tblcurrentcost and show the new prices in the frmvendors, but the tblcurrentcost is locked because it is in use. Does anyone have a suggestion how I can work around this. Also I might have to run the qryupdatecurrentcost in the on open event of frmvendors so the prices are current when opened. Also is there a way to eliminate the acces error of "This table will be deleted" everytime the qryupdatecurrentcost is run.

maybe I shouldn't be using a make table query but I couldn't figure out how to update an existing table with a query.

Any suggestions would be greatly appreciated. I may be going about this all wrong. But the main purpose is to be able to view the most current cost that is not a future cost and be able to edit it from the frmvendors.

Thanks in advance.

 

[crich21]

Sorry everyone

I have two relevant posts going on. Please refer to this post. Sorry but I don't want to drag other problems into this post. Thank you Jon K for helping me with the max date 2 query method, this does exactly what I want.

Other relevant thread