DATE FORMAT in crosstab query

[Guest]

Access table with fields: Customer, Cust PN, Ship Date, Qty, Price

I imported an excel file into this access table. The date field in access
is set at short date. In the excel file, the date format is "3/14/2001"

The table imports fine, no errors. However, when I do a crosstab query
(using the wizard), and asking to group the dates by month, or not group at
all, or group by year, I get this error message:

compile error. in query expression 'Format([SHIP DATE], mm/dd/yy")'

I have changed the date format in both the excel and the access to every
possible combination and cannot get this query to work. The end of the error
message changes depending on how I format the date field in access. I did
not specify a date format for my query, other than to group the dates by
month, year, whatever.

Help!

 

[Van T. Dinh]

You may have Reference errors that (quite often) affect the Format function.

See Douglas J. Steele's article:

http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

 

[Duane Hookom]

How about providing your SQL view?