Link to home
Start Free TrialLog in
Avatar of Sandy Sailer
Sandy SailerFlag for United States of America

asked on

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!
Avatar of PatHartman
PatHartman
Flag of United States of America image

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?
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.
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.
Avatar of Sandy Sailer

ASKER

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?
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

what SQL Server driver are you using for your connection to SQL Server
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.
I'm using SQL Server Native Client 11.0 to connect Access to the SQL Server database.
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.
Mark your own comment as the solution.
Even though it wasn't really a solution?  :)
It that what made it work, it was the cure. In this special case.
ASKER CERTIFIED SOLUTION
Avatar of Sandy Sailer
Sandy Sailer
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial