Add leading zero to date field for jan thru sep only

[Ricard via AccessMonster.com]

I have a field named invdate. this field data shows as 1/01/2001. how can i
write a query to add a leading zero to this date field for the months of
january thru september that will display the field as 01/01/2006?

thanks for your help,
Ricard

 

[Rick Brandt]

I have a field named invdate. this field data shows as 1/01/2001. how can i
write a query to add a leading zero to this date field for the months of
january thru september that will display the field as 01/01/2006?

Is this a DateTime type or a Text type field? If it's an actual date filed then
this is just a format for display issue as DateTimes are always stored exactly
the same. Just set the format property on your form and report controls to...

mm/dd/yyyy

If you are storing this in a Text field (bad idea) then you can use an
expression in an update query...

UPDATE TableName
SET FieldName = Format(CDate(FieldName), "mm/dd/yyyy")

 

[Ricard via AccessMonster.com]

Hi Rick, yes, I'm using the DateTime field. I used the mm/dd/yyyy for the
form. But when I export the data to a csv file I lose the leading zero. I'm
using transferText to export the data.

Is there a way to keep the leading zero for the export to .csv file?

Thanks,
Ricard

 

[Rick Brandt]

Hi Rick, yes, I'm using the DateTime field. I used the mm/dd/yyyy
for the form. But when I export the data to a csv file I lose the
leading zero. I'm using transferText to export the data.

Is there a way to keep the leading zero for the export to .csv file?

You have to build a query based on your table and export the query instead
of the table. In the query you can use the Format() function to convert the
DateTime to a string in the desired format.

Format(DateField,"mm/dd/yyyy")