Month occurred minus 1

[smithbr]

I have a query set up right now that pulls commission information for a data range the user types in through start and end dates. Now, I want to show in the query the previous balance for the broker...(each month, the balance is stored by broker, date, and balance) Essentially I need to find out the criteria to put into the date field that says month(paid)-1 but this will not work. (The date range the user types in gets records taht were paid within the range)

I have used the expression Month(Date-Day(Date)) before but that only finds the month previous to the current,a nd the range will not be the current month. How can I change this expression to use the month paid instead of current?

 

[substitute]

=dateadd("m",-1,[paid])
=dateserial(year([paid]),month([paid])-1,day([paid]))

give a calendar month before the date indicated by [paid]

Dateserial is useful if you want the 1st of the previous month as you just use 1 for the last argument

 

[smithbr]

I can not get that to work...the Paid criteria is "Between [Enter Start Date] And [Enter End Date]" I think this is messing access up. If I input 7/1/03 and 7/30/03 as the start adn end dates.....I want the balnces for 6/2003...When ever I run it using the code you suggested, I get zero results for my query.

 

[substitute]

I misunderstood your original question, and still don''t fully understand. Do you want an integer month number or a date value? The functions above return a date, if you want a month numbeer you have to wrap month() round the function.

In your month(date-day(date)) expression you can substitute a field value for date to get the number of the month before the date in the field value. Note that the month before 1 is 12 but the year is one lower!

This criteria text (for a field with a date value) will select records where the date in the field lies one month before the range the user enters:

Between dateadd("m",-1,[Enter Start Date]) And dateadd("m",-1,[Enter End Date])

If the user enters 7/1/03 and 7/30/03 the dates from 6/1/03 to 6/30/03 will be selected. If the current month has 30 days or less you can't select the last day(s) of the previous month as the end date entered won't be valid. I'm not sure there is a way round this unless you force the selection to the first and last days of a month. That is where dateserial() comes in - the date of the last day of any month is one less that the date of the first of the next month:

dateserial(year([dateparam]),month([dateparam]),1)

and

dateserial(year([dateparam]),month([dateparam])+1,1)-1

are the first and last dates in the month containing date [dateparam]

Using some combination of dateadd(), dateserial(), month() etc. should get you to a solution.