Prompt user for range of data to display in query

[80proof]

I am learning how to use MS Access 2003 and have one column in my data that has standard numbers ranging from 50,000 to 20,000,000,000. When I open the Query I would like a dialog box to open asking for a bottom value and then a second box to open for a top value, i want the result to display all entries between the two values. I have used the >= and <= and also used "between" and "and" but have not figured out how to make it work for my query. This is the closest I have come to being successful so far:

>=[Greater or Equal to] And <=[Less or Equal to]

This prompts me with the dialog boxes as wanted but doesn't give me every entry between the values I enter. I have roughly 1,000 items that should come up and only about 3 do.

 

[traingamer]

Are the 'standard numbers' defined as numbers in the underlying table or are they defined as 'text' (which sorts quite differently)?
If defined as numbers then

...Between [LowerLimit] And [UpperLimit]...

should work just fine.



Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[80proof]

The number that I am trying to search for values is a currency that stems from an expression consisting of two values from the underlying table:

Market Cap: ([Shares Outstanding]*[Closing])

The result is what I am trying to define an UpperLimit and LowerLimit. For the Criteria in my Query I copied and pasted:

Between [LowerLimit] And [UpperLimit]

as suggested. I entered 50000 as the lower limit and 100000000 as the upper limit and received Market Caps ranging from 14,000 to 20,000,000,000.

any other suggestions? please let me know if there is any other specific information that would help. Thank you.

 

[traingamer]

Hmmm. Try

Market Cap: [b]Val[/b]([Shares Outstanding]*[Closing])

which returns a number - it still sounds like you're getting a string of some kind.

As a test of whether or not it's being formatted as a string, sort by Market Cap and see if it's sorted numberically or alphabetically.

 

[80proof]

unfortunately adding 'Val' to the code did not help, it still gives me back the same random results.

The code:

Between [LowerLimit] And [UpperLimit]

works fine if i use it in a different column where the underlying info comes directly from the table.

I don't know exactly what you mean by testing to see if its formatted as a string. I don't see anything in design view when i right click the column and click on properties. When I open the query and sort the column using the A-Z button it numerically sorts the data correctly.

 

[lespaul]

Have you tried:

WHERE [b][Shares Outstanding]*[Closing][/b] BETWEEN [Lower Limit] And  [Upper Limit]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[traingamer]

One way - finally (tested):

WHERE [b]Val([/b][Shares Outstanding]*[Closing][b])[/b] BETWEEN [b]Val([/b][Lower Limit][b])[/b] And  [b]Val([/b][Upper Limit][b])[/b]

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[80proof]

Thank you all for your help, after days of trying to figure this out I found this website and you all were able to help me. Within an hour of posting my question I had enough feedback to fix my problem. The end result which worked for me ended up being:

Between Val(LowerLimit) And Val(UpperLimit)

the Where wouldn't work for some reason, but now the application functions just as I wanted it to. Thanks again everyone for your help!