Newbie - help with syntax (comma) error

[thebaey]

I am trying to kill the time stamp that Access attaches to the date when you try to export to a text file. I am receiving a syntax error in the following statement and I can't figure out what's wrong....help?

Date: Format([BALLOT_RECEIVE_DATE],"mm/dd/yyyy")

Thanks!!!

 

[nIGHTmAYOR]

oh
use Date: Format([BALLOT_RECEIVE_DATE];"mm/dd/yyyy")

 

[neileg]

Nope it's not the comma, unless you have a non English Windows setup. It's because Date is a reserved word since it is the name of a function. Change this to MyDate or something similar.

 

[thebaey]

Thanks for the replies! Neither of these solutions works for me. I am still receiving the error.

 

[MSAccessRookie]

Thanks for the replies! Neither of these solutions works for me. I am still receiving the error.

I spent a few minutes using a test database of mine as an example. The database has in it a Table called Table6 that has several columns, including a column called SaleDate that is of type Date/Time. Following is code that tests the Format function, and a result set showing the way each format works.

Notice that I also tested whether it would format a Date that was stored with Text properties as well. Perhaps this will help you to figure out why your code will not work.

QUERY:
 
SELECT 
    Table6.SaleDate, 
    Format([SaleDate],"mm/dd/yyyy") AS Format1,
    Format([SaleDate],"yyyy/mm/dd") AS Format2,
    Format([SaleDate],"dd/mm/yyyy") AS Format3,
    Format(cstr([SaleDate]),"dd/mm/yyyy") AS Format4
FROM Table6;
 
FIELD VALUES FROM DESIGN VIEW:
 
Format1: Format([SaleDate],"mm/dd/yyyy")
Format2: Format([SaleDate],"yyyy/mm/dd")
Format3: Format([SaleDate],"dd/mm/yyyy")
Format4: Format([SaleDate],"dd/mm/yyyy")
 
RESULTS:
 
[FONT=Arial][COLOR=#000000][SIZE=3][FONT=Times New Roman]SaleDate          Format1           Format2           Format3           Format4[/FONT][/SIZE][/COLOR]
[COLOR=#000000][SIZE=3][FONT=Times New Roman]1/1/1950          01/01/1950      1950/01/01      01/01/1950      01/01/1950[/FONT][/SIZE][/COLOR]
[COLOR=#000000][SIZE=3][FONT=Times New Roman]5/8/1971          05/08/1971      1971/05/08      08/05/1971      08/05/1971[/FONT][/SIZE][/COLOR]
[COLOR=#000000][SIZE=3][FONT=Times New Roman]6/14/1973        06/14/1973      1973/06/14      14/06/1973      14/06/1973[/FONT][/SIZE][/COLOR]
[COLOR=#000000][SIZE=3][FONT=Times New Roman]3/9/1979          03/09/1979      1979/03/09      09/03/1979      09/03/1979[/FONT][/SIZE][/COLOR]
[/FONT]

Have you verified that your [BALLOT_RECEIVE_DATE] is either Date/Time format or Text format?

 

[Kiwiman]

Howzit

I got round this by treating the date field as a text field in the specification I used to export to a csv file.

I applied the UK format so it came out in the format I required, with no time stamp.

SELECT 
Format([ActDate],"dd/mm/yyyy") AS Trial, tblActDates.Name
FROM tblActDates;