Go back to first day of previous month regardless of date entered

[spudracer]

I've got a query where I need a date calculated based on a date entered.

For instance, I have a report (paper report, not Access report) that has a due date of say July 13th. What I want the query to do is based on whatever the due date is, automatically roll back to the first day of the previous month.

Can this be done with the DateSerial function, or is there a different method?

More information, if needed. The due date (which is based on the person) is stored in one table (tblPersonnel), while the report info is stored in another table (tblReports), they are joined by a PK and FK.

I'm using Access 2007.

 

[ajetrumpet]

What I want the query to do is based on whatever the due date is, automatically roll back to the first day of the previous month.

mathematically, i suppose this :

SELECT [DUEDATE],

month(dateadd("m", -1, [duedate])) & "/01" & 

year(dateadd("m", -1, [duedate]))

probably other ways. and u may want to check that syntax on the dateadd. not sure if the interval "m" is for month or not. might too, want to use the conversion CDATE function on the whole expression too. or, FORMAT(), with a "short date", etc... argument in it.

 

[spudracer]

mathematically, i suppose this :

SELECT [DUEDATE],
 
month(dateadd("m", -1, [duedate])) & "/01" & 
 
year(dateadd("m", -1, [duedate]))

probably other ways. and u may want to check that syntax on the dateadd. not sure if the interval "m" is for month or not. might too, want to use the conversion CDATE function on the whole expression too. or, FORMAT(), with a "short date", etc... argument in it.

Now, is that altogether or are those just seperate ways of doing it?

So if seperate, I would have it as:

Report Due: month(dateadd("m",-1, [duedate])) & "/01"

Right?

 

[Brianwarnock]

Reportdue: Dateserial(year(duedate),month(duedate)-1,1)

Brian

Note this will automatically adjust the year

 

[spudracer]

Ok, figured it out. Now, the question is how to change between two different periods automatically, depending on the report.

There's only two different time periods, one is 5 months out, the other is a month out. I've got about three different reports that require a 5 month prep time and three reports that require about a month of prep time. How can I set this so that, based on the report, it will automatically display the correct date?

 

[Brianwarnock]

The question is what did YOU figure out, then we can respond.

Brian

 

[spudracer]

The question is what did YOU figure out, then we can respond.

Brian

Brian, before you posted, I had noticed the DateSerial code in a simliar database that will be replaced by what I'm creating. I was receiving an #Error with a record that I was using as a test record, but then realized that there wasn't a date for that persons transfer date, which is why it was returning the error. So, as I had said before, that's taken care of.

I just need to know if I need to move over to the VBA forum to post the next question of how to have the dates change based on the type of report chosen.

 

[Brianwarnock]

So you are using dateserial, in that case the Month(field)-1 can be replaced by Month(duedate)-timeperiod assuming timeperiod is an integer, does that help

If timeperiod is not actually carried anywhere then maybe even
Month(duedate)-iif(reporttype="A",1,5) might work but I've never tried it.

Brian

 

[spudracer]

I'll go ahead and ask a question about it over in the VBA forum, thanks Brian!