Losing formatting and currency symbol

[PeteJohnston]

I have an Invoicing database in Access2003 which I wrote a few years back and it has been working well for all that time.

One of the main forms shows a listbox which is populated by a query showing invoice number, customer, invoice date, pay date, invoice amount and pay amount. The table which holds the invoice header has formatting on that column like &&&&\/&&\/&&& so that it shows 2008/09/002 as the invoice number. The underlying query shows the invoice in this format and shows the invoice and pay amounts as proper currencies. However, when I open the form the invoice column is blank and the two currency columns are shown as normal numbers with no symbol and a variable number of decimal places.

This was working last Friday when I last printed an invoice but when I go in today it has screwed up. It does the same with all of the other listboxes using the base query. I haven't changed anything between then and now, in fact I haven't even opened the database.

Any ideas, other than a virus or some malevolent alien intent on ruining my day?

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[Remou]

Have you checked out the set-up for regional and language options in the control panel?

 

[PeteJohnston]

I have the standard UK setup with £ as the currency symbol. I've just checked and it is Okay. Running the underlying queries on their own shows the correct currency symbol so I don't think it has anything to do with the local settings.

Every Access database on my machine seems to have developed the same problem.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[Pampers]

Eh, maybe office service pack3???

Pampers
Keeping it simple can be complicated

 

[Remou]

Good point, Pampers.

Here is the link:

http://support.microsoft.com/default.aspx/kb/945674

 

[RoyVidar]

I've found that when working with lists and combos, to be sure formatting "sticks", one might need to do the formatting in the query, i e - converting the numbers and dates to strings in the query through the format function.

SELECT ID, Format(TheInvoiceHeader, "&&&&\/&&\/&&&") As TheFormattedInvoiceHeader, ...

For Right alignement, one might then use something like this

http://lebans.com/justicombo.htm

too.

But sometimes it seems strange results in list may also stem from mismatch between the actual number of columns returned by the query and what is specified in the list properties (Column Count).

Roy-Vidar

 

[PeteJohnston]

Thing is, they all worked perfectly for 5 years before Friday. For that reason I don't think it is a code problem.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[PeteJohnston]

Sorry, I meant to say as well that I already have SP3 applied.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[TheAceMan1]

How are ya PeteJohnston . . .

As a test, use the [blue]form wizard[/blue] to instantiate a new form based on the query and let us know the results of expected formatting.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Pampers]

See what can go wrong with comboboxes with SP3 at:

http://allenbrowne.com/bug-Access2003SP3.html

You could try (test) to remove the formatting in the table and put in the form (front end).

Pampers
Keeping it simple can be complicated

 

[PeteJohnston]

I checked up what has been said about SP3 and it sounds familiar. So I removed MS Office altogether and re-installed it from the original disk. Then, with no SP3 and baited breath, I opened the database and.....

It worked!

Thanks guys. What is still confusing me is that I thought I installed SP3 quite a while ago - maybe even last year?

Ah well, no SP3 for me then.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)