How to make a blank field move to the next field?...

[radar2000]

Hi everyone.

Does any know how to instruct Access to move to the next available field when it encounters a blank field? For example:

If you have 3 address fields

Address1
Address2
City

If Address 2 contains blank data, I would like Access to do the following:

Address1
City

I don't want Access to do this:

Address1
<Blank>
City

Any suggestions would be appreciated.

Thank you.

 

[Andrew Fergus]

Hi, in what context do you want to do this? On a form or a report?
Andrew

 

[radar2000]

Hi, in what context do you want to do this? On a form or a report?
Andrew

Hi Andrew.

Thanks for your reply.

I would like to do this in the report mode.

 

[Corticus]

Assuming these control and field names:

txtAdd1 = [address1]
txtAdd2 = [address2]
txtCity = [city]

Try using formulas instead of the field name like:
txtAdd1 = Iif(isnull([address1]), [address2], [address1])
txtAdd2 = Iif(isnull([address2]), [city], [address2])
txtCity = Iif(isnull([address2]),null,[city])

This assumes at least one address. If there is sometimes no addresses at all, you'll have to nest another If statement.

An alternative would be to pull these pieces of data as records in a single field in a query and then sort the query.

Hope this helps,

 

[radar2000]

Assuming these control and field names:

txtAdd1 = [address1]
txtAdd2 = [address2]
txtCity = [city]

Try using formulas instead of the field name like:
txtAdd1 = Iif(isnull([address1]), [address2], [address1])
txtAdd2 = Iif(isnull([address2]), [city], [address2])
txtCity = Iif(isnull([address2]),null,[city])

This assumes at least one address. If there is sometimes no addresses at all, you'll have to nest another If statement.

An alternative would be to pull these pieces of data as records in a single field in a query and then sort the query.

Hope this helps,

Hi Corticus.

That did the trick! ray:

Thank you everyone for all your help.