Query criteria in textbox on form

  • Thread starter Thread starter SBGFF
  • Start date Start date
S

SBGFF

I have a form using text box's to set criteria in a query. I need to be able
to use the criteria, <=6 or >=6 or just a number like 6, the number 6 could
be any number from 0 to 20. If I use just a number it works fine, but when I
use the <= in the textbox I get an error message "The OpenForm action was
canceled". Can someone tell me how to accomplish this with out the error

Thanks Blair
 
You can't do this in a query. You will need to write VBA code.

Omit the criteria from the query. Instead, use a button on the form that has
your text box. In the button's Click event procedure, build a WhereCondition
to work with OpenForm.

The code would look like this:

Private Sub Command8_Click()
Dim strWhere As String
With Me.Text24
If Not IsNull(.Value) Then
strWhere = "[SomeField] " & .Value
End If
End With
DoCmd.OpenForm "Form2", WhereCondition:= strWhere
End Sub

Note that this code assumes there is an operator (such as > or <=) as well
as a number in the text box. You will need to extend the code to check
whether an operator is present.
 
I have a form using text box's to set criteria in a query. I need to be able
to use the criteria, <=6 or >=6 or just a number like 6, the number 6 could
be any number from 0 to 20. If I use just a number it works fine, but when I
use the <= in the textbox I get an error message "The OpenForm action was
canceled". Can someone tell me how to accomplish this with out the error

Thanks Blair
Click to expand...

You cannot pass operators such as <= in a parameter or as part of the
criteria: only actual values.

If you want to trust users to correctly enter operators in a textbox on a
form, you must have a pretty good class of users... but if you do, you'll need
to write VBA code to actually construct the entire SQL string for the query,
concatenating the contents of the textbox in the appropriate part of the WHERE
clause. This leaves you vulnerable to ANY sort of criteria, which might
retrieve no records, all records, or give an error in the query, but that's
the risk you take!
 
Thanks
Blair
Allen Browne said:
You can't do this in a query. You will need to write VBA code.

Omit the criteria from the query. Instead, use a button on the form that
has your text box. In the button's Click event procedure, build a
WhereCondition to work with OpenForm.

The code would look like this:

Private Sub Command8_Click()
Dim strWhere As String
With Me.Text24
If Not IsNull(.Value) Then
strWhere = "[SomeField] " & .Value
End If
End With
DoCmd.OpenForm "Form2", WhereCondition:= strWhere
End Sub

Note that this code assumes there is an operator (such as > or <=) as well
as a number in the text box. You will need to extend the code to check
whether an operator is present.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

SBGFF said:
I have a form using text box's to set criteria in a query. I need to be
able to use the criteria, <=6 or >=6 or just a number like 6, the number 6
could be any number from 0 to 20. If I use just a number it works fine,
but when I use the <= in the textbox I get an error message "The OpenForm
action was canceled". Can someone tell me how to accomplish this with out
the error
Click to expand...
Click to expand...
 
Thanks
Blair
John W. Vinson said:
You cannot pass operators such as <= in a parameter or as part of the
criteria: only actual values.

If you want to trust users to correctly enter operators in a textbox on a
form, you must have a pretty good class of users... but if you do, you'll
need
to write VBA code to actually construct the entire SQL string for the
query,
concatenating the contents of the textbox in the appropriate part of the
WHERE
clause. This leaves you vulnerable to ANY sort of criteria, which might
retrieve no records, all records, or give an error in the query, but
that's
the risk you take!
Click to expand...
 
Hi! again. I am not very knowledgeable about coding. I am a copy and past
programmer and use that term very loosely, so I am hoping you will help me.
Once I get something to work and want something similar I copy and past them
make the changes to make it work. This particular problem I have never run
into till now so I need you to help me with it.

My Query name is [Kittags1], my form is [KitTagCardsCriteria]. The field in
the query I want to use the criteria for is named [KitsSurvived]. The text
box in the form I am using is called [KitSur.]
I hope this will help you help me
Thanks Blair

Allen Browne said:
You can't do this in a query. You will need to write VBA code.

Omit the criteria from the query. Instead, use a button on the form that
has your text box. In the button's Click event procedure, build a
WhereCondition to work with OpenForm.

The code would look like this:

Private Sub Command8_Click()
Dim strWhere As String
With Me.Text24
If Not IsNull(.Value) Then
strWhere = "[SomeField] " & .Value
End If
End With
DoCmd.OpenForm "Form2", WhereCondition:= strWhere
End Sub

Note that this code assumes there is an operator (such as > or <=) as well
as a number in the text box. You will need to extend the code to check
whether an operator is present.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

SBGFF said:
I have a form using text box's to set criteria in a query. I need to be
able to use the criteria, <=6 or >=6 or just a number like 6, the number 6
could be any number from 0 to 20. If I use just a number it works fine,
but when I use the <= in the textbox I get an error message "The OpenForm
action was canceled". Can someone tell me how to accomplish this with out
the error
Click to expand...
Click to expand...
 
The code depends on the data type of the field.

Here's an example using Number fields:
http://allenbrowne.com/casu-15.html
(It opens a report, but the WhereCondition for OpenForm is the same.)

Here's one using Date fields:
http://allenbrowne.com/casu-08.html

Here's some information about how to get the quotes right for Text fields:
http://allenbrowne.com/casu-17.html

Here's a more comprehensive example of how to combine multiple fields of
different types:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

SBGFF said:
Hi! again. I am not very knowledgeable about coding. I am a copy and past
programmer and use that term very loosely, so I am hoping you will help
me. Once I get something to work and want something similar I copy and
past them make the changes to make it work. This particular problem I have
never run into till now so I need you to help me with it.

My Query name is [Kittags1], my form is [KitTagCardsCriteria]. The field
in the query I want to use the criteria for is named [KitsSurvived]. The
text box in the form I am using is called [KitSur.]
I hope this will help you help me
Thanks Blair

Allen Browne said:
You can't do this in a query. You will need to write VBA code.

Omit the criteria from the query. Instead, use a button on the form that
has your text box. In the button's Click event procedure, build a
WhereCondition to work with OpenForm.

The code would look like this:

Private Sub Command8_Click()
Dim strWhere As String
With Me.Text24
If Not IsNull(.Value) Then
strWhere = "[SomeField] " & .Value
End If
End With
DoCmd.OpenForm "Form2", WhereCondition:= strWhere
End Sub

Note that this code assumes there is an operator (such as > or <=) as
well as a number in the text box. You will need to extend the code to
check whether an operator is present.

SBGFF said:
I have a form using text box's to set criteria in a query. I need to be
able to use the criteria, <=6 or >=6 or just a number like 6, the number
6 could be any number from 0 to 20. If I use just a number it works fine,
but when I use the <= in the textbox I get an error message "The OpenForm
action was canceled". Can someone tell me how to accomplish this with out
the error
Click to expand...
Click to expand...
Click to expand...
 
Back
Top