Lookup doesn't use format

[Jasper de Bruin]

When I do a lookup for a column in a table, it doesn't
show the format of the original column. The original
column contains numbers, formatted as currency, but in the
lookup column it shows as just numbers, except for the
first entry, but when you click it, the format disappears.

Formatting the lookup column (the new one) doesn't work
either.

How can I format the new column as currency?

 

[John Vinson]

How can I format the new column as currency?

By not using Microsoft's misdesigned, misleading, and all but useless
so-called Lookup feature AT ALL.

Instead, use a Form based on a Query joining the two tables, or put a
Combo Box based on the second table on a form bound to the first; you
can specify the format of the textbox or combo box to whatever you
like.

 

[Allen Browne]

In what context?

The simplest solution will be to feed the query into a form or report, and
set the Format property of the text box to Currency.

Another option would be to wrap the lookup in the function that converts to
currency. Unfortunately it cannot handle Nulls, so you would need to use
Nz():
Nz(CCur(DLookup(...)))

I third option would be to format the lookup as currency:
Format(DLookup(...), "Currency")
However, be aware that this changes the field into a string (text), so is
not a good idea where you want to perform calcualtions later.

 

[Guest]

Yeah...

I used a macro and a query now.

Thanks!

-----Original Message-----
In what context?

The simplest solution will be to feed the query into a form or report, and
set the Format property of the text box to Currency.

Another option would be to wrap the lookup in the function that converts to
currency. Unfortunately it cannot handle Nulls, so you would need to use
Nz():
Nz(CCur(DLookup(...)))

I third option would be to format the lookup as currency:
Format(DLookup(...), "Currency")
However, be aware that this changes the field into a string (text), so is
not a good idea where you want to perform calcualtions later.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.




.