I have a transaction table which holds all records with posting date on each of them. I used a make table query to find out the max date hold in the transaction table and use the dateAdd function to find the date with 1 month, 5 months and 6 months ago and export it into a table name TRSMPD that only have one record.
I then use the TRSMPD table to query the transaction table to find the records that has dates greater than the value in the 1mth field.
The TRSMPD table structure is
Field Name
Expand|Select|Wrap|Line Numbers
- MaxOfPOSTING_DA
- 1mth
- 5mth
- 6mth
I build the following query to find the records but it returns no records
Expand|Select|Wrap|Line Numbers
- SELECT Transactions_File.PLANT, Transactions_File.MATERIAL_N, Transactions_File.POSTING_DA, Sum(([QUANTITY_I]*-1)) AS Qty
- FROM Transactions_File LEFT JOIN TRSMPD ON Transactions_File.POSTING_DA = TRSMPD.MaxOfPOSTING_DA
- WHERE (((Transactions_File.PLANT) Like "DC*") AND ((Transactions_File.POSTING_DA)>[TRSMPD]![1mth]))
- GROUP BY Transactions_File.PLANT, Transactions_File.MATERIAL_N, Transactions_File.POSTING_DA;
It also works with input field for date criteria
I also tried using DateSerial and Format function in the criteria and Access gives me error message telling data type mismatch
When I use CDate function, Access error message tells Invalid use of Null
I want to make this query fully automatic that will update when new records are added and does not require any manual input when the query runs. Please advise.