Help get this topic noticed by sharing it on Twitter, Facebook, or email.
I’m confused and frustrated

access 2010 search fields as formatted

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
1 person has
this question
+1
Reply
  • Hello Steven,

    I do apologize for the frustration that you're facing. I have contacted the editor on this title, and someone should respond to this thread shortly concerning your question.

    Best regards,
    Paul Fichera
    Customer Service Representative
    O'Reilly Media
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited

  • Thanks for your prompt reply, however it could just me being stupid. It has been known......
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned

  • Hi Steven,

    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.

    Best,
    Matthew
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned

  • I’m Happy!
    Matthew,

    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!

    Rgds,

    Steve Davis
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned