Sorting Date imported as TEXT field in different formats

[Danick]

The main table in my database is imported from an excel table. All fields are TEXT fields.

My continuous form has a column called RevDate and I'd like to have a button to sort the form by this field. Most of the time, the date is in a format yyyy-mm-dd. So using this simple code requires no conversion and works very well.

Me.OrderBy = "[RevDate] DESC"

However, sometimes, the format is mm-dd-yyyy.

Is there any way to use VBA to sort this text field in either format?

 

[arnelgp]

maybe try:

Me.OrderBy = "CDate([RevDate]) DESC"
Me.OrderByOn = True

 

[plog]

The main table in my database is imported from an excel table. All fields are TEXT fields.

Address the problem, not the symptoms. You need your 'main' table to be properly structured and store your data correctly.

If you want to use dates, then you need to store your data in your table as dates. In your import process you need to convert those text dates (no matter their format) into actual dates and store those in the main table. When you do that this downstream issue (as well as any others) disappears.

 

[Pat Hartman]

Is there any way to use VBA to sort this text field in either format?

No. Like plog said, you need to make the format consistent and preferably an actual datetime. Also, you can't sort a date logically when it is a string formatted as mm/dd/yyyy. You get all the Januaries together for all years.

 

[Danick]

Address the problem, not the symptoms. You need your 'main' table to be properly structured and store your data correctly.

If you want to use dates, then you need to store your data in your table as dates. In your import process you need to convert those text dates (no matter their format) into actual dates and store those in the main table. When you do that this downstream issue (as well as any others) disappears.

I thought about trying to do that. But I don't control the external data which are all text fields. If I use the import process to convert those text dates, I was concerned that Access would mess up the conversion as the formats are different as mentioned above. So I just kept them the same way and figured I'd try to fix them with VBA in the form.

 

[Danick]

maybe try:

Me.OrderBy = "CDate([RevDate]) DESC"
Me.OrderByOn = True

Forgot to mention that some fields are empty. So this gives me a run-time error 94 - Invalid use of Null

 

[cheekybuddha]

Add a new field to your table, eg 'RevDateDt' - datetime datatype.

Then you can run a query like:

UPDATE YourTableName
SET RevDateDt = CDate(RevDate)
WHERE RevDateDt IS NOT NULL
  AND RevDate IS NOT NULL
;

Then use RevDateDt in you database where you previously used RevDate (ie queries or ControlSource's or as a sort field for your form etc)

Re-run the above query after each new import.

However, probably better to import to a staging table, and fill your proper tables with cleaned up data from that .

 

[Danick]

Add a new field to your table, eg 'RevDateDt' - datetime datatype.

Then you can run a query like:

UPDATE YourTableName
SET RevDateDt = CDate(RevDate)
WHERE RevDateDt IS NOT NULL
  AND RevDate IS NOT NULL
;

Then use RevDateDt in you database where you previously used RevDate (ie queries or ControlSource's or as a sort field for your form etc)

Re-run the above query after each new import.

However, probably better to import to a staging table, and fill your proper tables with cleaned up data from that .

I like the staging table method but can't get it Update correctly. It only updates the records where the RevDate field is not null. So the staging table ends up with a lot fewer records. How can I get it copy all records and just bypass trying to CDate the null fields. Here's what I got so far.

UPDATE tblTechPubs LEFT JOIN tblStaging ON tblTechPubs.ID = tblStaging.ID SET tblStaging.ID = ([tblTechPubs].[ID]), tblStaging.RevDateDt = CDate(tblTechPubs.RevDate)
WHERE (((tblTechPubs.RevDate) Is Not Null));

 

[Danick]

I like the staging table method but can't get it Update correctly. It only updates the records where the RevDate field is not null. So the staging table ends up with a lot fewer records. How can I get it copy all records and just bypass trying to CDate the null fields. Here's what I got so far.

UPDATE tblTechPubs LEFT JOIN tblStaging ON tblTechPubs.ID = tblStaging.ID SET tblStaging.ID = ([tblTechPubs].[ID]), tblStaging.RevDateDt = CDate(tblTechPubs.RevDate)
WHERE (((tblTechPubs.RevDate) Is Not Null));

I removed the Where clause which seems to work, but keep getting the Access Violation type conversion error. Suppose I can just suppress the warnings. Thanks for your help.

 

[arnelgp]

Forgot to mention that some fields are empty.

then use Nz()

Me.OrderBy = "CDate(Nz([RevDate],1)) DESC"