Microsoft Access Date Problem

[Esther7101]

Hi everyone. I have been struggling for 2 weeks now. I have Access 97. I have a number of date fields and have them formatted as short date. Whenever I export to text, I get the short date as well as a time. I do not want time which is why I chose the short format. I have reinstalled Access, checked the date format on my system and pulled my hair out. Any and all help and suggestions is greatly appreciated.

 

[Golom]

The format and the content of a date/time are different things. When you export, you export the content of the field, not the format.

To fix the problem, build a query like

Select f1, f2, cDate(Int(DateField)), ...
From TBL ...

and export that.

 

[Esther7101]

Thanks. I do not have a time field and do not collect times at all. The format for the short date does not include times. Any ideas why it is appearing in the export?

 

[Golom]

As I said ... what is stored in a date/time variable is not the same thing as how you format it on output. For example it is now "09/24/2003 14:02:27" in my time zone. But, as a short date, that is just "09/24/03". The rest of the information about the time is still there but it is just not displayed by the format that I have chosen.

When you export the field however, you export its content ("09/24/2003 14:02:27" in the above) and not just some way of formatting it like "09/24/03". The SQL I provided just strips the time portion from the date field. Another option for exporting from a query is:

Select f1, f2, Format(DateField,"mm/dd/yy&quot, ...
From TBL ...

 

[Esther7101]

I know I am a novice and I really appreciate your assistance, but what are you referencing by f1, f2??

 

[Golom]

Just whatever other fields that you want to see in your query to be exported. Remove them or change them to the names of other fields that you want to export.

 

[Esther7101]

Thanks again. I am still having a problem. It states that the code that you gave me to strip the time from the date creates a syntex error......

The exact code, with a field label of "birthdate" should read???????

 

[KenReay]

Hi

Better still in the table definition, on the birthdatecolumn, set the format to "dd/mm/yyyy" or "mm/dd/yyyy" as you wish

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Esther7101]

If I do that, it still exports the time with the date when exporting to text. It does not do it to rtf. I need to be able to strip away the unseen, but present time on export.
Thanks for all of the ideas

 

[Parax]

If you have called the field by the same name and have not specified which table birthdate comes from then it will create a circular reference, ie it looks at itself. Either give the field a different name or add the name of the table that the birthdate comes from.

in query designer use this text:

birthdate:Format([tablename].[birthdate],"dd/mm/yy&quot


In SQL use:

SELECT Format([tablename].[birthdate],"dd/mm/yy&quot AS birthdate
FROM [tablename];