Am I getting loopy or what?

OK, so am I getting loopy or what?   Have a long standing procedure in Access 2000 that formats a postal code based on a country.

Format is '&#& #&#', which is used for Canada.   It no longer seems to work.


? Format$("L9T0K1","&#& #&#")


L#9 #T#0K1

Anyone else having issues all of a sudden with format?  I'm wondering if an office or windows update has messed something up.

LVL 60
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

How do you want the results  of Format$("L9T0K1","&#& #&#") look?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerAuthor Commented:
It's supposed to look like L9T 0K1

Format fills right to left, so the 1 should be the first #, K the first &, 0 the second #, and so on.

This routine has been in place for years and years and never had a problem.  Now all of a sudden it does.

I'm seeing this on multiple machines, so either I missing something really obvious, or an update clobbered something.

Rey Obrero (Capricorn1)Commented:
i get the same result.

Canada postal code format is   L9T 0K1

try something  like format("L9T0K1","@@@ @@@")
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeCommented:
Alright, Jim.  I'll ask all the basic questions?

Office/Access Version?
OS? (patch/update)?

There was some discussion about a week ago (maybe longer) about an Office update that caused a bunch of havoc with several Access things.  Don't remember what version it applied to but I think the fix was to restore the pre-update version.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerAuthor Commented:

<<Alright, Jim.  I'll ask all the basic questions?>>

<<Don't remember what version it applied to but I think the fix was to restore the pre-update version.>>

I'll have to start digging...for the moment, I was looking for agreement that the format should work as stated (that's partly why the title of the question).

Second was to see if anyone else was having format issues.


<<try something  like format("L9T0K1","@@@ @@@") >>

 Already did last night at 1:30 am<g>, which does work for now.   No real reason I can't keep it that way that I can think of, but I loath to change something so fundamental to the application that has been in place for eight years and working fine.

 My main goal is to understand why all of a sudden this failed.  A Office/Windows update is the only thing I can think of.

 Guess I should have stated up front that the question is more about:

1. Confirming there is a problem.
2. And if so, serveing as a warning to be on the lookout for this.

I'd love to have someone check this on a machine that doesn't get patched often.   I don't have any machines here that are not on automatic update.

 Ah wait, there's an old laptop here!  I'll give that a go.

Dale FyeCommented:
I'm running A2007, SP3 MSO(12.0.6683.5000) and it is doing the same as you displayed in your original post.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerAuthor Commented:
Thanks for checking.

Rey Obrero (Capricorn1)Commented:
i have
A2007 (12.0.6606.1000) SP3 MSO(12.0.6683.5000)

and it is showing the same result as what you have.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.