Covert text date to date format

[TinaK]

I have a table that is imported from a text file. The date from the text file is stored as "short text" in my access table. (2018012903:3913010100110201). I want to convert it to 01/29/2018. I have tried several different ways and nothing has worked. This is the closest I was able to get:
Expr1: Right([field3],2) & "/" & Mid([field3],7,2) & "/" & Left([field3],4)
Results
test1

Field3	Expr1
2018012903:3913010100110201	01/29/2018
2018012807:2112770110110402	02/28/2018
2018012807:3512780120110402	02/28/2018

As you can see, the first one is correct, the others are not.

After I convert it to a date, I want to use "between #xx/xx/xx# and #xx/xx/xx#.

 

[ranman256]

mid([field3],5,2) & "/" & Mid([field3],7,2) & "/" & Left([field3],4)

 

[ranman256]

in query1, convert the dates.
then in Query2, use Q1 to do your date range.

 

[TinaK]

Thank you ranman. The first one works great. i created a second query and the results are blank. I used the expression field and used between #01/01/2019# and #11/30/2019# and nothing. I also only used 19 instead of 2019 and nothing.

 

[JonXL]

Combining ranman256's into the following query returned the correct results for me:

select * from [dates]
where #1/28/2018# between cdate(mid([field3],5,2) & "/" & Mid([field3],7,2) & "/" & Left([field3],4)) and cdate(expr1)

ABE: The query you say you are trying to run should naturally return no results since all your dates in the sample data are in 2018.

 

[TinaK]

JonXL the above was only an example. I have dates from 2018 & 2019.

 

[JonXL]

JonXL the above was only an example. I have dates from 2018 & 2019.

So did you get this to work?

 

[TinaK]

JohnXL, no I didn't.

 

[TinaK]

JohnXL, I did find a way for it to work. I had to use "make a table query", format the field in that table to be able to pull out certain dates.

Thank you all so much for your help.

 

[JonXL]

For a single use that might be fine, but if doing this on a regular basis, I think your DB is going to get very bloated.

And honestly, I don't see why you would need to create a new table for this. If you nest ranman256's formula in a CDate() function, it should return exactly what you're looking for just in one query.