 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / September 2005Dates
I am trying to do a select query on one table. This table has many records with a DATE type field in each record. I would like to select all records from the table with a given month and year, the day is not important in this query. I would like to make this a "parameter" driven query. My intention is to be able to enter Month and Year into my prompt and select the appropriate records. My prompt I believe is going to look for "mmddyyyy". Any help would be appreciated. Greg > I am trying to do a select query on one table. > This table has many records with a DATE type field in each record. [quoted text clipped - 7 lines] > > Any help would be appreciated. If YourDateField has no time component (all at midnight)... WHERE YourDateField BETWEEN DateSerial([Please Enter Year], [Please Enter Month Number], 1) AND DateSerial([Please Enter Year], [Please Enter Month Number] + 1, 0) If YourDateField has a time component then make the final zero a one. If there is a chance that some records do have a time of exactly midnight then I would add 23 hours, 59 minutes and 59 seconds (or 82859 seconds) to the second value... WHERE YourDateField BETWEEN DateSerial([Please Enter Year], [Please Enter Month Number], 1) AND DateAdd("s", 82859, DateSerial([Please Enter Year], [Please Enter Month Number] + 1, 0)) Don't be concerned about the two parameter markers beign entered twice. As long as you make each pair identical the user will only be prompted once for each.  SignatureI don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com >I am trying to do a select query on one table. >This table has many records with a DATE type field in each record. [quoted text clipped - 9 lines] > >Greg Since day is not important, I wouldn't include it in my lookup format. Suggestion: Have your entered paramters, as shown in SQL, look something like this: WHERE Month(DateField) = [Enter Month Number] And Year(DateField) = [Enter year]. Yes, it means two entries, but that's easier than putting confusing data (the day) in an entered parameter. SignatureSam Sam and Rick, Thank you both for your response! Your suggestions are similar and worked out very well for me. My problem is resolved. Thank you, again Greg > I am trying to do a select query on one table. > This table has many records with a DATE type field in each record. [quoted text clipped - 9 lines] > > Greg |
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.