CDate function in query

[mikeymay]

I am using CDate to convert a date text string into a date value, which is then listed in ascending order in the result.

If I leave the default format as 'dd/mm/yy' the data is in the correct date ascending order when the query runs but if I use -

Format(CDate(etc, etc),"dd mmm yy")

The query is in alpha/numeric order as if the value is a text string and not a date value.

Not entirely sure why it is doing this when all that I have changed/added is the 'Format' function.

 

[gemma-the-husky]

because if in a query yoiu use format whatever, you ARE changing the date into a string.

if you leave it in its native format (ie numeric), access will be able to sort it perfectly.

leave it as a date, and just format it as required for presentation purposes

 

[mikeymay]

Thought that may have been the case, but by using the CDate function I thought Access would have interpretted the result as a date.......

 

[gemma-the-husky]

sorry, misread your post

if you have a text string to START OFF WITH, then

cdate("the date string") {only - no format}

will change it to an access date - but don't format it at that point.

it would be better to try to get this data fixed in your table, to actually store it as a date, rather than as a string

 

[AC5FF]

MikeyMay
I think i had the same problem at one point. I was using =Format(CDate...) in my query and Access wouldn't sort in date order, but instead it sorted alpha/numerically. I needed a long date format stored in a table to be in MMM YY format so Access could group my data together .. That's where I took the wrong turn with the =Format

All I needed was CDate(....,MMM YY) and everything started working how I was expecting it to.

Hope this helps..

 

[mikeymay]

Thanks everyone for your input.

it is the little things like this in the world that drive us/me potty so is much appreciated