Get $0.00 to display on a Currency text box on a form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

magicdds- - 25 Mar 2008 09:10 GMT

I have a form that has a text box whose format is Currency. The control
source is a DLookUp function to a Query. When the result of the DLookUp has a
value, it displays in the text box correctly (i.e.; $234.78). When the
DLookUp returns NULL, the text box on the form is blank. Is there a way to
get the text box to display $0.00 if the DLookUp returns NULL?

Reply to this Message

Douglas J. Steele - 25 Mar 2008 11:27 GMT

Wrap the Nz function around your DLookup:

=Nz(DLookup("FieldName", "TableName", "Condition"), 0)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I have a form that has a text box whose format is Currency. The control
> source is a DLookUp function to a Query. When the result of the DLookUp
> has a
> value, it displays in the text box correctly (i.e.; $234.78). When the
> DLookUp returns NULL, the text box on the form is blank. Is there a way to
> get the text box to display $0.00 if the DLookUp returns NULL?

Reply to this Message

Maurice - 25 Mar 2008 11:42 GMT

Try adjusting the dlookup as follows:

Me.[field] = Nz(DLookup("lookupvalue", "tablename", "criteriafield" &
criteria), 0)

In this sample change the following to your own situation:

me.field = the field you want the value to store on your form
lookupvalue = the field you are looking for in the table
tablename= your tablename
criteriafield = criteriafield from the form to which the dlookup should do
the search
criteria= the field from the form you are referering

The NZ part tells the Dlookup to place a 0 instead of the Null value. I
assume you have a format set on the form which makes it $0.00

hth

Signature

Maurice Ausum

> I have a form that has a text box whose format is Currency. The control
> source is a DLookUp function to a Query. When the result of the DLookUp has a
> value, it displays in the text box correctly (i.e.; $234.78). When the
> DLookUp returns NULL, the text box on the form is blank. Is there a way to
> get the text box to display $0.00 if the DLookUp returns NULL?

Reply to this Message