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