Filter number range causing runtime rror in search form

  • Thread starter Thread starter Billp
  • Start date Start date
B

Billp

Hi,

If I run the following
'Number example.
If Not IsNull(Me.txtPCode) Then
strWhere = strWhere & "([Address5] >= " & Me.txtPCode & " ) AND "
End If

'Number field example.
If Not IsNull(Me.txtPCodeEnd) Then 'Less than the next.
strWhere = strWhere & "([Address5] <= " & Me.txtPCodeEnd & ") AND "
End If
I get a runtime "You Cancelled the previous operation.

However if I replace with
'Text field example. Use Like to find anywhere in the field.
'If Not IsNull(Me.txtPCode) Then
'strWhere = strWhere & "([Address5] Like ""*" & Me.txtPCode & "*"")
AND "
'End If

all is OK

The field "Address5" is a text field
 
So Address5 is a text field, and if you include the quotes (from the text
field example) it works, but if you use the example for the number field it
doesn't?

Surely that's what you expect.
 
Thanks for the reply.

What I am trying to do is allow the user to select a range of postal Codes.
Say NSW 2100 to Victoria 3100. Only the input is >= 2000 and <= 3000.
Not find all postal codes with 100.
The field Address5 id text - so mmmmmmmmmmmmm I should have more quotes? to
allow it to be text and not a number? - how many more?

Tahnks really apreciating your feedback and assistance.
Regards
Bill

Allen Browne said:
So Address5 is a text field, and if you include the quotes (from the text
field example) it works, but if you use the example for the number field it
doesn't?

Surely that's what you expect.

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

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


Billp said:
Hi,

If I run the following
'Number example.
If Not IsNull(Me.txtPCode) Then
strWhere = strWhere & "([Address5] >= " & Me.txtPCode & " ) AND "
End If

'Number field example.
If Not IsNull(Me.txtPCodeEnd) Then 'Less than the next.
strWhere = strWhere & "([Address5] <= " & Me.txtPCodeEnd & ") AND "
End If
I get a runtime "You Cancelled the previous operation.

However if I replace with
'Text field example. Use Like to find anywhere in the field.
'If Not IsNull(Me.txtPCode) Then
'strWhere = strWhere & "([Address5] Like ""*" & Me.txtPCode & "*"")
AND "
'End If

all is OK

The field "Address5" is a text field
Click to expand...
.
Click to expand...
 
And now I see the error of my ways.
The field is text so that post codes from UK in the form KY114RZ can be
entered as well as 2001 or 30402 etc.
So all that can be done is a like field.

mmmmmmmmmmmmmmmmm
Can't do it that way then for the Salesman to sellect a range.

Thank you.

Regards
Bill


Allen Browne said:
So Address5 is a text field, and if you include the quotes (from the text
field example) it works, but if you use the example for the number field it
doesn't?

Surely that's what you expect.

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

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


Billp said:
Hi,

If I run the following
'Number example.
If Not IsNull(Me.txtPCode) Then
strWhere = strWhere & "([Address5] >= " & Me.txtPCode & " ) AND "
End If

'Number field example.
If Not IsNull(Me.txtPCodeEnd) Then 'Less than the next.
strWhere = strWhere & "([Address5] <= " & Me.txtPCodeEnd & ") AND "
End If
I get a runtime "You Cancelled the previous operation.

However if I replace with
'Text field example. Use Like to find anywhere in the field.
'If Not IsNull(Me.txtPCode) Then
'strWhere = strWhere & "([Address5] Like ""*" & Me.txtPCode & "*"")
AND "
'End If

all is OK

The field "Address5" is a text field
Click to expand...
.
Click to expand...
 
And now I see the error of my ways.
The field is text so that post codes from UK in the form KY114RZ can be
entered as well as 2001 or 30402 etc.
So all that can be done is a like field.

mmmmmmmmmmmmmmmmm
Can't do it that way then for the Salesman to sellect a range.
Click to expand...
You'll have to clarify what kind of ranges you have in mind, and what types of
values are likely to be in your table. If there are numeric postcodes (NZ four
digit, US five-digit zips) you can use (e.g.)

LIKE "2###"

to find all four-digit codes starting with 2, or

LIKE "####" AND >= "2500" AND <= "3500"

to find all in that range; but if your table contains a mix of all different
formats, specifying ranges will get complicated indeed!
 
Back
Top