Changing field format in append

[Isolec]

I am using an append query to move data into another database. One of the fields being imported is a date field in text form (20141201). I need it appear in the final database in text form (01/12/14) I have tried using several date conversions and cant get this work. Ideally i need the final value as a text rather than date. Thanks in anticipation

 

[marlan]

Hi,
You can use string manipulation functions in a query field. something like:

right(str,2) & "/" & mid(str,5,2) & "/" & left(str,4)

in a query field.
(str is your date String

 

[Minty]

If it text to start with I would convert it to a date as it is much better for further processing later on. You can convert back to a string much easier for display or export purposes.

As for converting a quick search here gave me this ; http://www.access-programmers.co.uk/forums/showthread.php?t=204287

 

[jdraw]

Ideally i need the final value as a text rather than date.

Please tell us more. Why not use a Date/Time datatype that offers more?

 

[Isolec]

Hi JDraw

Because the text field is used throughout the system in tens of queries and reports and i didn't want to change it all. May be that would be best though!!!!

 

[Isolec]

Hi All

Thanks for all your help so far. I have managed to change the text to date using "CDate(Left([Field1],4)+"/"+Mid([Field1],5,2)+"/"+Right([Field1],2))". However i cannot seem to truncate the year to 2 digits ie. 01/12/14. I thought i could use a mask in the input field but there doesn't seem to be one for xx/xx/xx. Frustrated!!!

 

[marlan]

Hi Isolec,

The date data type has a specific value for each date and time, no matter what format you display it in: it is a numeric floating point number, the value left of the point represents the number of days after Jan 1 100, and the value to it's right is the number of 3 Second intervals since 00:00.
By default, the date is displayed according to your Windows definition. You can use the Format function to display it what ever way you want.

 

[Isolec]

Hi Marlan

That makes a lot of sense - thanks

 

[marlan]

Glad I could help