Data Type problem

J

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.
 
J

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.
Click to expand...

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]
 
J

Joe Cilinceon

John said:
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]
Click to expand...

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

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.
Click to expand...

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()));
Click to expand...

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]
 
J

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.
Click to expand...

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()));
Click to expand...
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]
Click to expand...


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.
 
Top