how do i differentiate 31 days month from 28 days month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i want to use access query to determine total bookings for the previous month
automaticallyat the beginning of each new month. What formula in access
language would do that taking into consideration the fact that all months are
not the same number of days
 
i want to use access query to determine total bookings for the previous month
automaticallyat the beginning of each new month. What formula in access
language would do that taking into consideration the fact that all months are
not the same number of days
Click to expand...

Always for the previous month?
As Criteria on the Date field:

Format([DateField],"mm/yyyy") =
Format(DateSerial(Year(Date()),Month(Date())-1,1),"mm/yyyy")
 
The following criteria will give you all records for the month prior to the
current one.

= DateSerial(Year(Date()), Month(Date())-1,1) and < DateSerial(Year(Date()),
Click to expand...
Month(Date()),1)


You could also use a calculated field
Field: Format(BookingDate,"yyyymm")
Criteria: Format(DateSerial(Year(Date()),Month(Date())-1,1),"yyyymm")

Or several other solutions.
 
Thank you for your help. The first one " >= DateSerial(Year(Date()),
Month(Date())-1,1) and < DateSerial(Year(Date()),
 
Thank you for your help. The first one " >= DateSerial(Year(Date()),
Month(Date())-1,1) and < DateSerial(Year(Date()),
Click to expand...

The information is there on your computer.
Check VBA Help files for more information about the DateSerial
function.
 
When ever I deal with the no of days within a month a always do the following
to find the last day of the month (Including Leap Years) and end of years.

LastDay: DateAdd("d",-1,DateAdd("m",1,DateSerial(Year(Now()),Month(Now()),1)))


it works in 3 steps.

1) Find Current Year & month and force first day of month

ie DateSerial(Year(Now()),Month(Now()),1)

2) Add 1 moth to previous date

ie. DateAdd("m",1,Date)

3) Finally take 1 day away to previous date in two

ie. DateAdd("d",-1,Date)


you can put it in one step

ie
LastDay: DateAdd("d",-1,DateAdd("m",1,DateSerial(Year(Now()),Month(Now()),1)))

Trev B
 
When ever I deal with the no of days within a month a always do the following
to find the last day of the month (Including Leap Years) and end of years.

LastDay: DateAdd("d",-1,DateAdd("m",1,DateSerial(Year(Now()),Month(Now()),1)))

it works in 3 steps.

1) Find Current Year & month and force first day of month

ie DateSerial(Year(Now()),Month(Now()),1)

2) Add 1 moth to previous date

ie. DateAdd("m",1,Date)

3) Finally take 1 day away to previous date in two

ie. DateAdd("d",-1,Date)

you can put it in one step

ie
LastDay: DateAdd("d",-1,DateAdd("m",1,DateSerial(Year(Now()),Month(Now()),1)))

Trev B
Click to expand...


Using DateSerial, day 0 of a month will return the last day of the
previous month.

=DateSerial(Year(Date()),Month(Date()),0)

today returns 3/31/2006

To find the last day of this current month:

=DateSerial(Year(Date()),Month(Date())+1,0)

today returns 4/30/2006
 
Back
Top