If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Data Type problem
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. -- Joe Cilinceon |
#2
|
|||
|
|||
Data Type problem
On Thu, 1 Dec 2005 14:38:15 -0500, "Joe Cilinceon"
wrote: 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] |
#3
|
|||
|
|||
Data Type problem
John Vinson wrote:
On Thu, 1 Dec 2005 14:38:15 -0500, "Joe Cilinceon" wrote: 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] 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())); -- Joe Cilinceon |
#4
|
|||
|
|||
Data Type problem
On Thu, 1 Dec 2005 17:43:46 -0500, "Joe Cilinceon"
wrote: John Vinson wrote: On Thu, 1 Dec 2005 14:38:15 -0500, "Joe Cilinceon" wrote: 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] 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] |
#5
|
|||
|
|||
Data Type problem
John Vinson wrote:
On Thu, 1 Dec 2005 17:43:46 -0500, "Joe Cilinceon" wrote: 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. -- Joe Cilinceon |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help PLEASE! Not sure what answer is: Match? Index? Other? | baz | Worksheet Functions | 7 | September 3rd, 2005 03:47 PM |
Unable to have multiple queries feeding a single report | PZ Straube | Setting Up & Running Reports | 15 | June 15th, 2005 08:16 AM |
multiple docs, one data source | kp | Mailmerge | 12 | January 31st, 2005 04:41 PM |
join problem | Jonas | Running & Setting Up Queries | 8 | December 23rd, 2004 07:35 AM |
Importing Excel spreadsheet into Access - data type problem | Andrew Good | General Discussion | 2 | August 27th, 2004 09:39 AM |