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

Don Barton wrote:

> 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.

> 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

Don Barton wrote:

>> 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.

On Jan 13, 6:35*pm, Rick Brandt <rickbran...@hotmail.com> wrote:
> Don Barton wrote:
> >> 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.

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

On Jan 13, 6:35*pm, Rick Brandt <rickbran...@hotmail.com> wrote:
> Don Barton wrote:
> >> 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.

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