Access: making me screech since 2002.

May 2, 2006 9:05 AM

Infuriating Microsoft Access questionfilter: I have a database that I inherited in which queries are set up to pull records which have dates of birth between certain dates. It works right in one database but is not working right in another, for a very particular reason..

In this first database the query design view is set up to pull records with the criteria Between #01/01/1900# And #05/02/1990# in the date of birth column and it works great. I tried to duplicate it in the second database in the same column and whenever I move off that line or save it, Access puts in these quotes, so it looks like this: Between "#01/01/1900#" And "#05/02/1990#" What's going on here?

Access 2002, winXP Pro. The date of birth columns are differently named but contain the same data. I have (laboriously) regularised the data so it's all DD/MM/YYYY. There is no validation in either database.

posted by By The Grace of God to computers & internet (7 comments total)

Oh, and when I remove that criterion in database 2, it does pull records.

Many thanks in advance for your assistance or even your thought on the matter if you can't help! I do appreciate it.
posted by By The Grace of God at 9:06 AM on May 2, 2006

Is the column formatted as a date column? I assume so, but just want to make sure.
posted by misanthropicsarah at 9:07 AM on May 2, 2006

Did regularizing the data include going into the table in Access and setting the Data type? It sounds like it's reading the first column as dates and the second one as text.
posted by PinkStainlessTail at 9:07 AM on May 2, 2006

Agreeing with PinkStainlessTail: your second table probably has its date field set up as a string.

This is unrelated to the issue, but please start validating your data. You will thank yourself later on.
posted by boo_radley at 9:33 AM on May 2, 2006

It's a data type issue. Access is reading the field in the first query (the one that works) as a date, and the field in the second query as text.

Two ways to solve this:

Band-aid approach:

Warning: OK for something that's just holding a bunch of data for analysis/reporting that you won't need to keep around forever. Not a long-term solution for a a large, multiuser database that requires strict data integrity in order to keep functioning smoothly. Access is usually used for the former, so I'm guessing this is the case with your db.

Change the text field to a date field on-the-fly, using the CDate function, within the query. Assuming the field is named "Birthdate", you could do the following. In the query design view, go to a blank column and type the following in the Field row (where you'd normally drop or select the field name):

CDate([Birthdate])

Access will change this to something like

Expr1: CDate([Birthdate])

That's ok -- don't worry about the "Expr1" label. Uncheck the "Show" box and place your date criteria (with the # signs) in the Criteria row.

Change the data type in the underlying table:

Warning: if this is a complex database, there may be more than just this query depending on the data. If you change the data type, you could cause other queries/processes to fail.

Find the underlying table that the second query is pulling the data from (you may have to trace it back through several queries if the second query is built on other queries). Open the table in design view, and change the data type of the offending field from Text to Date. If it's already set to date, then you either don't have the correct table, or (less likely) there's an intermediary query which changes the data type to text (The CStr function could do this).
posted by treepour at 9:37 AM on May 2, 2006

Thank you all. That was just the problem. It is all Data Entered by Others so I will be enforcing guidelines on all database formats and creation (including validation) from here on in.
posted by By The Grace of God at 4:52 AM on May 3, 2006

I'm tempted to make "Data Entered by Others" a "root cause of problem" in our bug tracking database. The caps totally make it.
posted by boo_radley at 8:40 AM on May 5, 2006