Sandy Sailer
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!
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!
My first instinct is that [DollarAmount] is a text field, not numeric. But you might try;
WHERE [DollarAmount] > val([forms]![frmExcel-Col- 1]![MinAmo unt])
WHERE [DollarAmount] > val([forms]![frmExcel-Col-
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.
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.
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.
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>
what SQL Server driver are you using for your connection to SQL Server
ASKER
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.
ASKER
I'm using SQL Server Native Client 11.0 to connect Access to the SQL Server database.
ASKER
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.
SO WEIRD! Thank you, everybody, for your help and suggestions.
Mark your own comment as the solution.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?