jonnymenthol
Registered User.
- Local time
- Today, 21:55
- Joined
- Oct 31, 2001
- Messages
- 58
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 ?
Thanks.
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 ?
Thanks.
