Can't Get DatePart to Work!!!

[LQ]

I am writing a query where I would like to group certain lab tests by month, so I am trying to separate out the individual months from the date field. I have tried to use DatePart but I cannot get it to work...I created a new field with the expression DatePart("m",[Date_of_Service]), but when I run the query, I get an Enter Parameter Value dialog box. I have tried to write the DatePart function several different ways, but I always get that parameter box.

I also tried to create a field to format the date: Format([Date_of_Service],"mm"), but that also gives me a parameter dialog box.

Any suggestions will be appreciated...this seems so simple, but it's driving me crazy!

LQ

 

[Rich]

Add the following to your query
Date By Month: Format$(

.[DateField],"mmmm yyyy")and Year(

.[DateField])*12+DatePart("m",

.[DateField])-1 will give the month and year of your dates order by Year.
HTH

 

[LQ]

Rich, thanks for the suggestion, but I am *still* getting that dang parameter box.

Here is the SQL that I am using for the query (btw, I know that some of the field names do not follow proper naming conventions, but this is not anything I set up myself!)

SELECT Format$([yr2000].[Date_of_Service],"mmmm yyyy") And Year([yr2000].[Date_of_Service])*12+DatePart("m",[yr2000].[Date_of_Service])-1 AS [Date By Month], yr2000.[Ser Dept], yr2000.[Test/Procedure Performed], yr2000.Charge
FROM yr2000
GROUP BY Format$([yr2000].[Date_of_Service],"mmmm yyyy") And Year([yr2000].[Date_of_Service])*12+DatePart("m",[yr2000].[Date_of_Service])-1, yr2000.[Ser Dept], yr2000.[Test/Procedure Performed], yr2000.Charge
HAVING (((yr2000.[Ser Dept]) Like "4050"));

Please help!!!

Thanks!

 

[DJN]

The field Date_of_Service, does it have spaces and not undderscores? if so, try taking out the underscores and just leave the spaces. I tried it that way and it worked. Just a thought.

 

[LQ]

Well, this is weird...the field in the table really *did* have underscores in it. But when I created a new table with a field called Date of Service, I didn't get that parameter box. However, when I ran the query using Rich's suggestion, I ended up getting a field that had all -1s in it. Now I just tried running the query again using my original DatePart function (using the new field name without underscores), and it actually seems to have separated out the month.

I just don't get it--why would underscores have made that much of a difference? And why did I get a field with only negative ones? Oh well, I will use the field without underscores if that is what I have to do.

I will never understand why Access does the things it does!

Thanks for all the help.

LQ