02-24-06, 08:30 #1Registered User
- Join Date
- Jun 2004
Ok - I have a problem which I have narrowed down to the following :
If I use the "weekday(mydate)" function on a dataset held in a table it works fine.
However, if I use it on a dataset which is a query based on a table, it doesn't work.
For example :
- I have 100,000 records in tbl_data
- I have qry_data which pulls all data from tbl_data, and also has a couple of additional fields, one of which is "weekday(mydate). (The main reason for the query is to add a couple of fields and I need certain column headings, and I can't change them in the table)
- I then have a grouped query (qry_group) based on the qry_data, which groups by date, shows the day, and provides the number of records per day etc.
However, the problem comes when I want to only pick up data for weekdays.
In qry_group, the 'mydate' field shows the days wrong - i.e. it shows Today as weekday "5", when it should be "6"
This is even though it shows correctly when I run the main query (qry_data).
To further complicate matters, if I set qry_data as a "make-table" query and run qry_group on the table instead, it works fine !!!
Can anyone offer any advice on this matter please ?
02-24-06, 11:33 #2Cavalier King Charles
- Join Date
- Dec 2002
- Préverenges, Switzerland
have a look at the Weekday() function in VBA-help. you can set the first day of the week definition - my guess is that forcing your preferred definition of first day throughout the application will solve the problem.
...as to WHY your tables behave and your queries don't: dunno
izycurrently using SS 2008R2