Help with date stored as string

[weisslakeguy]

Hello Everyone!

I apologize in advance if this question has been answered before, but I would appreciate some help.

I have a field called TRANSDATE that stores dates as text in the format YYYYMMDD.

I need to perform calculations using this field, so in a query, I wrote:
Checkdate: DateValue(Mid([transdate],5,2) & "/" & Right([transdate],2) & "/" & Left([transdate],4))

I then wrote Criteria: Between [Enter Check Date] And [Enter Today's Date]



The query is returning data, but it is not what I would expect. If I ask for the query to return data between 05/17/2010 and 05/25/2010, it returns information apparently from all dates stored in the system - not just the range I specified in the criteria.

How can I solve this?

Thank You!

[evander]

The Checkdate query field and Between criteria works in my simulation. Dates are filtered as I expect them to be. Where did you put the Between criteria? This should be placed in the Checkdate query field.

[weisslakeguy]

The "Between...And..." criteria is set in the criteria field for Checkdate. I just can't get it to work.

I tweaked the formula a little (attached) and now I get more odd results (also attached).

Using the Criteria: Between [Enter Check Date] and [Enter Today's Date], I used 5/17/2010 for the Check Date and 5/25/2010 for Today's Date.

As you can see from the results image, it pulled the correct month and day range, but it also pulled other years (i.e. 2008, 2009 - as well as 2010).

I made a workaround by basing a separate query on these query results. The second query seems to be pulling the correct information. There must be some other factor I'm missing in the original query.

[evander]

Hold on, I'll make another simulation, including multiple years.

[evander]

Yes, you're right. I get 2009 and 2008 even though my criteria was for 2010 only. It's my first time to encounter this... I'll give you the solution later. For the meantime, try separating the months, days, and years into three (3) separate query fields, and put a criterion in each. Also, could you try storing the date as a true date value? If you need to encode date in the YYYYMMDD format, you can use the input mask feature of Access.

Good luck.

[ConneXionLost]

Just a shot in the dark.

This may be a date format issue within your Operating System.

If you convert a Date value to the String type (which is what you may be doing by using the BETWEEN - AND as criteria), the date is rendered according to the short date format recognized by your computer.

In other words, when you enter dates using:

Between [Enter Check Date] and [Enter Today's Date]
as
5/17/2010 for the Check Date and 5/25/2010 for Today's Date;

it's possible your operating system is misinterpreting the dates and providing incorrect criteria to the query.

Go to your OS's Control Panel to confirm the stored short date format for the proper way to enter the date information.

(for XP this is found at "Start > Control Panel > Regional and Language Options".)

I tested this with your query and got normal results from data spanning three years. When I entered the date "incorrectly" for the query, some very strange results showed up.

Cheers,

[weisslakeguy]

I believe the short date in the Regional settings is correct. I have attached a screen shot.

Unfortunately, I cannot alter the structure of the table containing "transdate". Our accounting software stores all dates as text in the format YYYYMMDD.

There are different date formulae that have me a little confused. What is the difference in using CDate, DateSerial, and DateValue? Can any of those be used on the transdate field to basically get the same result (i.e., MM/DD/YYYY)? Does any one of those have an advantage over the others?

I will keep experimenting here too. My original way of solving this was a two-step process. I allowed the MS Access query to pull all data for all years (with the date being stored in the query as MM/DD/YYYY format). I then had a separate Excel query (MS Query) that polled the Access query to narrow down the results to the correct time frame. But we have just updated our software - which now uses SQL, and now none of my custom Excel queries will connect. All the Access queries are still working after I pointed the tables to the new SQL database. But as you can see, they are not working exactly as I want them to. I am not experienced in SQL at all, but I've got a feeling I'm about to learn - whether I want to or not.

[evander]

Good idea to migrate to SQL. Perhaps you could use Access project to connect to SQL Server.

Based on my experience (I am not an IT or Computer Science graduate), I find writing SQL scripts in SQL Server much easier. I can recommend a book for you to read, if you want.

I usually use CDate to convert a number to date. But I think you'll be on a much safer side using DateSerial because you can control the way you convert each month, day, and year.

If your two-query solution works, I think you should stick to it till you find a better one. Microsoft Access has issues in complex expressions that may be resolved by creating "intermediate" queries. One problem is complex outer joins.

Good luck in your project.

[weisslakeguy]

FYI - To make everything work, the two-step approach is best for now. I changed the Access query from a Select type to an Append type and created a temporary table to hold the data from the query.

In Excel, I pointed the MSQuery at the temporary table and further isolated the Checkdate field by using 'Is Between ... And ...'

I really could use a good book or reference that explains the relationships between SQL, Access, and Excel. After the switch to a SQL database, my Excel MSQueries no longer connected to the Access queries, but they do not seem to have a problem connecting to Access tables.

Thank you for all your help!