Partial Dates

[mkb69]

I have a database with fields for dates but formatted in "text" so as to allow for partial input of a date. (ie. 12/00/1990 or 00/00/2001) I now need to extract data prior to an exact date and after an exact date. Due to the text format, I am assuming, I cannot create a query to do this.
I have tried the expression below but of course it does not give me what I need.
< 5/1/1995

I have also tried to create a new field formatted for date/time but I cannot get the date to copy into it since some of my dates are partial.

Can someone please tell me what I need to do so that I can extract the data I need? Any help is appreciated as I have a deadline for this report.

 

[KeithG]

Have you tried the DateValue Function to convert the string to a date?

 

[CraigDolphin]

Since you have what you call 'partial dates' (which are not dates at all as far as access is concerned) and you wish to sort by date (requiring actual dates to compare with) I suspect you need to get creative with the data and replace 'partial' dates with valid dates.

One way to do this might be to use the Replace to function to substitute the value '01' instead of '00'. (Assuming your partial dates always substitute an 00 for missing information)

Another approach might be to use functions like Left, Right, InStr, and InStrRev to cut up your date string and then reassemble it substituting the 00 values.

Be aware that the dates you create might not be accurate and could affect what is selected depending on what values you use to substitute the 00.

 

[mkb69]

i looked at DateValue that but cannot figure out how to write the expression as to convert all of the different dates. Code is not my strong point obviously. Can you walk me through it?

Thanks for the help!

Wow Craig what you said makes sense which is what I suggested they enter in the first place but the argument then was it would not be accurate. Again given the fact that I know very little about code we may have to visually extract this data. Is there a way I can easily query ie. prior to 1990 and after 1996 without having to write a lot of code?

 

[CraigDolphin]

provided that the year is always entered and is at the end of the string you could extract the year as an integer by using something like:

right([fieldnamehere],4)

in your query as the field name. Substitute the name of your stringdate field for 'fieldnamehere', obviously.

This should return the year.

then in the criteria of your query put
< 1990 Or >1996

 

[Brianwarnock]

Will the powers that be allow you to change the table? If not you will need to create a duplicate, then as all of the 00 that you nedd to change will be in the day or month they will be followed by / thus open the table then using Editr - Replace replace 00/ by 01/ either throughout the table or the selected fields, then in a query you can use CDate([fieldname]) to convert these to dates.

Brian

edit If it is only years that you are interested in then Craig has provided the solution as I typed

 

[mkb69]

Thanks Craig! I think that will get me past the deadline.

Brian I will work with your suggestion later this afternoon so as to give a more accurate report. They have told me close is good for what they want to see today. Obviously I will need to fix it so as I dont have this problem next time they want stats quickly.

Again THANKS to you both.

 

[CraigDolphin]

Glad to help.

 

[Brianwarnock]

Hi mkb69, sometimes one has too much time on one's hands. If they will let you add a field to the table then they can continue with the partial text dates and you can create a proper date with 01 replacing the 00 if this helps and is acceptable.

If you are iinterested then
Add field say CreatedDate with date format

Place the following code in a module

Function fchange(textdate)
fchange = Replace(textdate, "00/", "01/", , , 1)
End Function

Create an update query
Field Createddate
Table yourtablename should appear
Update To CDate(fchange([yourfieldnameofTextdate]))

and all of your dates will appear in the new field in the table, you can run this before any date queries.
Best of luck

Brian

 

[Brianwarnock]

I've had second thoughts about this , not just because what i proposed was against the rules of normalisation, but should stats be based on incorrect data?
I think that if the stats are year based then Craig's solution stands, if they need to be at a lower level, say month, then the records not recording the month need to be filtered out and reported separately, easy to do using Like with your text based fields.

Brian

 

[mkb69]

Thanks Brian. I do have the rights to do anything I need to do to get them the data they need. Craig's solution got us close enough to what they wanted to see and I was able to weed out what they didnt want to see after the query was run. I have however thrown your idea at them as an alternative.

Thanks again for everyones help!!! You guys are the BEST!!!!!!