Dlookup-syntax

[JMay]

What's wrong syntactically with:
=DLookUp("[Zone]","tblZipZone","[ZZip] =
Chr(34)&Forms!frmMain.Left([Zip],3)&Chr(34)")
Thanks in Advance,

 

[MacDermott]

Try it like this:

=DLookUp("[Zone]","tblZipZone","[ZZip] =" &
Chr(34)&Forms!frmMain.Left([Zip],3)&Chr(34))

Also add spaces around your & characters.

HTH
- Turtle

 

[JMay]

Tks Turtle, I've inched forward (I think); at least now I'm getting at least
#Name? in my textbox. No matter that I enter it as below including ...
frmMain.Left([Zip],3) &...
it is converted (by Access) to ... [frmMain].

([Zip],3) &...
Is that my problem (the Left in [ ]'s looks suspicious!!;

Further, is it best sometimes to delete the textbox you have been trying to
create this thing in and create a new one fresh and enter your suggested
code into the new one?

Much appreciated......

Try it like this:

=DLookUp("[Zone]","tblZipZone","[ZZip] =" &
Chr(34)&Forms!frmMain.Left([Zip],3)&Chr(34))

Also add spaces around your & characters.

HTH
- Turtle

What's wrong syntactically with:
=DLookUp("[Zone]","tblZipZone","[ZZip] =
Chr(34)&Forms!frmMain.Left([Zip],3)&Chr(34)")
Thanks in Advance,

 

[Debra Dalgleish]

The Left function should enclose the entire name:

DLookUp("Zone","tblZipZone","[ZZip]='" & Left([Forms]![frmMain].[Zip],3)
& "'")

What's wrong syntactically with:
=DLookUp("[Zone]","tblZipZone","[ZZip] =
Chr(34)&Forms!frmMain.Left([Zip],3)&Chr(34)")

 

[Fredg]

You placed Left in the position after a dot, (forms!FormName.Property) so
that Access is expecting a property not a function.
(It should have been a bang (!) anyway.)

What is the name of the form that contains this DLookUp?
Is it frmMain?

If so you do not need the forms!FormName!ControlName syntax:

=DLookUp("[Zone]","tblZipZone","[ZZip] =" & Chr(34) & Left([Zip],3) &
Chr(34))

Also, as it's highly unlikely there are any single quotes stored in a Zip
code field you could use:
=DLookUp("[Zone]","tblZipZone","[ZZip] = '" & Left([Zip],3) & "'")

If the control is not on frmMain, then the syntax should be:

"[ZZip] = " & chr(34) & Left(forms!frmMain![Zip],3) & chr(34)
or..
"[ZZip] = '" & Left(forms!frmMain![Zip],3) & "'")

Make sure the name of this control is not "ZZip" or "Zone".
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

Tks Turtle, I've inched forward (I think); at least now I'm getting at least
#Name? in my textbox. No matter that I enter it as below including ...
frmMain.Left([Zip],3) &...
it is converted (by Access) to ... [frmMain].

([Zip],3) &...
Is that my problem (the Left in [ ]'s looks suspicious!!;

Further, is it best sometimes to delete the textbox you have been trying to
create this thing in and create a new one fresh and enter your suggested
code into the new one?

Much appreciated......

Try it like this:

=DLookUp("[Zone]","tblZipZone","[ZZip] =" &
Chr(34)&Forms!frmMain.Left([Zip],3)&Chr(34))

Also add spaces around your & characters.

HTH
- Turtle

What's wrong syntactically with:
=DLookUp("[Zone]","tblZipZone","[ZZip] =
Chr(34)&Forms!frmMain.Left([Zip],3)&Chr(34)")
Thanks in Advance,

​

 

[JMay]

Fredg;
Thank you so much, I have printed your explantion out for a permanent
reference, best explantion of Dlookup() I have gotten today, or any day.
JMay

You placed Left in the position after a dot, (forms!FormName.Property) so
that Access is expecting a property not a function.
(It should have been a bang (!) anyway.)

What is the name of the form that contains this DLookUp?
Is it frmMain?

If so you do not need the forms!FormName!ControlName syntax:

=DLookUp("[Zone]","tblZipZone","[ZZip] =" & Chr(34) & Left([Zip],3) &
Chr(34))

Also, as it's highly unlikely there are any single quotes stored in a Zip
code field you could use:
=DLookUp("[Zone]","tblZipZone","[ZZip] = '" & Left([Zip],3) & "'")

If the control is not on frmMain, then the syntax should be:

"[ZZip] = " & chr(34) & Left(forms!frmMain![Zip],3) & chr(34)
or..
"[ZZip] = '" & Left(forms!frmMain![Zip],3) & "'")

Make sure the name of this control is not "ZZip" or "Zone".
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

Tks Turtle, I've inched forward (I think); at least now I'm getting at least
#Name? in my textbox. No matter that I enter it as below including ...
frmMain.Left([Zip],3) &...
it is converted (by Access) to ... [frmMain].

([Zip],3) &...
Is that my problem (the Left in [ ]'s looks suspicious!!;

Further, is it best sometimes to delete the textbox you have been trying to
create this thing in and create a new one fresh and enter your suggested
code into the new one?

Much appreciated......

Try it like this:

=DLookUp("[Zone]","tblZipZone","[ZZip] =" &
Chr(34)&Forms!frmMain.Left([Zip],3)&Chr(34))

Also add spaces around your & characters.

HTH
- Turtle

What's wrong syntactically with:
=DLookUp("[Zone]","tblZipZone","[ZZip] =
Chr(34)&Forms!frmMain.Left([Zip],3)&Chr(34)")
Thanks in Advance,

​

​

 

[JMay]

Thanks Debra,
Appreciate the straight-forward explanation. Most worthwhile receiving your
input..
JMay

The Left function should enclose the entire name:

DLookUp("Zone","tblZipZone","[ZZip]='" & Left([Forms]![frmMain].[Zip],3)
& "'")

What's wrong syntactically with:
=DLookUp("[Zone]","tblZipZone","[ZZip] =
Chr(34)&Forms!frmMain.Left([Zip],3)&Chr(34)")