Is it possible to use IIF in format criteria of a table?

[Niklas Östergren]

Hi!

I would like to format a Zip Code differently depending on which country it
belongs to!

I have a table tblLookUpZipCode with fields:
- ZipCode (Primary Key)
- City
- Country

Is it possible to have contitioning formating of the zip code depending on
which value I have in field <Country> in the same table?

If so, how?

TIA!
// Niklas

 

[Douglas J. Steele]

You could do this using a query, but not going directly against the table.

In a query, you could create a computed field that uses Choose, IIf or
Switch to distinguish between the possibilities:

FormattedZipCode: Switch([Country] = "Canada", Format([ZipCode], "xxx"), _
[Country] = "UK", Format([ZipCode], "yyy"), _
[Country] = "USA", Format([ZipCode], "zzz"))

Sorry, but I was too lazy to figure out what xxx, yyy and zzz would have to
be (not that I think you can actually specify a format for UK Post Codes!)

 

[Niklas Östergren]

Hi!

Thanks for your answer! Not realy what I wanted to hear but now I know that
I have to solve it in the fom with VBA instead, which works just fine!

Or maby I should use it in the query and base the form on the q. instead of
the table!? Either way works!

Thanks for a quick reply!

// Niklas

You could do this using a query, but not going directly against the table.

In a query, you could create a computed field that uses Choose, IIf or
Switch to distinguish between the possibilities:

FormattedZipCode: Switch([Country] = "Canada", Format([ZipCode], "xxx"), _
[Country] = "UK", Format([ZipCode], "yyy"), _
[Country] = "USA", Format([ZipCode], "zzz"))

Sorry, but I was too lazy to figure out what xxx, yyy and zzz would have to
be (not that I think you can actually specify a format for UK Post Codes!)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi!

I would like to format a Zip Code differently depending on which country it
belongs to!

I have a table tblLookUpZipCode with fields:
- ZipCode (Primary Key)
- City
- Country

Is it possible to have contitioning formating of the zip code depending on
which value I have in field <Country> in the same table?

If so, how?

TIA!
// Niklas

 

[Douglas J. Steele]

In my opinion, forms should always be based on queries, never on tables.

For one thing, that's the only way you can control the order of the records.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi!

Thanks for your answer! Not realy what I wanted to hear but now I know that
I have to solve it in the fom with VBA instead, which works just fine!

Or maby I should use it in the query and base the form on the q. instead of
the table!? Either way works!

Thanks for a quick reply!

// Niklas


"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> skrev i meddelandet

You could do this using a query, but not going directly against the table.

In a query, you could create a computed field that uses Choose, IIf or
Switch to distinguish between the possibilities:

FormattedZipCode: Switch([Country] = "Canada", Format([ZipCode], "xxx"), _
[Country] = "UK", Format([ZipCode], "yyy"), _
[Country] = "USA", Format([ZipCode], "zzz"))

Sorry, but I was too lazy to figure out what xxx, yyy and zzz would have to
be (not that I think you can actually specify a format for UK Post Codes!)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi!

I would like to format a Zip Code differently depending on which

country
it

belongs to!

I have a table tblLookUpZipCode with fields:
- ZipCode (Primary Key)
- City
- Country

Is it possible to have contitioning formating of the zip code

depending

on

 

[Cindy]

I tried to do this before with telephone numbers, and was told it was
impossible. But, just tried it at the form level, and had success with
a Select Case statement in the After Update event of the textbox for
the country.

Select Case Me.txtCountry
Case "USA"
Me.txtPhone.InputMask = "000\-000\-0000;0;_"
Case "Mexico"
Me.txtPhone.InputMask = "000\-00\-000\-000\-0000;0;_"
End Select

This also preserves the formatting at the table level. I'm not sure if
this is what you were looking for, but I hope it helps you out.

Cindy

 

[Niklas Östergren]

Hi Douglas!

Yes, you are right! I will base the form on a query. It also give med better
control of the recordsource if I want to manipulate it, like sorting order
for instance!

// Niklas

In my opinion, forms should always be based on queries, never on tables.

For one thing, that's the only way you can control the order of the records.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Hi!

Thanks for your answer! Not realy what I wanted to hear but now I know that
I have to solve it in the fom with VBA instead, which works just fine!

Or maby I should use it in the query and base the form on the q. instead of
the table!? Either way works!

Thanks for a quick reply!

// Niklas


"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> skrev i meddelandet

You could do this using a query, but not going directly against the table.

In a query, you could create a computed field that uses Choose, IIf or
Switch to distinguish between the possibilities:

FormattedZipCode: Switch([Country] = "Canada", Format([ZipCode],

"xxx"),

_

[Country] = "UK", Format([ZipCode], "yyy"), _
[Country] = "USA", Format([ZipCode], "zzz"))

Sorry, but I was too lazy to figure out what xxx, yyy and zzz would

have
to

be (not that I think you can actually specify a format for UK Post Codes!)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Hi!

I would like to format a Zip Code differently depending on which country
it
belongs to!

I have a table tblLookUpZipCode with fields:
- ZipCode (Primary Key)
- City
- Country

Is it possible to have contitioning formating of the zip code

depending

on
which value I have in field <Country> in the same table?

If so, how?

TIA!
// Niklas

 

[Niklas Östergren]

Hi Cindy!

Thanks a lot! Yes I will probably do anything like that and then use it in
both forms and reports. So i think it would be a good ideá to write a public
function since I have a need for this in several objects, not just the form!

Thanks for your contribute!

// Niklas