if no mailaddress, then use the house address

[Michiel Rapati-Kekkonen]

I have a database with a table for customers and a related table with their
different addresses.
The addresses have a type field: a number that corresponds with a table
addresstypes:
house-address = 1, mailaddress = 4, delivery-address =2, etc.

now I have an orderform with two subforms for two addresstypes.
The second shows the delivery address, if available.
The first should show the mailaddress, but if that is not present in the
database, then it should take the house address.
if no 4 then 1...

There is my problem.
I donot succeed in producing the proper query for that subform.
If I make something, then it wants to show both addresses, when they are
both available.
It should however show only one, and preferably the mailaddress.

Can someone help me?

TIA

Michiel

 

[Evi]

I have a database with a table for customers and a related table with their
different addresses.
The addresses have a type field: a number that corresponds with a table
addresstypes:
house-address = 1, mailaddress = 4, delivery-address =2, etc.

now I have an orderform with two subforms for two addresstypes.
The second shows the delivery address, if available.
The first should show the mailaddress, but if that is not present in the
database, then it should take the house address.
if no 4 then 1...

There is my problem.
I donot succeed in producing the proper query for that subform.
If I make something, then it wants to show both addresses, when they are
both available.
It should however show only one, and preferably the mailaddress.

Can someone help me?

TIA

Michiel

In the query on which your subform is built add an extra field -
MailOrDeliv.
The basic formula is

MailOrDeliv: IIF (IsNull([MailAddress]),[Delivery-Address],[MailAddress])

The snag with this, is that this field is not updatable so you would need eg
a DoubleClick event to open a form to let you update Mail or Delivery
Addresses.

The other solution, for an updatable field, is to put 2 Subforms on top of
each other, one showing only the MailAddress and one only the House
Addressand in the Main form's OnCurrent event have a DLookup which checks
the address record for that customer and if eg MailAddress is present sets
Me.MyMailSubForm.Visible = True
Me.MyHouseSubForm.Visible = False
Evi

 

[Michiel Rapati-Kekkonen]

Oops, my mistake, I translated to easily from Dutch
My problem does not concern Forms, but Reports
and more precisely: subreports.

For the moment I look if the Iif can help me in any way.

Thanks for your response

Michiel

I have a database with a table for customers and a related table with their
different addresses.
The addresses have a type field: a number that corresponds with a table
addresstypes:
house-address = 1, mailaddress = 4, delivery-address =2, etc.

now I have an orderform with two subforms for two addresstypes.
The second shows the delivery address, if available.
The first should show the mailaddress, but if that is not present in the
database, then it should take the house address.
if no 4 then 1...

There is my problem.
I donot succeed in producing the proper query for that subform.
If I make something, then it wants to show both addresses, when they are
both available.
It should however show only one, and preferably the mailaddress.

Can someone help me?

TIA

Michiel

In the query on which your subform is built add an extra field -
MailOrDeliv.
The basic formula is

MailOrDeliv: IIF (IsNull([MailAddress]),[Delivery-Address],[MailAddress])

The snag with this, is that this field is not updatable so you would need
eg
a DoubleClick event to open a form to let you update Mail or Delivery
Addresses.

The other solution, for an updatable field, is to put 2 Subforms on top of
each other, one showing only the MailAddress and one only the House
Addressand in the Main form's OnCurrent event have a DLookup which checks
the address record for that customer and if eg MailAddress is present sets
Me.MyMailSubForm.Visible = True
Me.MyHouseSubForm.Visible = False
Evi