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 mismatch in criteria expression
I'm trying to use an expression as the criteria in a query field and I can't
understand why I'm getting a data type mismatch. The field is a text field. The expression I'm trying to use is: IIf(DLookUp("Processed","tblFromToInvoiceAandP","F romToID = 1")=-1,Null,Is not Null). I know there's nothing wrong with the DLookUp function, because I've checked it in the VBA Immediate Window. In addition, both Null and Is not Null work just fine if I type either in by themselves. I know there's nothing wrong with the IIf structure, because if I replace Null and Is not Null with specific text values, there is no error and the query recordset returns the records that have the criteria valuse. So why am I getting a "data type mismatch" error when I put the Null/Is not Null values in the IIf statement? Thanks in advance, Paul |
#2
|
|||
|
|||
I tried several different forms for this criteria expression, and the
following expression works in the Immediate Window: ?IIf(DLookUp("Processed","tblFromToInvoiceAandP"," FromToID = 1")=-1,Null,"Not Null") That is, if the DLookUp table field has a value of -1, VBA displays the following: Null If the table field has a value other than one, (0), VBA displays Not Null But it still doesn't work in the query. When I go to datasheet view, there is no error message, but the query doesn't display any records, regardless of whether the DLookUp table field has a value of 0 or -1. Can anyone tell me how I can modify this expression so it will return the desired records? Thanks again in advance. Paul |
#3
|
|||
|
|||
You can see that in your DLookUp, the DLookUp returns Null (of undefined
type) for the True part and "Is Not Null" of String Type. Since there are 2 different types possible in the return, DLookUp() returns Variant. Most of the time, JET/Access type-casts the Variant return of DLookUp to the correct type for you. Unfortunately, not in this case. Since your FieldX is a Text Field and you can't see the difference between Null and an Empty String, my suggestion is to use the length of FieldX instead. Create a Calculated Field (preferably, not displayed) in your Query: FldXNotEmpty: (Len(Trim$([FieldX] & "")) 0) In the criteria of the above Calculated Field, use: IIf(DLookUp("Processed","tblFromToInvoiceAandP","F romToID = 1")=-1, False, True) This way, when Processed = -1 (True), you get all Records whose FieldX value is either Null, Empty String or white spaces. -- HTH Van T. Dinh MVP (Access) "Paul James" wrote in message ... I tried several different forms for this criteria expression, and the following expression works in the Immediate Window: ?IIf(DLookUp("Processed","tblFromToInvoiceAandP"," FromToID = 1")=-1,Null,"Not Null") That is, if the DLookUp table field has a value of -1, VBA displays the following: Null If the table field has a value other than one, (0), VBA displays Not Null But it still doesn't work in the query. When I go to datasheet view, there is no error message, but the query doesn't display any records, regardless of whether the DLookUp table field has a value of 0 or -1. Can anyone tell me how I can modify this expression so it will return the desired records? Thanks again in advance. Paul |
#4
|
|||
|
|||
You're right, Van, it works just fine.
Thanks for the solution, and thanks also for the clear explanation. Paul |
#5
|
|||
|
|||
Dear Paul:
Your IIf statement suggests that the phrase "Is not Null" is a value. It isn't. If the DLookup succeeds, what value do you want to assign? Perhaps you want the value of the lookup assigned. If so: IIf(DLookUp("Processed","tblFromToInvoiceAandP", "FromToID = 1")=-1, Null, DLookUp("Processed","tblFromToInvoiceAandP","FromT oID = 1")) Does this help? Tom Ellison Microsoft Access MVP Ellison Enterprises - Your One Stop IT Experts On Fri, 3 Sep 2004 14:29:29 -0700, "Paul James" wrote: I'm trying to use an expression as the criteria in a query field and I can't understand why I'm getting a data type mismatch. The field is a text field. The expression I'm trying to use is: IIf(DLookUp("Processed","tblFromToInvoiceAandP"," FromToID = 1")=-1,Null,Is not Null). I know there's nothing wrong with the DLookUp function, because I've checked it in the VBA Immediate Window. In addition, both Null and Is not Null work just fine if I type either in by themselves. I know there's nothing wrong with the IIf structure, because if I replace Null and Is not Null with specific text values, there is no error and the query recordset returns the records that have the criteria valuse. So why am I getting a "data type mismatch" error when I put the Null/Is not Null values in the IIf statement? Thanks in advance, Paul |
#6
|
|||
|
|||
Thanks for your reply and suggestion, Tom. Although Van's solution worked
quite well, I finally decided to make some design changes to my form and query which made it possible to obtain the results with a simpler criteria expression that didn't require an IIf clause. All I now have to do is set the criteria expression to the value of a checkbox in the form, while the criteria field is itself a Yes/No field. However, I tried testing the expression you suggested: IIf(DLookUp("Processed","tblFromToInvoiceAandP", "FromToID = 1")=-1, Null, DLookUp("Processed","tblFromToInvoiceAandP","FromT oID = 1")) and it correctly displays records when the IIf condition is false, that is, when the value is 0, in which case the expression returns a value of zero, which produces the desired records. However, when the expression is true, it doesn't return any records. I believe the reason for this is that the field being tested is a Yes/No (checkbox) field, and it can only have values of -1 or zero. So if the criteria has a value of Null, it doesn't return any records. Again, thanks for responding to my original question. Paul |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Required Field Conditional | Katherine R | New Users | 2 | September 1st, 2004 05:52 AM |
How do I print the details view | David | Running & Setting Up Queries | 5 | August 28th, 2004 12:17 AM |
Data Type field | Angel_G | New Users | 1 | July 13th, 2004 11:49 PM |
Mial merge data base problems | Rachael | Mailmerge | 16 | May 21st, 2004 06:22 PM |
Importing from Excel to Access | yolanda | Worksheet Functions | 2 | May 13th, 2004 10:16 AM |