Ascending Date order

[Darren Smith]

When I ascend the date order in the field it loses lots of rows of data does anyone know why this is?

 

[Micron]

Often a work around is to link to or import the records (consider that to be a staging table) then append to a secondary table where the fields are properly typed. Then anything that looks like a date will be properly stored as such. You are correct in that date values can be an issue with some people's regional settings. To convert as you suggest may require code because conversion functions will usually fail if they encounter nulls.

 

[Joe4]

Often a work around is to link to or import the records (consider that to be a staging table) then append to a secondary table where the fields are properly typed. Then anything that looks like a date will be properly stored as such. You are correct in that date values can be an issue with some people's regional settings. To convert as you suggest may require code because conversion functions will usually fail if they encounter nulls.

Yeah, those conversions functions can be a pain with the regional settings and date values where month/days can go either way.
Personally, if it were me, I would probably choose the last option I suggested, and use that calculated field for sorting. It is fairly simple.

I do use the other option you talk about, using Staging tables too, but I have a bunch of VBA code around that to automatically delete the old data, and compact and repair the database to keep it from bloating. It certianly works well, but is a bit more complex solution.

 

[james_lankford]

depending on how slow it got you might want to make a query that does nothing but selects the data from the linked table, but also includes a calculated column that makes a date out of the text field
then do all your regular work with the query as a source instead of the linked table

I imagine it will be slow, but you never know till you try it out

 

[Darren Smith]

Thanks for your help