Dlookup only returning the first record

[Don Barton]

Access 2007. Unbound form with Username and Password controls and cmd
button with VBA behind the OnClick event.
My criteria is being ignored in my DLookup expression.
UName = DLookup("[StudUName]", "tblStudentAccess", "StudPW = '" &
Me.password & "'")
UName is Dimmed as variant (tried String as well)
Me.Password.value criteria is correctly read off the unbound form
password control.
Student Username (StudUName) returned is from the first record -which
is a different password.
It is as if the criteria is being ignored.
The result is UName returns a null, so the error routine kicks in.
I've never seen this before. Is my criteria syntax wrong? password is
a String
Any ideas?

Don

 

[Rick Brandt]

Access 2007. Unbound form with Username and Password controls and cmd
button with VBA behind the OnClick event.
My criteria is being ignored in my DLookup expression.
UName = DLookup("[StudUName]", "tblStudentAccess", "StudPW = '" &
Me.password & "'")
UName is Dimmed as variant (tried String as well)
Me.Password.value criteria is correctly read off the unbound form
password control.
Student Username (StudUName) returned is from the first record -which
is a different password.
It is as if the criteria is being ignored.
The result is UName returns a null, so the error routine kicks in.
I've never seen this before. Is my criteria syntax wrong? password is
a String
Any ideas?

Don

If it is returning the first record then why would UName end up as Null?
That would be the result of DLookup() not finding a record at all which
would suggest that your criteria is being interpreted differently than you
think.

Try it with a hard-coded criteria to see if that works. You don't need the
brackets around your field name so I would try it without those as well.

 

[Don Barton]

If it is returning the first record then why would UName end up as Null?  

That would be the result of DLookup() not finding a record at all which
would suggest that your criteria is being interpreted differently than you
think.

Try it with a hard-coded criteria to see if that works.  You don't needthe
brackets around your field name so I would try it without those as well.-Hide quoted text -

- Show quoted text -

I've worked on this some and have found an interesting situation.

The following code works:

strTest = Forms!frmTest!password
vartest6 = DLookup("LabTestName", "tblLabTests", "LabTestMnemonic = '"
& strTest & "' ")

When the unbound control's contents is read into a variable, and this
is used as a criteria, varTest6 give a correct vaule.

But when I try reading the unbound control directly from the form as
my criteria, I get an error.
"The expression you entered as a query parameter produced this error:
[string in unbound control]"
vartest5 = DLookup("LabTestName", "tblLabTests", "LabTestMnemonic = "
& Forms!frmTest!password)

Shouldn't either of these DLookup scenarios work??

Thanks

 

[Rick Brandt]

If it is returning the first record then why would UName end up as Null?
That would be the result of DLookup() not finding a record at all which
would suggest that your criteria is being interpreted differently than
you think.

Try it with a hard-coded criteria to see if that works. You don't need
the brackets around your field name so I would try it without those as
well.- Hide quoted text -

- Show quoted text -

I've worked on this some and have found an interesting situation.

The following code works:

strTest = Forms!frmTest!password
vartest6 = DLookup("LabTestName", "tblLabTests", "LabTestMnemonic = '"
& strTest & "' ")

When the unbound control's contents is read into a variable, and this
is used as a criteria, varTest6 give a correct vaule.

But when I try reading the unbound control directly from the form as
my criteria, I get an error.
"The expression you entered as a query parameter produced this error:
[string in unbound control]"
vartest5 = DLookup("LabTestName", "tblLabTests", "LabTestMnemonic = "
& Forms!frmTest!password)

Shouldn't either of these DLookup scenarios work??

Your second one doesn't surround the value with quotes. Those are required
if it's a string.

 

[Don Barton]

I've worked on this some and have found an interesting situation.

The following code works:

strTest = Forms!frmTest!password
vartest6 = DLookup("LabTestName", "tblLabTests", "LabTestMnemonic ='"
& strTest & "' ")

When the unbound control's contents is read into a variable, and this
is used as a criteria, varTest6 give a correct vaule.

But when I try reading the unbound control directly from the form as
my criteria, I get an error.
"The expression you entered as a query parameter produced this error:
[string in unbound control]"
vartest5 = DLookup("LabTestName", "tblLabTests", "LabTestMnemonic ="
& Forms!frmTest!password)

Shouldn't either of these DLookup scenarios work??

Your second one doesn't surround the value with quotes.  Those are required
if it's a string.

Thanks Rick, that makes sense. The MS examples showed no quote, but
that must have been because of they were numeric.
Don

 

[Don Barton]

I've worked on this some and have found an interesting situation.

The following code works:

strTest = Forms!frmTest!password
vartest6 = DLookup("LabTestName", "tblLabTests", "LabTestMnemonic ='"
& strTest & "' ")

When the unbound control's contents is read into a variable, and this
is used as a criteria, varTest6 give a correct vaule.

But when I try reading the unbound control directly from the form as
my criteria, I get an error.
"The expression you entered as a query parameter produced this error:
[string in unbound control]"
vartest5 = DLookup("LabTestName", "tblLabTests", "LabTestMnemonic ="
& Forms!frmTest!password)

Shouldn't either of these DLookup scenarios work??

Your second one doesn't surround the value with quotes.  Those are required
if it's a string.

Thanks Rick. That makes sense. The examples I looked at didn't have
any quotes, which means they must have been addressing numeric values.
Moving forward,
Don

 

[John Moris]

Access 2007. Unbound form with Username and Password controls and cmd
button with VBA behind the OnClick event.
My criteria is being ignored in my DLookup expression.
UName = DLookup("[StudUName]", "tblStudentAccess", "StudPW = '" &
Me.password & "'")
UName is Dimmed as variant (tried String as well)
Me.Password.value criteria is correctly read off the unbound form
password control.
Student Username (StudUName) returned is from the first record -which
is a different password.
It is as if the criteria is being ignored.
The result is UName returns a null, so the error routine kicks in.
I've never seen this before. Is my criteria syntax wrong? password is
a String
Any ideas?

Don

I have spent hours trying to solve a similar occurrence in an application that I was developing and have noticed a possible cause for a number of these enquiries --

DLookup validates its arguments - expression, domain and criteria - so that if you get a column name wrong etc. you will also get an error. Normally the criteria parameter has the form:

FieldName = Value

but, however, the "criteria" parameter accepts any expression that can be evaluated to a number - and where this evaluation returns ANY number except 0 (+ve or -ve), it will pick up the first row of the domain. If it evaluates to 0 (False) then it won't find any record in the domain A simple example will explain:

MyTable Contains 2 Rows and 2 columns: e.g.

MyKey MyVal
A ValForA
B ValForB

Dim MyVar As String
DIm MySearchKey As String
MySearchKey = "B"
MyVar = DLookUp("[MyVal]","MyTable","[MyKeyl] = '" & MySearchKey & "'")

MyVar will now contain "ValForB" as expected -

but if I had coded the assignment incorrectly - e.g.

MyVar = MySearchKey
MyVar = DLookUp("MyVal","MyTable","[MyVar] = '" & MySearchKey & "'")

instead of reporting an error - (there is no MyVar in the table), the system evaluates the equality MyVar = MySearchKey, returning -1. So MyVar now contains "ValForA" which is not what was intended. (without the MyVar = MySearchKey, the criteria would evaluate to 0 and a Null would be returned/

This may explain a number of DLookUp "first row" returns!

John