Pulling prior month on the 1st of the month, current month on all other days.

[DITTYPOP]

I have a data connection to an access DB appended table and need to pull prior month data on the 1st of the month and current month data on all other days.

Table name: CI_AND_P_INVENTORY_APPEND
Field with date data: RPT_DATE

SELECT *
FROM CI_AND_P_INVENTORY_APPEND
WHERE
?

Please help, Thank you in advance.

 

[Micron]

Answer depends on how you're calling this query. If via form (as it should be) then the command button code can get the month from DatePart of the current month (in a calculated form control) and subtract 1 from it if the day of DatePart of the current month is 1. If that's not clear, research the DatePart function here http://www.techonthenet.com/access/functions/
otherwise, an expression might be required if you're doing this direct in a query. Not sure if this will work as written, but it looks good in my head

WHERE DatePart("m",tbl.RPT_DATE) = (DatePart("m",Date) - IIF(DatePart("d",Date)=1,1,0))

The IIF part should return 1 or 0 based on whether or not the current month day is the 1st. The criteria should then become the current month or current minus one month and get tbl values where the month part is equal to that. Note that the DatePart function has two parameters you may need to set. I also shortened your table name for my convenience.

 

[DITTYPOP]

I am trying to automate this and adding parameters wouldn't help. When I change the "Date" to values in the table , (DatePart("m",tbl.RUN_DATE) ,it either brings back last 30 days or all data less 1 date Depending on RPT_DATE or RUN_DATE. How would I substitute "Date" for todays date? Today() didn't work either.

 

[DITTYPOP]

Never mind prior, date() works. Thank you for the answer.....

 

[Micron]

I forgot the () part of Date ().
Glad you solved it.

 

[xenou]

I have found in practice that although just subtracting a day on the first day of the year is good (particularly for automated reports) it is awkward for some reports, since, for example, the first day of the month could be on a Sunday and on the following day, Monday, you still want to see that final report with the entire last month's data. Would that be a concern here?