Is This Correct

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I'm using this. It seems to work but it looks suspicious. Is this the
correct syntax?
Thanks
DS

Me.TxtValid = Nz(DLookup("[EmployeeID]", "[tblEmployees]", "EmployeeID=" &
Me.TxtServID & "And EmpActive=" & -1), 0)
 
DS said:
I'm using this. It seems to work but it looks suspicious. Is this the
correct syntax?
Thanks
DS

Me.TxtValid = Nz(DLookup("[EmployeeID]", "[tblEmployees]", "EmployeeID=" &
Me.TxtServID & "And EmpActive=" & -1), 0)
Click to expand...


Almost, but not prefectly. You need a space before the "And EmpActive", and
you may as well embed the literal -1 directly in the criteria string. Try:

Me.TxtValid = _
Nz(DLookup("[EmployeeID]", "[tblEmployees]", _
"EmployeeID=" & Me.TxtServID & _
" And EmpActive=-1"), 0)

If the original statement works, it must be that the query processor can
figure out how to parse and expression like

EmployeeID=1234And EmpActive=-1

If it can, bravo, but I wouldn't count on it in principle; I'd put the
space in before "And".
 
Thanks Dirk. That works fine. Just one question. Sometimes when I build
the D statements and I have the word and in the statement the word is in
blue. Is this correct or doesn't this matter. Just a little confused on
this matter.
Thanks
DS
 
DS said:
Thanks Dirk. That works fine. Just one question. Sometimes when I build
the D statements and I have the word and in the statement the word is in
blue. Is this correct or doesn't this matter. Just a little confused on
this matter.
Click to expand...


If you intention is to have the word "And" as part of the criteria argument
for the call to the function, then if it is displayed in blue your statement
is probably wrong. The VB editor will only color the word blue if is
interpreting the word as logical operator, and it shouldn't do that if the
word is part of the criteria string. As far as the VB editor is concerned,
the criteria string is just something to be passed to the function, and not
something for VB to interpret. So if it's trying to interpret it, it's not
seeing it as just a string, and there's something wrong with the way you
built the string.

Consider some examples:

If a = 1 And b = 2 Then

In the above example, the word "And" should be colored blue (in the VB
editor), because it's being interpreted as a logical operator.

X = DLookup("MyField", "MyTable", "A=1 And B=2")

In the above example, the word "And" should be *not* be colored blue (in the
VB editor), because it's just part of the string argument, "A=1 And B=2",
being passed to the DLookup function. *DLookup* will interpret it, but VB
doesn't care what it is.

X = DLookup("MyField", "MyTable", "A=1" And "B=2")

In the above (incorrect) example, the word "And" will be colored blue (in
the VB editor), because it's not part of either of the strings "A=1", "B=2".
VB will interpret this as an instruction to apply the And operator to those
two strings and pass the result to the function. This is almost certainly
not what you would want.
 
Thanks a million! This cleared up something that wasn't totally clear to me
from the begining.
Very useful information.
Thanks
DS
 
Back
Top