Dlookup problem

[larochy]

Hi, I'm trying to lookup the customer name from <tblRNowCustomers> in a form
based off of a combo box called <Combo13> which allows the user to choose the
<CustomerID> which is a customer number. I want it to not show a name if the
combo is blank. It's giving me the #error code so any help you could provide
would be appreciated, thanks.

=IIf([Combo13]="" Or IsNull([Combo13]),"",DLookUp("Customer
Name","[tblRNowCustomers]","[Customer ID]='" & [Forms]![frmInvoice]![Combo13]
& "'"))

 

[rico]

If Customer Name has a space between the two words then you might need to
bracket them so:

Dlookup("[Customer Name]", etc

I would suggest its good practice to not use spaces in names for fields or
controls.

HTH

Rico

 

[larochy]

I put brackets around Customer Name and it's still not working....

If Customer Name has a space between the two words then you might need to
bracket them so:

Dlookup("[Customer Name]", etc

I would suggest its good practice to not use spaces in names for fields or
controls.

HTH

Rico

Hi, I'm trying to lookup the customer name from <tblRNowCustomers> in a form
based off of a combo box called <Combo13> which allows the user to choose the
<CustomerID> which is a customer number. I want it to not show a name if the
combo is blank. It's giving me the #error code so any help you could provide
would be appreciated, thanks.

=IIf([Combo13]="" Or IsNull([Combo13]),"",DLookUp("Customer
Name","[tblRNowCustomers]","[Customer ID]='" & [Forms]![frmInvoice]![Combo13]
& "'"))

 

[rico]

Ok, this should be it.

I would assume that Customer ID is a number right? if so then you dont need
the extra apostraphe's around combo13, so:

DLookUp("[Customer Name]","[tblRNowCustomers]","[Customer ID]=" & Combo13]))

HTH

I put brackets around Customer Name and it's still not working....

If Customer Name has a space between the two words then you might need to
bracket them so:

Dlookup("[Customer Name]", etc

I would suggest its good practice to not use spaces in names for fields or
controls.

HTH

Rico

Hi, I'm trying to lookup the customer name from <tblRNowCustomers> in a form
based off of a combo box called <Combo13> which allows the user to choose the
<CustomerID> which is a customer number. I want it to not show a name if the
combo is blank. It's giving me the #error code so any help you could provide
would be appreciated, thanks.

=IIf([Combo13]="" Or IsNull([Combo13]),"",DLookUp("Customer
Name","[tblRNowCustomers]","[Customer ID]='" & [Forms]![frmInvoice]![Combo13]
& "'"))

 

[Rick]

I put brackets around Customer Name and it's still not working....

If Customer Name has a space between the two words then you might
need to bracket them so:

Dlookup("[Customer Name]", etc

I would suggest its good practice to not use spaces in names for
fields or controls.

HTH

Rico

Hi, I'm trying to lookup the customer name from <tblRNowCustomers>
in a form based off of a combo box called <Combo13> which allows
the user to choose the <CustomerID> which is a customer number. I
want it to not show a name if the combo is blank. It's giving me
the #error code so any help you could provide would be appreciated,
thanks.

=IIf([Combo13]="" Or IsNull([Combo13]),"",DLookUp("Customer
Name","[tblRNowCustomers]","[Customer ID]='" &
[Forms]![frmInvoice]![Combo13] & "'"))

[Forms]![frmInvoice]![Combo13]

Personally, I'd set this to a variable and use NZ around it before
passing it. Make sure that value is good before passing it. Ther are
lots of things that can go wrong with that (i.e. form not open, cobmo
not initialized, comba rowsource isn't working, etc.)

 

[Rick]

I put brackets around Customer Name and it's still not working....

If Customer Name has a space between the two words then you might
need to bracket them so:

Dlookup("[Customer Name]", etc

I would suggest its good practice to not use spaces in names for
fields or controls.

HTH

Rico

Hi, I'm trying to lookup the customer name from <tblRNowCustomers>
in a form based off of a combo box called <Combo13> which allows
the user to choose the <CustomerID> which is a customer number. I
want it to not show a name if the combo is blank. It's giving me
the #error code so any help you could provide would be appreciated,
thanks.

=IIf([Combo13]="" Or IsNull([Combo13]),"",DLookUp("Customer
Name","[tblRNowCustomers]","[Customer ID]='" &
[Forms]![frmInvoice]![Combo13] & "'"))

[Forms]![frmInvoice]![Combo13]

Personally, I'd set this to a variable and use NZ around it before
passing it. Make sure that value is good before passing it. There are
lots of things that can go wrong with that (i.e. form not open, combo
not initialized, combo rowsource isn't working, return a number instead
of a string because wrong column is the first one, etc.)

 

[larochy]

Thanks Rico, that did the trick.

The reason I added the if statement is when you hit cancel on the form
because maybe you want to start over or cancel the data entry you've already
done, the customer name field remains populated with whatever the DLookup
returns. I thought if I add the if, is null statment it would take care of
this problem which it seems to have done

Thanks everyone for your replies.

Hi, I'm trying to lookup the customer name from <tblRNowCustomers> in a form
based off of a combo box called <Combo13> which allows the user to choose the
<CustomerID> which is a customer number. I want it to not show a name if the
combo is blank. It's giving me the #error code so any help you could provide
would be appreciated, thanks.

=IIf([Combo13]="" Or IsNull([Combo13]),"",DLookUp("Customer
Name","[tblRNowCustomers]","[Customer ID]='" & [Forms]![frmInvoice]![Combo13]
& "'"))

Won't the DLookup return Null if there's no match of Customer ID? In that case, you
don't need the IIf at all.
If the Combo box is empty, there should be no match and the customer name would be
blank.

=DLookUp("[Customer Name]","[tblRNowCustomers]","[Customer ID]='" & [Combo13] & "'"))



Tom Lake