Update to SQL statement

[zbaker]

I have the following append query that each month add a new line to my records if it meets the criteria. I need to add to the criteria but i am not sure what to add. Currently it add a new line if the Contract Status is Active, I now need to to add a new line if there is no date for that record. So "WHERE (((FINANCEACCRUALSPREADSHEET.[Contract Status])="Active") AND [Accrual Month] Is Empty)"

INSERT INTO AccrualUpdateTable ( AccrualRefID, [Accrual Month] )


SELECT tblMASTERDeviceTable.AccrualRefID, DateSerial(Year(Date()),Month(Date()),1) AS NewDate
FROM tblMASTERDeviceTable INNER JOIN FINANCEACCRUALSPREADSHEET ON tblMASTERDeviceTable.AccrualRefID = FINANCEACCRUALSPREADSHEET.AccrualRefID
WHERE (((FINANCEACCRUALSPREADSHEET.[Contract Status])="Active");

[June7]

If there is no date in AccrualUpdateTable?

Try:

[Accrual Month] Is Null

or

IsNull([Accrual Month])

[zbaker]

If a new record was added then there is no date for that record for the month it was added unless done manually. The suggestions listed above did not apply the IS Null criteria, it appended to all records.

[June7]

Sorry, my error, filter criteria on destination table fields is meaningless for an INSERT action, I was thinking UPDATE. Not really understanding the issue. What do you mean by 'if a new record was added then there is no date'? How can there be no date when the INSERT action just added records with date value?

[zbaker]

I am using a parent child relationship question. Parent is the Device record, Child is the month record. The append query is used to add a new month to the device record. When a new device record is added a date is not assigned to the new device. I was hoping to add criteria to the append query to say that when a new device is added then append a month record with that device record.

[June7]

Adding records to two tables?
That would be 2 separate SQL actions.