"#" for Dates; Equivalent for numbers or currency?

Posted on 2012-08-22
Last Modified: 2012-08-22
I have a query that pulls thousands of transaction records.  I'm trying to restrict the set to values below a certain threshold, say $10,000.  when I hard code it (ie. under filter put <=10000) it works.  But when I try to add a criteria parameter (ie. <=[ceiling]), it produces incorrect results.

I'm guessing I need to put something around the brackets that tells Access I'm looking for a number or currency, like using the "#" sign with dates.

Does anyone know what character I should use or, if I'm approaching it incorrectly, a better way to do it?
Question by:BBlu
    LVL 61

    Accepted Solution

    Numeric criteria use no delimiters.

    How are you entering your criteria, and what are the incorrect results?  

    You could try this:

    <= Val([Ceiling])
    LVL 65

    Expert Comment

    by:Jim Horn
    In Access, numbers / currency do not require anything for delimeters, such as #'s for dates and "s for text.
    LVL 65

    Expert Comment

    by:Jim Horn
    >that tells Access I'm looking for a number or currency,
    Access will evaluate it based on whatever the textbox's Forat property is.
    LVL 65

    Expert Comment

    by:Jim Horn
    er, I mean Format.

    Author Closing Comment


    Author Comment

    Sorry..need some additional color around "Perfect".  The Val() function worked.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now