Default value problem

[Tony Williams]

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

 

[Guest]

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

 

[Tony Williams]

Thanks Andy that worked fine
Tony

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