Query By Form Parameter Rejected

Robot2016

New Member
Joined
Feb 3, 2016
Messages
9
I have an Access 2016 database containing names, email addresses and dates of hirers together with how many adults and children there were in the party (0-99). I use a form to enable the user to search by different criteria. All works well with the exception of the criteria for "Children" or "No Children". The field concerned holds a number value equating to the number of children in the party. The form has a combo box posing the question "With Children? Yes or No". If the User clicks "No", I put a "0" in an unbound control on the form. If the user click "Yes", I put ">0" in the unbound control. The form passes it's data to a Query which works fine when "No" has been selected but returns no records when "Yes" has been selected. I have tried formatting the field to Number and to Text but it makes no difference. Can anyone help?
Thank you.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Welcome to the Board!

Try:
Code:
"<>0"

Some programs treat TRUE as 1 while others treat it as -1. Access treats it as -1.
 
Upvote 0
Welcome to the Board!

Try:
Code:
"<>0"

Some programs treat TRUE as 1 while others treat it as -1. Access treats it as -1.

Hi Joe4,
Thanks for the welcome. I tried
Code:
"<>0"
and it was rejected with invalid syntax.
 
Upvote 0
Since it is a number, you won't actually have the double-quotes around them (I was just copying the way you were writing it here):
If the user click "Yes", I put ">0" in the unbound control.
So you will actually want to physically type it as:
<>0
 
Upvote 0
Remove the quotation marks from your criteria, ie. <>0

EDIT: Joe types faster. :)
 
Last edited:
Upvote 0
Since it is a number, you won't actually have the double-quotes around them (I was just copying the way you were writing it here):

So you will actually want to physically type it as:
<>0

Yes, that is what I tried.
 
Upvote 0
Please post your VBA code and/or SQL Query where you are trying to do this so we can see what you have.
 
Upvote 0
In the formula, I believe you will need the double-quotes, i.e.
Code:
[COLOR=#333333]IIF([txtChildren]="Yes", ">0", ">=0")[/COLOR]

But a few other questions:
- What is the name of this field and what is the name of the Form it is found on?
- Please post the SQL code of your query that uses this parameter. You can do that by switching your query to SQL View and copying and pasting the code here.
 
Upvote 0
Hi Joe4,
I've got round the problem this morning by creating a calculated field in the table to determine whether children were in the party or not. The calculation is exactly the same logic and it works perfectly. The original problem with the form looks like an inability to pass the symbols "<>" to the query.
Thanks for your help!
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top