|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 17th, 2005, 09:45 PM
|
|||
|
|||
DATE FORMAT in crosstab query
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! 
|
|
#2
February 17th, 2005, 11:27 PM
|
|||
|
|||
|
You may have Reference errors that (quite often) affect the Format function.
See Douglas J. Steele's article: http://members.rogers.com/douglas.j....nceErrors.html -- HTH Van T. Dinh MVP (Access) "Mary" wrote in message ... 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!
|
|
#3
February 18th, 2005, 03:31 PM
|
|||
|
|||
|
How about providing your SQL view?
-- Duane Hookom MS Access MVP -- "Mary" wrote in message ... 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!
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Crosstab query and formatted date | SMac | Running & Setting Up Queries | 1 | January 27th, 2005 05:17 AM |
| Crosstab query has more problems | Robin | Running & Setting Up Queries | 2 | January 20th, 2005 01:23 PM |
| Date range in crosstab query | ew | Running & Setting Up Queries | 6 | October 4th, 2004 03:46 PM |
| How do I set up Date query in a crosstab query? | Doddysgirl | Running & Setting Up Queries | 2 | September 24th, 2004 08:20 PM |
| Aggregating Date Data into Weeks and Quarters | Roger | Running & Setting Up Queries | 3 | July 11th, 2004 05:56 PM |
Linear Mode
