Query on Specific Field's Change in Timestamp

[Kiley]

In this scenario: I have two tables - One is the T_NBQuLog table which records all new incoming quotes. There is a relationship between this table and my T_NBQUStatus table in which users can choose if the quote is In Progress, Proposed, Not Written, or Written. I would like to record the date stamp on this one field - not the entire record - when it is updated - as there are reports i want to be able to run that depend on when this status changes.

So i added a field to my T_NBQuLog table called NBQuStatusTimestamp and on my after update event on the Status combobox, i have the code me.NBQUStatusTimestamp = Date



This worked and when i update this field, it updated NBQuStatusTimestamp properly, but now I am trying to build a query and am not sure how to word my nested IIF statements...

IF the status is Proposed/In Progress - i want to see all records through current date, regardless of timestamp
IF the status is Written/Not Written - i want to see all records where the NBQuStatusTimeStamp was updated for the current month

I know i'm close to getting this. But i just can't seem to figure out how to word this expression. Any help is greatly appreciated. I hope i've explained everything clearly. Thanks in advance for your generosity!

[aytee111]

Field1: If proposed/in progress, true, false - criteria =True
Field2: If written/not written, true, false - criteria on different line to Field1, = True
Field3: If timestamp= current month, true, false - criteria on same line as Field2, = True

In English - if the status is proposed or in progress then show that record, OR if the status is written/not written AND the date is current month then show that record.

[Bulzie]

Not sure if this is what you are looking for. Will need to adjust to your exact tables/fields. That last part gets the month and year of the timestamp and month year of current date to see if a match.

SELECT T_NBQuLog.NBQuLogID, T_NBQuLog.Quote, T_NBQUStatusID.NBQUStatus, T_NBQUStatusID.NBQuStatusTimestamp, DatePart("m",[NBQuStatusTimestamp]) & DatePart("yyyy",[NBQuStatusTimestamp]) AS cMonthYear
FROM T_NBQuLog INNER JOIN T_NBQUStatusID ON T_NBQuLog.NBQuLogID = T_NBQUStatusID.NBQuLogID
WHERE (((T_NBQUStatusID.NBQUStatus)="Proposed") AND ((T_NBQUStatusID.NBQuStatusTimestamp)<=Date())) OR (((T_NBQUStatusID.NBQUStatus)="Written") AND ((DatePart("m",[NBQuStatusTimestamp]) & DatePart("yyyy",[NBQuStatusTimestamp]))=DatePart("m",Date()) & DatePart("yyyy",Date())));

[Kiley]

Thanks all for your help! I got it