Format date

[prabhu]

Hi there,

I want to Format the date column from YYYYMMDD to DD/MM/YYYY or YYYY/MM/DD.

I used FORMAT([something],YYYY/MM/DD)

But its giving me #error.

When i check the source table date column is with text property; i cant change the source table.

I need to change the date format to find the records which or less then todays date;
<date()

Please help.

 

[boblarson]

You have to use quotes:

FORMAT([something],[B][COLOR=red]"[/COLOR][/B]YYYY/MM/DD[B][COLOR=red]"[/COLOR][/B])

 

[prabhu]

Bob,

I tried, but its still giving me the result as #error.

 

[boblarson]

If it is text you might need to use CDate to convert to a date first:

FORMAT(CDate([something]),"YYYY/MM/DD")

And if you have null or empty string values you might need to further do something. Why aren't they stored as dates but as text?

 

[Dairy Farmer]

There must be a $ after format

format[COLOR="Red"][B][SIZE="6"]$[/SIZE][/B][/COLOR]([Date1],"yyyy/mm/dd")

 

[boblarson]

There must be a $ after format

format[COLOR=red][B][SIZE=6]$[/SIZE][/B][/COLOR]([Date1],"yyyy/mm/dd")

No there doesn't. In fact, that is OLD syntax which is around just for backwards compatibility (answer provided by Microsoft's Access Development Team directly).

 

[Dairy Farmer]

You are right. I have just always used it like that and thought that was how it was done. Never tried it, untill now, without the $.

BTW I tested using dates in both Date/Time and Number formats. Both fields returned correct formating in A2K and A2K7.

Now I just have to remember that I don't need the $. Guess old habits die hard.

 

[gemma-the-husky]

its a common problem isnt it - changing old habits

eg instrrev

didn't have it in A97. Still hard to remember its there now.

 

[Dairy Farmer]

Like finally being able to remove the cardboard cutout from the function keys and the coloured dots off the Shift, Ctrl and Alt buttons, but still using the function keys to do stuff.

For you youngsters out there, Im talking about Word Perfect some 21 years ago.

 

[boblarson]

For you youngsters out there, Im talking about Word Perfect some 21 years ago.

What about WordStar (pre-pc world)?