I am confused by the explanation of 'search fields as formatted' on p 106 of the Access 2010 Missing Manual. As I understand the explanation, if I were searching for the value £7.00 I should be able to find it by entering 7 as the search argument and de-selecting the option 'search fields as formatted'. However, I cannot get this to work. See Access format issue 1 (attached). I then followed the 'tip. and searched on a single field but that did not work either. See Access format issue 2 (attached).
the only way I can find the £7.00 entry is by using £7.00 as the search argument and selecting 'search fields as formatted'. I have looked at the Microsoft 'help' which is not very helpful (as usual). Either I am doing something stupid or the book is not clear. Can you help as this is doing my head in!
Regards, Steve Davis
Help get this topic noticed by sharing it on Twitter, Facebook, or email.
If you have a Currency field, you should also be able to search for "7" and find "$7.00" if you turn off the "Search Field as Formatted" checkbox. I just tried it successfully (see image), and I also tried it after changing the currency symbol to Euros (it still worked). Have you changed the formatting of the field to get the pound symbol, or is it just part of your computer's regional settings?
You can of course solve this problem by choosing to search "Any Part of Field" in the Match box. In this case, Access will find just the "7" regardless of whether you search the formatted values or not. But I suspect you already know that.
Lastly, there's a strange issue that can cause Access not to find your value if you start in edit mode in the field that contains that value. But I don't think that's the problem you're experiencing.
Many thanks for your prompt and helpful reply; it is much appreciated. As you surmised, the problem turned out to be the way in which I had formatted the 'PurchasePrice' field. I have now corrected that and everything works as it should..... however, I doubt I would have found the root cause without your help.
A couple of other points:
1. If you download the bobblehead database from the web site onto a PC setup with regional settings for the UK you get the same problem. See attached screen shot. I assume this is because the format is set as $#,##0.00;($#,##0.00)
2. Having spent some time trying different 'find' options I suspect it would be good practice always to search on xx.xx regardless of whether a price/number ends in .00 or not.
Once again, many thanks. Great book on a difficult subject!