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

  • Thread starter Thread starter Niklas Östergren
  • Start date Start date
N

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
 
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!)
 
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 said:
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)


Niklas Östergren said:
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
Click to expand...
Click to expand...
 
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)


Niklas Östergren said:
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)


Niklas Östergren said:
Hi!

I would like to format a Zip Code differently depending on which
Click to expand...
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
Click to expand...
Click to expand...
depending
on
Click to expand...
Click to expand...
 
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
 
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


Douglas J. Steele said:
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)


Niklas Östergren said:
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],
Click to expand...
Click to expand...
"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
Click to expand...
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
Click to expand...
depending
on
which value I have in field <Country> in the same table?

If so, how?

TIA!
// Niklas
Click to expand...
Click to expand...
Click to expand...
 
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
 
Back
Top