I use the Microsoft-supplied Orders database for a small business I run. Because I'm in the UK, I've had to amend certain fields within the tables, forms, reports etc to accept UK-style data. I've just come across one issue though that I'm struggling with.
In the "Customer" table, the post code mask I've been using is >LL0\ 0LL - for those not aware, the usual format is (A = alpha, N = numeric) AAN space NAA. The format after the space never changes, but the format before it can. It can be AANA or AANN. The majority of my customers are AAN space but today I've got one which is AANN space.
Changing the mask to >LL00\ 0LL works fine - for this customer. However, it's changed the format of all other customers. E.g. my main customer's post code is AA1 1AA, but after changing the mask, it's showing as AA11 AA. How do I get it to display AA1 1AA again, whilst showing my new customer as AA11 1AA?
Also, on the "Invoice" report form, the same problem arises.In the "Bill to" & "Ship to" address fields, the Properties for these show as =[City] & ", " & [StateOrProvince] & " " & Format([PostalCode],"!&&& &&&&"). I tried adding an extra "&" in the group of 3, but I end up with the same problem as above.
Any ideas as to how I can satisfy all formats in all forms/tables etc without having to amend the properties each time I use a different customer?
In the "Customer" table, the post code mask I've been using is >LL0\ 0LL - for those not aware, the usual format is (A = alpha, N = numeric) AAN space NAA. The format after the space never changes, but the format before it can. It can be AANA or AANN. The majority of my customers are AAN space but today I've got one which is AANN space.
Changing the mask to >LL00\ 0LL works fine - for this customer. However, it's changed the format of all other customers. E.g. my main customer's post code is AA1 1AA, but after changing the mask, it's showing as AA11 AA. How do I get it to display AA1 1AA again, whilst showing my new customer as AA11 1AA?
Also, on the "Invoice" report form, the same problem arises.In the "Bill to" & "Ship to" address fields, the Properties for these show as =[City] & ", " & [StateOrProvince] & " " & Format([PostalCode],"!&&& &&&&"). I tried adding an extra "&" in the group of 3, but I end up with the same problem as above.
Any ideas as to how I can satisfy all formats in all forms/tables etc without having to amend the properties each time I use a different customer?