Formatting DATE without timestamp

[GriffyNJ]

I am trying to format a date field without having the attached time stamp. In access it doesn't show the time stamp, but when I export a table to a TEXT FILE, the time stamp is there. I've used DATE(), and it attaches a 00:00 at the end. Any advice on how to get the desired result?

Thank you in advance for all your help

 

[Rabbie]

Access stores date/time fields as a real number. The integer part represents the date and thedecimal part represents the time.

If You use a query to extract all the records in the table you could use the format command to format thedate so it appeared without the time part. Then export the query results to your text file.

 

[GriffyNJ]

What does that look like? I have an append query that makes my final table. What do I do to get rid of the time stamp?

 

[namliam]

If You use a query to extract all the records in the table you could use the format command to format thedate so it appeared without the time part. Then export the query results to your text file.

Like the man says, use a format... Look up the format function in the help
read all about it

 

[Rabbie]

What does that look like? I have an append query that makes my final table. What do I do to get rid of the time stamp?

As a matter of curiosity why are you worried about the time stamp especially since you have stripped it out of the date info. The users never need to see it if you use the Format() function correctly

 

[GriffyNJ]

As a matter of curiosity why are you worried about the time stamp especially since you have stripped it out of the date info. The users never need to see it if you use the Format() function correctly

What i'm trying to accomplish a text file that is formatted just perfectly for an import into a SQL server database. The software we are running on this server is fickle to say the least. So my date fields have to be mm/dd/yyyy without the time stamp.

Now when I export the query straight to a text file, the formatting is fine without the time stamp. But i'm exporting this text file from a TABLE populated by my queries. I did as you said and put format([DOB],"mm/dd/yyyy") but when I get over to my table and then do the export, I still have a time stamp attached. Can I throw this format function in the table itself? I have tried an mm/dd/yyyy in the table's FORMAT field, but I still get a time stamp when I export it. What am I doing wrong?

 

[Rabbie]

What i'm trying to accomplish a text file that is formatted just perfectly for an import into a SQL server database. The software we are running on this server is fickle to say the least. So my date fields have to be mm/dd/yyyy without the time stamp.

Now when I export the query straight to a text file, the formatting is fine without the time stamp. But i'm exporting this text file from a TABLE populated by my queries. I did as you said and put format([DOB],"mm/dd/yyyy") but when I get over to my table and then do the export, I still have a time stamp attached. Can I throw this format function in the table itself? I have tried an mm/dd/yyyy in the table's FORMAT field, but I still get a time stamp when I export it. What am I doing wrong?

As I said in my first answer to you use a query with the date formatted as you want it and then export that to your text file. A dataset is a dataset so that should work for you.

 

[namliam]

Where do you put the format() in a query surely? Then export the query not the table.

Also SQL server should be able to work with Date/Time as its dates are date/time as well...

Edit xposted with Rabbie

 

[GriffyNJ]

Where do you put the format() in a query surely? Then export the query not the table.

Also SQL server should be able to work with Date/Time as its dates are date/time as well...

Edit xposted with Rabbie

I put the format in the FIELD position as expr1: format([dob],"mm/dd/yyyy")

the problem is im not the DBA. i'm just a pawn in a governmental chess game. the powers that be want a tab delimited text file. When you export from query you get hash marks and | marks. These people don't want to attempt anything new, they just want it the way they've always done, which prevents me from doing it the way you guys might be suggesting. I'm sorry if i'm making it difficult, and I understand if you really don't want to help. I was just hoping I could export from a table without a time stamp.

 

[namliam]

I put the format in the FIELD position as expr1: format([dob],"mm/dd/yyyy")

the problem is im not the DBA. i'm just a pawn in a governmental chess game. the powers that be want a tab delimited text file. When you export from query you get hash marks and | marks.

You can specify the delimiters during the export in the Advanced options...

If all else fails, write your own export procedure using "Open" to create a file and "recordset" to read the table.
You can then "write" or "print" as required in what ever format you require etc...