The query below has worked fine under Access 2003. We're about to
convert to Office 2010 so I tested it there, and received "Data type
mismatch in criteria expression".

One might guess that Microsoft have tightened up the rules.
DayTitle.DateKey is a Date/Time field, whereas UserVariables.DataValue
is a Text field.

But maybe it's not so simple. In Office 2010, I created a new
database, typing in the table definitions, values, and query by hand
very carefully. They appear to be the same as the old database. But
now the query works!

Thinking that a database in 2010 format might work where a 2003-format
database would not, I created a blank database and imported the entire
contents of the old database, in effect converting it to 2010 format
(I hope). But in this case the query did not work.

My analytical powers are being challenged...

SELECT *
FROM DayTitle
WHERE (DateKey BETWEEN
(SELECT DataValue FROM UserVariables WHERE Keyword='Reporting
date from')
AND
(SELECT DataValue FROM UserVariables WHERE Keyword='Reporting
date to'))
ORDER BY DateKey;

I won't bore you with further complications. The solution was to
force text fields into date fields:

SELECT *
FROM DayTitle
WHERE (DateKey BETWEEN
* * *(SELECT DateValue(DataValue) FROM UserVariables WHERE
Keyword='Reporting date from')
* * *AND
* * *(SELECT DateValue(DataValue) FROM UserVariables WHERE
Keyword='Reporting date to'))
ORDER BY DateKey;

It doesn't make much sense to keep dates in text fields when there is a
Date/Time field and also a Date/Time variable, and a plethora of built-in
functions for calculating date/time values (which will not always work, or
not work well, as you see, when you try to use text for dates).
--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"Composer" <composer (AT) uwclub (DOT) net> wrote

I won't bore you with further complications. The solution was to
force text fields into date fields:

SELECT *
FROM DayTitle
WHERE (DateKey BETWEEN
(SELECT DateValue(DataValue) FROM UserVariables WHERE
Keyword='Reporting date from')
AND
(SELECT DateValue(DataValue) FROM UserVariables WHERE
Keyword='Reporting date to'))
ORDER BY DateKey;

"Composer" wrote in message
news:85acdb6f-1734-4e55-b9a2-4b775f488ac9 (AT) e35g2000yqc (DOT) googlegroups.com...


This usually suggests that on the new machine or different computer that
regional date defaults or formats are different.

I tend to find that "most" of the problems in regards to date issues tend to
be that of date settings on the computer,
especially when letting access "cast" the string into a date for you.

In those cases (if possible), when working with dates, you want to force the
issue so to speak,
and use real date columns and real date expressions.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com