Why Doesn't This Work

[Hank]

I have a report with a text box control. I am trying to use this expression
for the control. All the underlying forms are open . All else works except
this. Don't know why. Please assist!!! Code Follows

=DLookUp("Prefix","AirlinePrefix","Airline Name = " & [Forms]![Logistics &
Routing]![Carrier])

"Prefix" is the field
"AirlinePrefix" is the table
"Airline Name" is the other field
"Logistics & Routing" is the open form and "Carrier" is a populated field

All I want is the 3 digit prefix that coorisponds to the record where
Airline Name matches Carrier on the form!

Please Help!

 

[Dirk Goldgar]

I have a report with a text box control. I am trying to use this
expression
for the control. All the underlying forms are open . All else works except
this. Don't know why. Please assist!!! Code Follows

=DLookUp("Prefix","AirlinePrefix","Airline Name = " & [Forms]![Logistics &
Routing]![Carrier])

"Prefix" is the field
"AirlinePrefix" is the table
"Airline Name" is the other field
"Logistics & Routing" is the open form and "Carrier" is a populated field

All I want is the 3 digit prefix that coorisponds to the record where
Airline Name matches Carrier on the form!

Please Help!

You don't say, but probably AirlineName is a text field, and so the literal
value you are bvuilding into your criteria argument must be enclosed in
quotes. Try this:

=DLookUp("Prefix","AirlinePrefix","Airline Name = """ &
[Forms]![Logistics & Routing]![Carrier] & """")

The following would also work, I think, because I believe Access would
evaluate the form reference as a query parameter::

=DLookUp("Prefix","AirlinePrefix","Airline Name = [Forms]![Logistics &
Routing]![Carrier]")

 

[Hank]

I copied and pasted both of your examples and neither of them worked either.
I get and Error in the textbox field on the report. Any further suggestions
would be gladly accepted!

I have a report with a text box control. I am trying to use this
expression
for the control. All the underlying forms are open . All else works except
this. Don't know why. Please assist!!! Code Follows

=DLookUp("Prefix","AirlinePrefix","Airline Name = " & [Forms]![Logistics &
Routing]![Carrier])

"Prefix" is the field
"AirlinePrefix" is the table
"Airline Name" is the other field
"Logistics & Routing" is the open form and "Carrier" is a populated field

All I want is the 3 digit prefix that coorisponds to the record where
Airline Name matches Carrier on the form!

Please Help!

You don't say, but probably AirlineName is a text field, and so the literal
value you are bvuilding into your criteria argument must be enclosed in
quotes. Try this:

=DLookUp("Prefix","AirlinePrefix","Airline Name = """ &
[Forms]![Logistics & Routing]![Carrier] & """")

The following would also work, I think, because I believe Access would
evaluate the form reference as a query parameter::

=DLookUp("Prefix","AirlinePrefix","Airline Name = [Forms]![Logistics &
Routing]![Carrier]")


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I copied and pasted both of your examples and neither of them worked
either.
I get and Error in the textbox field on the report. Any further
suggestions
would be gladly accepted!

I just noticed you don't have square brackets around the "Airline Name"
field name, which has a space in it (bad idea). If that name is correct,
then my suggestions need to be modified like this:

=DLookUp("Prefix","AirlinePrefix","[Airline Name] = """ &
[Forms]![Logistics & Routing]![Carrier] & """")

or

=DLookUp("Prefix","AirlinePrefix","[Airline Name] = [Forms]![Logistics &
Routing]![Carrier]")

Incidentally, the " & " in your form name is also a bad idea.

 

[Hank]

Actually, Airline name is a field in the AirlinePrefix Table. Doe this make
a difference. Thx for the naming Convention Tips also

I copied and pasted both of your examples and neither of them worked
either.
I get and Error in the textbox field on the report. Any further
suggestions
would be gladly accepted!

I just noticed you don't have square brackets around the "Airline Name"
field name, which has a space in it (bad idea). If that name is correct,
then my suggestions need to be modified like this:

=DLookUp("Prefix","AirlinePrefix","[Airline Name] = """ &
[Forms]![Logistics & Routing]![Carrier] & """")

or

=DLookUp("Prefix","AirlinePrefix","[Airline Name] = [Forms]![Logistics &
Routing]![Carrier]")

Incidentally, the " & " in your form name is also a bad idea.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Actually, Airline name is a field in the AirlinePrefix Table. Doe this
make
a difference.

Yes, that's where the code assumes it is. Did you try the alternative
suggestions in my last post?

Thx for the naming Convention Tips also

You're welcome. The reason to avoid special characters, such as spaces and
punctuation, in object names is that such characters force you to "quote"
those names with square brackets when you refer to them. If you don't, the
names are generally not parsed properly and give rise to errors such as the
one I believe you're experiencing now.