Data Type problem

[Joe Cilinceon]

All of sudden I'm having problems in queries with Expressions that I set a
criteria on. Calculated dates I can't use Date() below in the criteria. Iif
statements that return a 1 or 0 and put say a 1 in the criteria I get the
error. What am I missing here.

 

[John Vinson]

All of sudden I'm having problems in queries with Expressions that I set a
criteria on. Calculated dates I can't use Date() below in the criteria. Iif
statements that return a 1 or 0 and put say a 1 in the criteria I get the
error. What am I missing here.

One thing you're missing is the opportunity for us to see the query,
and perhaps be able to help. Care to post the SQL?

John W. Vinson[MVP]

 

[Joe Cilinceon]

One thing you're missing is the opportunity for us to see the query,
and perhaps be able to help. Care to post the SQL?

John W. Vinson[MVP]

Sure John here it is. I might add that created a table with this query and
the NoticeDate saves as a Date/Time field. I'm also having problems with
Iff(something here = something there, 1, 0) and setting the criteria as say
1.

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()));

 

[John Vinson]

Sure John here it is. I might add that created a table with this query and
the NoticeDate saves as a Date/Time field. I'm also having problems with
Iff(something here = something there, 1, 0) and setting the criteria as say
1.

The function is IIF, not IFF - might that be the problem? Also, be
sure you [bracket] fieldnames if they have blanks - I usually do so
even if they don't.

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()));

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

John W. Vinson[MVP]

 

[Joe Cilinceon]

The function is IIF, not IFF - might that be the problem? Also, be
sure you [bracket] fieldnames if they have blanks - I usually do so
even if they don't.

This was a type in the post not the query sorry about that.

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()));

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
.DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

John W. Vinson[MVP]

I not only tried what you suggested I also made a new db1.mdb and imported
every part, 1 part at a time. Reset all References and recompiled. I still
get the error. As for the brackets this is straight from the query builder
in Access. I'm at a complete loss now as to what can be causing this. It
only happening on expressions. If it is in a table not a problem.