Query a date range using a date field from other table

[nhkam]

I am using MS Access 2007
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

MaxOfPOSTING_DA
1mth
5mth
6mth

with data type Date/Time

I build the following query to find the records but it returns no records

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;

I have test it with a date value to replace the 1mth field name as criterna and it works.
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.

[ChipR]

Try replacing [TRSMPD]![1mth] (you just can't do that) with:
(SELECT MAX(1mth) FROM TRSMPD)

[NeoPa]

If you have a single record in the table of a global nature (IE. That record pertains to all data), then there is no need for a JOIN in your FROM clause. You simply need to separate the two tables with a comma (,).

[NeoPa]

I suspect the shriek (!) character may cause a problem, but you would want to use [TRSMPD].[1mth].

[nhkam]

Hi ChipR,
The query give me a syntex error message when using the code you suggest.
By the way the TRSMPD table only have one record, so, I don't understand why I need to use max function.

[nhkam]

Hi NeoPa,

All I need to get is a list of records from transaction file that has the posting date from the most current date backward to one month ago in the record. eg. most current date in record is March 15, 2009, then I need to list all records that is greater than February 15, 2009.

I change the shriek to dot but still not getting any record from the query.
Any other way I can try ?

[NeoPa]

Did you read my post #3?

[OldBirdman]

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.

The following logic would do as requested without the need for a table for the newest date.

Dim dteNewest As Date
Dim dte1Ago As Date
Dim dte5Ago As Date

dteNewest = DMax(...
dte1Ago = DateAdd("m", -1, dteNewest)
dte5Ago = ...

strWHERE = "WHERE (((Transactions_File.PLANT) Like 'DC*'  AND ((Transactions_File.POSTING_DA)>" & dte1Ago & ")) " 

strSQL = strSELECT & strFROM & strWHERE & strGROUPBY