DateSerial and query Problem

[k209310]

I have created a base query which gathers together dates from a query and using dateserial chnages the date format so that all of the dates start wioth the day(1) eg 01/12/2002.

I had planned to use the basequery in another query to count the number of dates per month.


However when i come to use the base query i get problems. i can run the query to return all of the dates and they appear as 01/mm/yyyy (which is correct). However whenever i try to do ANYTHING else i get a "data mismatch in criteria expression message". I mean anything - i cant even sort the data in ascending and descending order, i cant group by or count any of the data or even put any criteria in to the query and if i try a datesearch via access. - the data is there but i cant do anything to it.


I have looked in help and cant find anything and again nothing on the net.
Do you have any ideas why this would be occuring?

 

[Rich]

Why and how are you using DateSerial?

 

[neileg]

I don't understand why you are using dateserial.

Any data that Access recognises as a date is stored as a number. You seem to be attempting to change the date data in a way that replicates changing the format.

 

[k209310]

ok. Im trying to count the number of order per month. As far as i could see i couldnt simply count the months in one query - access seems only to allow me to count one month per query.

Anyway i was given some advice that suggested that i should create a base query and use dateserial to change the dates so that the all read the first of the month.

Then calling this base query in another query would allow me to group and count the months for every month in one sigle query.

Is this bad advice? Am i going about this the wrong way - all i want to do is have a query that counts the orders recived per month for a one year period.

Chris

 

[neileg]

the Month() function will give you a number from 1 to 12 corresponding to the month of the year. So if you run a query that includes , say MyDate, create a calculated field like MthNo:Month([MyDate]) and set your total for this field to count.

Am I missing something?