puzzling syntax error in this query

[BrockWade]

I do not have the ability to zip a file this large but here are a few rows with the header

Date	Store	Amount
26/11/2013	1	160.00
05/11/2013	1	160.00
22/11/2013	1	140.00
12/11/2013	1	140.00
29/10/2013	1	100.00
19/11/2013	1	40.00
31/10/2013	3	440.00
07/11/2013	3	331.00
22/11/2013	3	300.00
14/11/2013	3	222.00
26/11/2013	4	1,276.00
05/11/2013	4	1,243.00
01/11/2013	13	400.00
21/11/2013	13	241.00
19/11/2013	13	230.00
05/11/2013	13	202.00

[June7]

I copy/pasted your data into an Excel sheet. Then imported (Date to text field) into table. The d/m/yyyy structure was retained except the leading zeros were dropped.

I formatted the Excel Date column as text and all the entries that were a valid mm/dd/yyyy date display as numbers (5/11/2013 became 41405). I retyped those data in Excel as text with leading zeros. Redid the import. This time leading zeros were retained and dates in dd/mm/yyyy order.

Query:

SELECT [Date], Store, Amount, Mid([Date],4,2) & "/" & Left([Date],2) & "/" & Right([Date],2) AS DebitDate FROM Table1;

Date	Store	Amount	DebitDate
26/11/2013	1	160	11/26/13
05/11/2013	1	160	11/05/13
22/11/2013	1	140	11/22/13
12/11/2013	1	140	11/12/13
29/10/2013	1	100	10/29/13
19/11/2013	1	40	11/19/13
31/10/2013	3	440	10/31/13
07/11/2013	3	331	11/07/13
22/11/2013	3	300	11/22/13
14/11/2013	3	222	11/14/13
26/11/2013	4	1276	11/26/13
05/11/2013	4	1243	11/05/13
01/11/2013	13	400	11/01/13
21/11/2013	13	241	11/21/13
19/11/2013	13	230	11/19/13
05/11/2013	13	202	11/05/13