Query to edit date format

[Summer123]

Hello,
does anyone know how to edit for date format? For example, when i pull in a text file, one of the field is date and it would be in yyyymmdd, however sometimes if the user doest create the text file correctly then the date would be entered in as mmddyyyy or some other incorrect date format. So what i want to do is, once the file is pulled into a table i want to create a query to catch if the format is NOT in yyyymmdd. Is there a way to do this? maybe a function or something? please assist.

thank you,
Summer

 

[spikepl]

"or some other incorrect date format" meaning ddmmyyyy? Unless you in advance know about the extent of possible ranges of dates, then there is little in the date text to say what is "correct" and what isn't. Do you?

Are there separators in the text? Like mm-dd-yyyy or mm/dd/yyyy, at least to tell what is a year and what isn't?

I 'd focus my efforts at the user-end: preventing creation of erroneous data.

 

[Summer123]

ok yes so it can be in mmddyyyy or ddmmyyyy only but it needs to be in yyyymmdd. No there are no seperators

"or some other incorrect date format" meaning ddmmyyyy? Unless you in advance know about the extent of possible ranges of dates, then there is little in the date text to say what is "correct" and what isn't. Do you?

Are there separators in the text? Like mm-dd-yyyy or mm/dd/yyyy, at least to tell what is a year and what isn't?

I 'd focus my efforts at the user-end: preventing creation of erroneous data.

 

[spikepl]

You did not reply to my question whether you know what the expected range of dates in any given file is.

Otherwise, I do not think there is anyway to guess whether a value is "correct". SOme can probably be discarded, but you will wind up with ambiguous dates, about which only the person that created them can decide eg, what is 20102011 or 05042011?

 

[Summer123]

No there is no expected range it could be any date, basically its a file with birthdate of many, and it needs to be in yyyymmdd format. its not a form where i can control the user, its a file that i import and do some basic error checking and other things and this would be one of the checks i need to do in order to have a correct format of a file... does that make sense?

 

[spikepl]

Noe it doesn't. The date itself, if contained in a text field as outlined, does not contain enough information to disambiguate all dates. You cannot rely on Access to do that.

See section 1. Misinterpretation in the User Interface

in


http://allenbrowne.com/ser-36.html for examples of some weird effects.

But yyyymmdd is not very usual in many countries - a better safeguard would be to use a format the users are familiar with, or a format like 10MAY2011 which is not ambiguous, and when you get a deviation you can flag it.

 

[Summer123]

so if the date is in text field then there is no way to distinguish if is in the correct format?

 

[JohnLee]

Hi Summer123,

The problem that exist is that if the person(s) that have supplied the data to you in the first instance are not consistently using a date format that you can work with, you are going to struggle to get the correct date out of that field.

As Spikepl said in the above posts, if the end user [person(s)] who supplied you the data in first instance are allowed to supply this information in any which way they like, you will never be able to extract accurate data.

So the problem lies with the supplier of the data, get them to supply the data in an acceptable format that you can work with and you'll be home free.

for instance 20102011 could be 20 Nov 2010 or 20 Oct 2011 which is it
10202011 could only be 20 Oct 2011. If it was written as 102011 then what could it be 20 Oct 11 or 20 Nov 10. If a human can't decide which it should be how would you expect a program to do so.

You need some basic rules on how the data is to be submitted so that you can work with the data to get the desired results.

John

 

[Summer123]

thank you... i see both your points and its something i cant control so i guess i'll have to have the client do a check on their end...

 

[JohnLee]

Hi Summer123,

It would be a good idea to get some ground rules set up with your client, draw their attention to the benefits of meeting those rules which could be speed of processing, accuracy and quality of the data and the biggie, financial.

Good luck

John

 

[Summer123]

thank you John!

 

[Galaxiom]

If the data entry is done in Access you could consider using three separate text boxes for the day, month and year.

Might annoy the hell out of a competent data entry operator but at least it is unambiguous. It wouldn't be too bad provided the tab sequence was set properly so they could progress though the three fields with the tab key.

Another alternative is to automatically trigger a move to the next control when valid data is completed in each box.

Assuming you start with the year textbox. The OnChange event procedure decides the century after receiving two characters by comparing it with a cutoff year. (If the range of dates is wider than a century then trigger it at four digits.) Either way the focus is moved automatically to the Month textbox.

In the month textbox the OnChange event accepts any digit from 2 to 9 and moves on to the Day since that is sufficient to define the month. If a 0 or 1 is entered it waits for the second character.

Similarly, any digit from 4 to 9 completes the data while 0 to 3 waits for another digit.

The bound DOB textbox receives the concatenated values form the other textboxes when all three are completed or in the BeforeUpdate event of the form.

Format(Me.txtYear,"0000") & Format(Me.txtMonth,"00") & Format(Me.txtDay,"00")

 

[Summer123]

Thank you Galaxyomathome. But it's not in access.