Yet another DLookup problem!

[Tony Williams]

I thought I'd got the hang of DLookup but just recently I cannot get any of
them to work, so frustrating when I think I'm following convention.
Anyway I have a form (frmmain) on which is a subform (frmcomplaint) The
frmcomplaint has a control txtauthto (which has a control source
=[cmbemployee].[column](1)) I want to check the value of txtauthto against a
table tblindividual and find the value of a field txtsurname where the value
of another field txtmemnumber =thevalue of txtauthto on my form.
Here's my Dlookup:
=DLookUp("[txtsurname]","[
tblindividual]","[txtmemnumber]=Forms!frmmain!frmcomplaint.Form![txtauthto]")

But I get#Error. Where am I going wrong?
Thanks
Tony

 

[RonaldoOneNil]

Your variable needs to be outside the double quotes and enclosed by single
quotes if its text.
=DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]='" &
Forms!frmmain!frmcomplaint.Form![txtauthto] & "'")

 

[Tony Williams]

Thanks for that but now I get #Name?
Any ideas?
Tony

Your variable needs to be outside the double quotes and enclosed by single
quotes if its text.
=DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]='" &
Forms!frmmain!frmcomplaint.Form![txtauthto] & "'")

I thought I'd got the hang of DLookup but just recently I cannot get any of
them to work, so frustrating when I think I'm following convention.
Anyway I have a form (frmmain) on which is a subform (frmcomplaint) The
frmcomplaint has a control txtauthto (which has a control source
=[cmbemployee].[column](1)) I want to check the value of txtauthto against a
table tblindividual and find the value of a field txtsurname where the value
of another field txtmemnumber =thevalue of txtauthto on my form.
Here's my Dlookup:
=DLookUp("[txtsurname]","[
tblindividual]","[txtmemnumber]=Forms!frmmain!frmcomplaint.Form![txtauthto]")

But I get#Error. Where am I going wrong?
Thanks
Tony

 

[RonaldoOneNil]

Are txtmemnumber and txtauthto both text ?
If not, you do not need to enclose in single quotes
=DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]=" &
Forms!frmmain!frmcomplaint.Form![txtauthto])

Thanks for that but now I get #Name?
Any ideas?
Tony

Your variable needs to be outside the double quotes and enclosed by single
quotes if its text.
=DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]='" &
Forms!frmmain!frmcomplaint.Form![txtauthto] & "'")

I thought I'd got the hang of DLookup but just recently I cannot get any of
them to work, so frustrating when I think I'm following convention.
Anyway I have a form (frmmain) on which is a subform (frmcomplaint) The
frmcomplaint has a control txtauthto (which has a control source
=[cmbemployee].[column](1)) I want to check the value of txtauthto against a
table tblindividual and find the value of a field txtsurname where the value
of another field txtmemnumber =thevalue of txtauthto on my form.
Here's my Dlookup:
=DLookUp("[txtsurname]","[
tblindividual]","[txtmemnumber]=Forms!frmmain!frmcomplaint.Form![txtauthto]")

But I get#Error. Where am I going wrong?
Thanks
Tony

 

[Tony Williams]

They're both numbers
Tony

Are txtmemnumber and txtauthto both text ?
If not, you do not need to enclose in single quotes
=DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]=" &
Forms!frmmain!frmcomplaint.Form![txtauthto])

Thanks for that but now I get #Name?
Any ideas?
Tony

Your variable needs to be outside the double quotes and enclosed by single
quotes if its text.
=DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]='" &
Forms!frmmain!frmcomplaint.Form![txtauthto] & "'")

:

I thought I'd got the hang of DLookup but just recently I cannot get any of
them to work, so frustrating when I think I'm following convention.
Anyway I have a form (frmmain) on which is a subform (frmcomplaint) The
frmcomplaint has a control txtauthto (which has a control source
=[cmbemployee].[column](1)) I want to check the value of txtauthto against a
table tblindividual and find the value of a field txtsurname where the value
of another field txtmemnumber =thevalue of txtauthto on my form.
Here's my Dlookup:
=DLookUp("[txtsurname]","[
tblindividual]","[txtmemnumber]=Forms!frmmain!frmcomplaint.Form![txtauthto]")

But I get#Error. Where am I going wrong?
Thanks
Tony

 

[Tony Williams]

I deserve a good kicking!!!!! Found the problem it was a spelling mistake the
field txtauthto was infact txauthto note the missing 2nd "t"
Sorry Ronaldo!!!
Thanks foryour help anyway
Sorry if I wasted your tme
Tony

They're both numbers
Tony

Are txtmemnumber and txtauthto both text ?
If not, you do not need to enclose in single quotes
=DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]=" &
Forms!frmmain!frmcomplaint.Form![txtauthto])

Thanks for that but now I get #Name?
Any ideas?
Tony

:

Your variable needs to be outside the double quotes and enclosed by single
quotes if its text.
=DLookUp("[txtsurname]","[tblindividual]","[txtmemnumber]='" &
Forms!frmmain!frmcomplaint.Form![txtauthto] & "'")

:

I thought I'd got the hang of DLookup but just recently I cannot get any of
them to work, so frustrating when I think I'm following convention.
Anyway I have a form (frmmain) on which is a subform (frmcomplaint) The
frmcomplaint has a control txtauthto (which has a control source
=[cmbemployee].[column](1)) I want to check the value of txtauthto against a
table tblindividual and find the value of a field txtsurname where the value
of another field txtmemnumber =thevalue of txtauthto on my form.
Here's my Dlookup:
=DLookUp("[txtsurname]","[
tblindividual]","[txtmemnumber]=Forms!frmmain!frmcomplaint.Form![txtauthto]")

But I get#Error. Where am I going wrong?
Thanks
Tony