Jun 9 2005, 04:31 AM
I'm trying to create a query for a training course database. The courses are booked in on specific dates, but the same course can take place on multiple dates. i want to set a date parameter which will tell me what courses are due to take place in a particular month. for example, i type in 'July' and it tells me what courses are due then. I think I will also have to add the year as well.
is this possible? i don't find date functions in Access particulary easy, so any advice much appreciated.
Jun 9 2005, 04:37 AM
In your query after adding the fields you wish to see add a new field as follows:-
Field: MonthofCourse:format(Month([CourseDate]),"mmmm") - coursedate is the date the course is taking place
Show: either ticked or not depending if you want to view the month
Criteria: [Please enter month] - or similar
This should work for you.
Jun 9 2005, 04:40 AM
Oops forgot about adding the year.
format(month([Date]),"mmmm" & " " & format(year([Date]),"yyyy")
Criteria [Enter month and year] - ie June 2005
Add an extra field to test the year in a similar way to the month
Jun 9 2005, 05:07 AM
thanks for the advice. i see in principle what its trying to achieve. its not returned the correct results though? the results are in fact pretty random?
will the format of my CourseDate effect the format?
Jun 9 2005, 05:10 AM
just to add, for every date, it only offers January or December as the Month?
Jun 9 2005, 05:12 AM
when i try the 'Year' version of the same expression, it always returns "1905"
Jun 9 2005, 05:42 AM
Odd i got the same when i ran it through properly. I think it is something to do with English and American Dates.
Anyway i have created a sample which is attached an appears to work. The only thing is you have to enter the Numeric month not the text.
I should have cheked before i posted!
Jun 9 2005, 06:12 AM
have tried it and its working well,
many thanks for your efforts!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here