Default value problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tony Williams - 24 May 2007 09:47 GMT

I have a form that is based on a query that is based on 1 table. One control
is Txtmemnbr and is a number field and therefore is shown on the form as 0
when the form opens. The control on the form that displays the surname is
txtsurname and is a Dlookup control. There could be many surnames that don't
have a real value for txtmemnbr and so would be 0. However when I open the
form as the value of txtmemnbr defaults to 0 the txtsurname control shows
the first surname in the table where the value of txtmemnbr is 0, when I
want it to be blank. Is the only way round this to make the field txtmemnbr
field a text field?
Thanks
Tony

Reply to this Message

Andy Hull - 24 May 2007 15:37 GMT

Hi Tony

What is this form for? and are you happy with seeing a zero in the txtmemnbr
box?
If that is ok then amend your dlookup to something like...

=iif(nz(textmemnbr,0)=0,"",dlookup("surname", "tablename", "MemNbr = " &
me.txtmemnbr))

If you don't like seeing the zero then go back to this field in the table
design and remove the default setting and make sure Required is set to No.

Note that this will mean for new rows, memnbr will be Null. This is no
problem but you may need to check any existing queries etc that you have will
work as they are or need to be amended. The above iif statement works for
zeroes abd nulls because of the nz part of it.

hth

Andy Hull

> I have a form that is based on a query that is based on 1 table. One control
> is Txtmemnbr and is a number field and therefore is shown on the form as 0
[quoted text clipped - 7 lines]
> Thanks
> Tony

Reply to this Message

Tony Williams - 24 May 2007 17:03 GMT

Thanks Andy that worked fine
Tony

> Hi Tony
>
[quoted text clipped - 35 lines]
>> Thanks
>> Tony

Reply to this Message