 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2006how do i differentiate 31 days month from 28 days month
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 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")  SignatureFred Please respond only to this newsgroup. I do not reply to personal e-mail 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()), 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. > 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 Thank you for your help. The first one " >= DateSerial(Year(Date()), Month(Date())-1,1) and < DateSerial(Year(Date()), > Month(Date()),1) " worked. I am an A level student who just started computing so i do not really understand how the code you gave my works. Could you please explain it to me if it's not to much of a bother. > The following criteria will give you all records for the month prior to the > current one. [quoted text clipped - 12 lines] > > language would do that taking into consideration the fact that all months are > > not the same number of days > Thank you for your help. The first one " >= DateSerial(Year(Date()), > Month(Date())-1,1) and < DateSerial(Year(Date()), [quoted text clipped - 16 lines] >>> language would do that taking into consideration the fact that all months are >>> not the same number of days The information is there on your computer. Check VBA Help files for more information about the DateSerial function. SignatureFred Please respond only to this newsgroup. I do not reply to personal e-mail 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 > 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 > 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. [quoted text clipped - 26 lines] >> language would do that taking into consideration the fact that all months are >> not the same number of days 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 SignatureFred Please respond only to this newsgroup. I do not reply to personal e-mail |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.