Zipcode formatting problem

  • Thread starter Thread starter Amit
  • Start date Start date
A

Amit

Hi,

I'm having a problem with displaying the hyphen in the
zipcode in a report when the length of the zipcode is
greater than 5.

In the table, the zipcode is stored as a text, minus the
hyphen.

I used the following code in the "Format" section of the
properties for the zipcode control:

"=IIf(Len([[PersonZip])>5,Format([[PersonZip],!@@@@@-
@@@@),Format([PersonZip],!@@@@@))"

This is not displaying the hyphen when the zipcode has
more than 5 characters. I'm not sure why this is not
working....

If instead of using an "IIf" function, I simply use "!
@@@@@-@@@@", then the zipcode is displayed with the
hyphen - even for zipcodes that only have 5 characters.

Am I not placing the code where it should be placed? Any
help on this will be appreciated.

Thanks!

-Amit
 
Just quick note to say I did notice the extra "[" in the
IIf expression and corrected it, and it is still not
working.
 
Amit said:
I'm having a problem with displaying the hyphen in the
zipcode in a report when the length of the zipcode is
greater than 5.

In the table, the zipcode is stored as a text, minus the
hyphen.

I used the following code in the "Format" section of the
properties for the zipcode control:

"=IIf(Len([[PersonZip])>5,Format([[PersonZip],!@@@@@-
@@@@),Format([PersonZip],!@@@@@))"
Click to expand...


You have numerous syntax errors in that expression, extra [s
and, missing quotes around the format specification.

=IIf(Len([PersonZip])>5, Format([PersonZip],"!@@@@@-@@@@"),
Format([PersonZip]",!@@@@@")
 
Hi Marshall,

I tried the code from your reply, and it's still not
working. As soon as I paste the code in the Format
section, and tab out, Access removes the quotes from
around '@' signs and puts them around the entire
expression. Also, was the last quote sign in your
expression misplaced? I tried your code and a different
variation (by placing the quote after the ',' in the
second Format expression), with the same results.

Is there a new-line character just before the second
Format expression, or it doesn't matter?

So, back to square one.

-Amit
-----Original Message-----
Amit said:
I'm having a problem with displaying the hyphen in the
zipcode in a report when the length of the zipcode is
greater than 5.

In the table, the zipcode is stored as a text, minus the
hyphen.

I used the following code in the "Format" section of the
properties for the zipcode control:

"=IIf(Len([[PersonZip])>5,Format([[PersonZip],!@@@@@-
@@@@),Format([PersonZip],!@@@@@))"
Click to expand...


You have numerous syntax errors in that expression, extra [s
and, missing quotes around the format specification.

=IIf(Len([PersonZip])>5, Format([PersonZip],"!@@@@@- @@@@"),
Format([PersonZip]",!@@@@@")
Click to expand...
 
Yes, the quote was misplaced.

No, there is not supposed to be any new lines.

I just noticed that you originally said that you were trying
to use this in the text box's Format property. That will
not work. The expression should be in the text box's
Control Source property.
--
Marsh
MVP [MS Access]


I tried the code from your reply, and it's still not
working. As soon as I paste the code in the Format
section, and tab out, Access removes the quotes from
around '@' signs and puts them around the entire
expression. Also, was the last quote sign in your
expression misplaced? I tried your code and a different
variation (by placing the quote after the ',' in the
second Format expression), with the same results.

Is there a new-line character just before the second
Format expression, or it doesn't matter?
-----Original Message-----
Amit said:
I'm having a problem with displaying the hyphen in the
zipcode in a report when the length of the zipcode is
greater than 5.

In the table, the zipcode is stored as a text, minus the
hyphen.

I used the following code in the "Format" section of the
properties for the zipcode control:

"=IIf(Len([[PersonZip])>5,Format([[PersonZip],!@@@@@-
@@@@),Format([PersonZip],!@@@@@))"
Click to expand...


You have numerous syntax errors in that expression, extra [s
and, missing quotes around the format specification.

=IIf(Len([PersonZip])>5, Format([PersonZip],"!@@@@@- @@@@"),
Format([PersonZip]",!@@@@@")
Click to expand...
Click to expand...
 
Yes, that worked!! Created a new text control and placed
the code in the 'control source' as you suggested.

Thanks for your help!!!

-Amit
-----Original Message-----
Yes, the quote was misplaced.

No, there is not supposed to be any new lines.

I just noticed that you originally said that you were trying
to use this in the text box's Format property. That will
not work. The expression should be in the text box's
Control Source property.
--
Marsh
MVP [MS Access]


I tried the code from your reply, and it's still not
working. As soon as I paste the code in the Format
section, and tab out, Access removes the quotes from
around '@' signs and puts them around the entire
expression. Also, was the last quote sign in your
expression misplaced? I tried your code and a different
variation (by placing the quote after the ',' in the
second Format expression), with the same results.

Is there a new-line character just before the second
Format expression, or it doesn't matter?
-----Original Message-----
Amit wrote:
I'm having a problem with displaying the hyphen in the
zipcode in a report when the length of the zipcode is
greater than 5.

In the table, the zipcode is stored as a text, minus the
hyphen.

I used the following code in the "Format" section of the
properties for the zipcode control:

"=IIf(Len([[PersonZip])>5,Format([[PersonZip],!@@@@@-
@@@@),Format([PersonZip],!@@@@@))"


You have numerous syntax errors in that expression,
Click to expand...
extra
[s
and, missing quotes around the format specification.

=IIf(Len([PersonZip])>5, Format([PersonZip],"!@@@@@- @@@@"),
Format([PersonZip]",!@@@@@")
Click to expand...
Click to expand...
.
Click to expand...
 
-----Original Message-----
Hi,

I'm having a problem with displaying the hyphen in the
zipcode in a report when the length of the zipcode is
greater than 5.

In the table, the zipcode is stored as a text, minus the
hyphen.

I used the following code in the "Format" section of the
properties for the zipcode control:

"=IIf(Len([[PersonZip])>5,Format([[PersonZip],!@@@@@-
@@@@),Format([PersonZip],!@@@@@))"

This is not displaying the hyphen when the zipcode has
more than 5 characters. I'm not sure why this is not
working....

If instead of using an "IIf" function, I simply use "!
@@@@@-@@@@", then the zipcode is displayed with the
hyphen - even for zipcodes that only have 5 characters.

Am I not placing the code where it should be placed? Any
help on this will be appreciated.

Thanks!

-Amit

.
I believe you need an Else statement after your IIF to
Click to expand...
reflect the zip with the hyphen if it does have more than
5 char.
 
Back
Top