Problem with a query

[Joe Cilinceon]

Here is the query:

SELECT LEASES.LedgerID, LEASES.PaidThru,
DateSerial(Year([PaidThru]),Month([PaidThru])-1,1) AS NoticeDate
FROM LEASES
WHERE (((DateSerial(Year([PaidThru]),Month([PaidThru])-1,1))>=Date()));

Now the Leases.paidthru date is a date NoticeDate: is an expressions that is
basically giving me the first day of the previous month. I'm all of a sudden
having problems with expressions in queries using criteria. Am I missing
something as they have always worked before. OfficeXP pro Developers.
Windows XP Media Center and all the latest upgrades on both. This is also
DAO 3.6 2002 formatted tables.

 

[Jeff Boyce]

Joe

Is this a SQL-only statement, or have you done this in the query designer,
then switched to the SQL view?

I'm not clear what/which "criterion" you are referring to.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Joe Cilinceon]

Joe

Is this a SQL-only statement, or have you done this in the query
designer, then switched to the SQL view?

I'm not clear what/which "criterion" you are referring to.

Regards

Jeff Boyce
<Office/Access MVP>

Here is the query:

SELECT LEASES.LedgerID, LEASES.PaidThru,
DateSerial(Year([PaidThru]),Month([PaidThru])-1,1) AS NoticeDate
FROM LEASES
WHERE
(((DateSerial(Year([PaidThru]),Month([PaidThru])-1,1))>=Date())); Now the
Leases.paidthru date is a date NoticeDate: is an expressions
that is basically giving me the first day of the previous month. I'm
all of a sudden having problems with expressions in queries using
criteria. Am I missing something as they have always worked before.
OfficeXP pro Developers. Windows XP Media Center and all the latest
upgrades on both. This is also DAO 3.6 2002 formatted tables.

Hi Jeff and thanks for responding. This is a copy of the SQL statement from
the Query designer. This query was built to test the problem. It seems to be
happening on other expressions with a criteria in the criteria fields. This
just started today and I have done the following trying to fix it as I was
told it could be a reference problem.

Reset all References by unchecking and rechecking.
Build a new db1.mdb file and first link to the backend then import all the
other stuff.
Build a new db1.mdb and import every thing.

This is not only happening with date field expression but any expression I
try to use criteria on. Another example is, I wrote a query in designer
where iif( A fields value = content of field, 1, 0). It works fine and shows
the 0 and 1. Put a 1 in the criteria to filter it for those that test true
and I get Data Type error same as above with the date expression.. I'm
beginning to wonder if it has anything to do with the last batch of updates
this morning from MS for my OS (Microsoft Media Center). I'm now at a
complete loss because other queries in this app have the same type of
expression and are working fine. Even on the same data.

 

[Jeff Boyce]

"all of a sudden having problems with expressions in queries using criteria"
is still a bit generic...

What kind of problems (what are the symptoms?) (what are the error
messages?) (what kind of criteria -- all the same or any data type?)?

Does it happen only on "old" queries, or can you create a new query and have
the same thing happen? If the latter, have you confirmed that you don't
have any missing references?

Regards

Jeff Boyce
<Office/Access MVP>

Joe

Is this a SQL-only statement, or have you done this in the query
designer, then switched to the SQL view?

I'm not clear what/which "criterion" you are referring to.

Regards

Jeff Boyce
<Office/Access MVP>

Here is the query:

SELECT LEASES.LedgerID, LEASES.PaidThru,
DateSerial(Year([PaidThru]),Month([PaidThru])-1,1) AS NoticeDate
FROM LEASES
WHERE
(((DateSerial(Year([PaidThru]),Month([PaidThru])-1,1))>=Date())); Now
the Leases.paidthru date is a date NoticeDate: is an expressions
that is basically giving me the first day of the previous month. I'm
all of a sudden having problems with expressions in queries using
criteria. Am I missing something as they have always worked before.
OfficeXP pro Developers. Windows XP Media Center and all the latest
upgrades on both. This is also DAO 3.6 2002 formatted tables.

Hi Jeff and thanks for responding. This is a copy of the SQL statement
from the Query designer. This query was built to test the problem. It
seems to be happening on other expressions with a criteria in the criteria
fields. This just started today and I have done the following trying to
fix it as I was told it could be a reference problem.

Reset all References by unchecking and rechecking.
Build a new db1.mdb file and first link to the backend then import all the
other stuff.
Build a new db1.mdb and import every thing.

This is not only happening with date field expression but any expression I
try to use criteria on. Another example is, I wrote a query in designer
where iif( A fields value = content of field, 1, 0). It works fine and
shows the 0 and 1. Put a 1 in the criteria to filter it for those that
test true and I get Data Type error same as above with the date
expression.. I'm beginning to wonder if it has anything to do with the
last batch of updates this morning from MS for my OS (Microsoft Media
Center). I'm now at a complete loss because other queries in this app have
the same type of expression and are working fine. Even on the same data.

 

[Joe Cilinceon]

Well Jeff, if you had followed the thread last night, every expression I
created in a query designer that had a criteria added gave me a data type
error. This showed up in old queries that had worked fine and new ones I
created to test to see what the problem was. I got exactly 2 responses (more
than usual for my questions) with suggestions none of which worked. When I
did a repair of my Office install that fixed it why I have no idea other
than perhaps a bad dll. The sample I posted is as follows:

SELECT LEASES.LedgerID, LEASES.PaidThru,
DateSerial(Year([PaidThru]),Month([PaidThru])-1,1) AS NoticeDate
FROM LEASES WHERE
(((DateSerial(Year([PaidThru]),Month([PaidThru])-1,1))>=Date()));

It is now fixed and working fine again as it should. Thanks any how Jeff.