Criteria in query not working from unbound form value

Sandy Sailer
Sandy Sailer used Ask the Experts™
on
I am building a query in Microsoft Access that is getting it's criteria from a form.  I want it to show all donations where the dollar amount is greater than the number entered into the form field.  The unbound form field is named MinAmount.  In my query, my criteria for the donationAmount field is >[forms]![frmExcel-Col-1]![MinAmount].

When I run the query, I get no results.  When I remove the criteria, it's fine ... I get a ton of results. Yes, there are many donations that are greater than 100, so even with the criteria in place, I should get results.  I've tried making the field a general number, currency, etc.  Nothing seems to matter.  What have I overlooked?  Pulling my hair out by now!  

Thank you for any suggestions and help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
Is the form open when you run the query?

You can test the query - put it into design view and add a column to display the form field.  When you run the query, you will see what it is seeing.  Are you sure that the data type of the amount field is some numeric data type?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
My first instinct is that [DollarAmount] is a text field, not numeric.  But you might try;

WHERE [DollarAmount] > val([forms]![frmExcel-Col-1]![MinAmount])
Distinguished Expert 2017

Commented:
Wouldn't that be WHERE Val([DollarAmount]) > [forms]![frmExcel-Col-1]![MinAmount]) ?

Of course the problem may be that the form control has a format that is making Access think the form field is a string.  In that case, you would use val() on both fields or better still - fix the form control to have a numeric format or no format.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Hmm, to my knowledge, control  types and queries data types arn't related to each other.

since with queries the string delimiters are either a single or a double quote, I don't think the fact that the control is a texBox is the problem.
It pretty much looks like the form is closed when the query is run, so the query recieve a null value, and comparing anything with null is always false.
Sandy SailerWebSailer Web Design

Author

Commented:
Here's the thing...  The MAIN database is in SQL Server, and donationAmount is a "money" field.  I'm connecting to the database via MS Access, because they all work with Access.  So, I'm creating the front end pieces (form, queries, etc.) in Access, which is connecting to the SQL Server database.  I probably should have mentioned that earlier ... sorry!
Distinguished Expert 2017

Commented:
Hmm, to my knowledge, control  types and queries data types arn't related to each other.
They do.  For example, Access will recognize an unbound control as a date data type if you format it as a date.

What data type does Access show as the type for "money"?  Do you see Currency or Short Text?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Specify the control as a parameter to free Access from guessing what kind of data to expect:

PARAMETERS [Forms]![frmExcel-Col-1]![MinAmount] Currency;
SELECT .. <your current query>

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
what SQL Server driver are you using for your connection to SQL Server
Sandy SailerWebSailer Web Design

Author

Commented:
donationAmount is a currency field in Access, and a money field in SQL Server.  I've already tried making the unbound text box currency format.  That didn't work either.
Sandy SailerWebSailer Web Design

Author

Commented:
I'm using SQL Server Native Client 11.0 to connect Access to the SQL Server database.
Sandy SailerWebSailer Web Design

Author

Commented:
Ugh .... so weird!  I originally formatted the unbound text box as a general number and I SWEAR it didn't work as query criteria.  This morning, it's working.  So, I guess this was the answer all along:  > [forms]![frmExcel-Col-1]![MinAmount]

SO WEIRD!  Thank you, everybody, for your help and suggestions.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Mark your own comment as the solution.
Sandy SailerWebSailer Web Design

Author

Commented:
Even though it wasn't really a solution?  :)
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
It that what made it work, it was the cure. In this special case.
WebSailer Web Design
Commented:
It did't work last night, it worked today.  No   this.

> [forms]![frmExcel-Col-1]![MinAmount]

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial