Criteria in query not working from unbound form value

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!
Sandy SailerWebSailer Web DesignAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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, Developing Solutions LLCCommented:
My first instinct is that [DollarAmount] is a text field, not numeric.  But you might try;

WHERE [DollarAmount] > val([forms]![frmExcel-Col-1]![MinAmount])
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Fabrice LambertConsultingCommented:
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 DesignAuthor 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!
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?
Gustav BrockCIOCommented:
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, Developing Solutions LLCCommented:
what SQL Server driver are you using for your connection to SQL Server
Sandy SailerWebSailer Web DesignAuthor 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 DesignAuthor Commented:
I'm using SQL Server Native Client 11.0 to connect Access to the SQL Server database.
Sandy SailerWebSailer Web DesignAuthor 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.
Gustav BrockCIOCommented:
Mark your own comment as the solution.
Sandy SailerWebSailer Web DesignAuthor Commented:
Even though it wasn't really a solution?  :)
Gustav BrockCIOCommented:
It that what made it work, it was the cure. In this special case.
Sandy SailerWebSailer Web DesignAuthor Commented:
It did't work last night, it worked today.  No   this.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.