"<>" not working

[Karen]

When trying to use the following statement
If DLookup("[fldcode]", "tblReagents", "[fldcode] <> Forms![frm shelflifedialog box]!Text0")
Access always returns
Runtime Error 13 Type Mismatch.

However when I change the statement to
If DLookup("[fldcode]", "tblReagents", "[fldcode] = Forms![frm shelflifedialog box]!Text0")
using the equals sign rather than <>
IT WORKS!
Why?
(I'm using Access 2002 on a Win 2000 Pro machine)
Thanks for helping clear this up.
Karen

 

[Wayne Morgan]

What are the data types of [fldcode] and Text0? Also, try the following to see if it helps:

If the value is a number
If DLookup("[fldcode]", "tblReagents", "[fldcode] <> " & Forms![frm shelflifedialog box]!Text0)

If the value is text
If DLookup("[fldcode]", "tblReagents", "[fldcode] <> '" & Forms![frm shelflifedialog box]!Text0 & "'")

--
Wayne Morgan
MS Access MVP


When trying to use the following statement
If DLookup("[fldcode]", "tblReagents", "[fldcode] <> Forms![frm shelflifedialog box]!Text0")
Access always returns
Runtime Error 13 Type Mismatch.

However when I change the statement to
If DLookup("[fldcode]", "tblReagents", "[fldcode] = Forms![frm shelflifedialog box]!Text0")
using the equals sign rather than <>
IT WORKS!
Why?
(I'm using Access 2002 on a Win 2000 Pro machine)
Thanks for helping clear this up.
Karen

 

[Karen]

Wayne-

[fldcode] is a Text field. Text0 is an unbound control and the format line of the properties is blank.

I tried the code below with the extra quote marks and still get a type mismatch error.


K-
What are the data types of [fldcode] and Text0? Also, try the following to see if it helps:

If the value is a number
If DLookup("[fldcode]", "tblReagents", "[fldcode] <> " & Forms![frm shelflifedialog box]!Text0)

If the value is text
If DLookup("[fldcode]", "tblReagents", "[fldcode] <> '" & Forms![frm shelflifedialog box]!Text0 & "'")

--
Wayne Morgan
MS Access MVP


When trying to use the following statement
If DLookup("[fldcode]", "tblReagents", "[fldcode] <> Forms![frm shelflifedialog box]!Text0")
Access always returns
Runtime Error 13 Type Mismatch.

However when I change the statement to
If DLookup("[fldcode]", "tblReagents", "[fldcode] = Forms![frm shelflifedialog box]!Text0")
using the equals sign rather than <>
IT WORKS!
Why?
(I'm using Access 2002 on a Win 2000 Pro machine)
Thanks for helping clear this up.
Karen

 

[Dirk Goldgar]

When trying to use the following statement
If DLookup("[fldcode]", "tblReagents", "[fldcode] <> Forms![frm
shelflifedialog box]!Text0")
Access always returns
Runtime Error 13 Type Mismatch.

However when I change the statement to
If DLookup("[fldcode]", "tblReagents", "[fldcode] = Forms![frm
shelflifedialog box]!Text0")
using the equals sign rather than <>
IT WORKS!
Why?
(I'm using Access 2002 on a Win 2000 Pro machine)
Thanks for helping clear this up.
Karen

Would you care to post the whole If statement? Is the difference
because in one case the DLookup expression returns Null and in the other
it doesn't? What would be the value of the [fldcode] that the
expression returns?

 

[Wayne Morgan]

Have you stepped through the code and verified what the value of the textbox is? The form has to be open at the time the code is run. While the field is text, what does it contain (some sample data)?

I just tried DLookup with the <> in the criteria to make sure that is would accept it and it did. The problem is that this isn't the normal use of DLookup. There is a good chance that <> will return more than one record, if so, DLookup will only return the value from the first matching record it finds.

You don't have the full expression here, you have

If DLookup(....) what goes here Then

I'm wondering if the type mismatch is in this part of the equation, not in the DLookup itself.

--
Wayne Morgan
MS Access MVP


Wayne-

[fldcode] is a Text field. Text0 is an unbound control and the format line of the properties is blank.

I tried the code below with the extra quote marks and still get a type mismatch error.