search criteria - VBA

  • Thread starter Thread starter dina
  • Start date Start date
D

dina

Hi,

I have a criteria search page where I have about 10 or
eleven data items that a user may search by. I need the
search to be use the AND boolean string.

How do I write it so that the result is that it returns
for example, the person or people with the last
name 'Smith' who ordered between 01-01-03 and 09-01-03?
Right now it is returning those which are Smith or
ordered between the chosen dates.


If Not txtLastName = "" Then

If strWHERE <> "" Then strWHERE = strWHERE & " and "

strWHERE = strWHERE & "tblContacts.LastName LIKE '*" &
txtLastName & "*'"

' MsgBox (strWHERE)

End If



If Not ([txtOrderBeginDate]) Or [txtOrderEndDate] = ""
Then

strWHERE = "[OrderDate] Between #" &
[txtOrderBeginDate] _

& "# And #" & [txtOrderEndDate] & "#"



End If
 
The unbound text boxes are probably Null rather than a zero-length string.
Try something like this:

Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtOrderBeginDate) Then
If Not IsNull(Me.txtOrderEndDate) Then 'End, no begin.
strWhere = "OrderDate <= " & _
Format(Me.txtOrderEndDate, strcJetDate)
End If
Else
If IsNull(Me.txtOrderEndDate) Then 'Begin, no end.
strWhere = "OrderDate >= " & _
Format(Me.txtOrderBeginDate, strcJetDate)
Else 'Both begin and end.
strWhere = "OrderDate Between " & _
Format(Me.txtOrderBeginDate, strcJetDate) & _
" And " & Format(Me.txtOrderEndDate, strcJetDate)
End If
End If

If Not IsNull(Me.txtLastName) Then
If Len(strWhere) = 0 Then
strWhere = "(" & strWhere & ") AND "
Else
strWhere = strWhere & "(LastName = """ & Me.txtLastName & """)"
End If
End If

If Len(strWhere) = 0 Then
'No criteria: show all records
Me.FilterOn = False
Else
Me.Filter = strWhere
Me.FilterOn = True
End If
 
Hi, Whn I copy/paste what you wrote I get back the
following error message:

Runtime error 3075:

Invalid use of a '.', '!' or an (). ijn expression
OrderDate between #01/01/2003# And #09/01/2003#
(LastName="Smith")'

And when I tried to search only on 'Smith' I got Syntax
error (missing operator) in query expression in () AND '.

I do not understand.

d-
-----Original Message-----
The unbound text boxes are probably Null rather than a zero-length string.
Try something like this:

Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtOrderBeginDate) Then
If Not IsNull(Me.txtOrderEndDate) Then 'End, no begin.
strWhere = "OrderDate <= " & _
Format(Me.txtOrderEndDate, strcJetDate)
End If
Else
If IsNull(Me.txtOrderEndDate) Then 'Begin, no end.
strWhere = "OrderDate >= " & _
Format(Me.txtOrderBeginDate, strcJetDate)
Else 'Both begin and end.
strWhere = "OrderDate Between " & _
Format(Me.txtOrderBeginDate, strcJetDate) & _
" And " & Format(Me.txtOrderEndDate, strcJetDate)
End If
End If

If Not IsNull(Me.txtLastName) Then
If Len(strWhere) = 0 Then
strWhere = "(" & strWhere & ") AND "
Else
strWhere = strWhere & "(LastName = """ & Me.txtLastName & """)"
End If
End If

If Len(strWhere) = 0 Then
'No criteria: show all records
Me.FilterOn = False
Else
Me.Filter = strWhere
Me.FilterOn = True
End If

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


dina said:
Hi,

I have a criteria search page where I have about 10 or
eleven data items that a user may search by. I need the
search to be use the AND boolean string.

How do I write it so that the result is that it returns
for example, the person or people with the last
name 'Smith' who ordered between 01-01-03 and 09-01- 03?
Right now it is returning those which are Smith or
ordered between the chosen dates.


If Not txtLastName = "" Then

If strWHERE <> "" Then strWHERE = strWHERE & " and "

strWHERE = strWHERE & "tblContacts.LastName LIKE '*" &
txtLastName & "*'"

' MsgBox (strWHERE)

End If



If Not ([txtOrderBeginDate]) Or [txtOrderEndDate] = ""
Then

strWHERE = "[OrderDate] Between #" &
[txtOrderBeginDate] _

& "# And #" & [txtOrderEndDate] & "#"



End If
Click to expand...


.
Click to expand...
 
It's missing an "AND" in this line:

strWhere = strWhere & " AND (LastName = """ & Me.txtLastName & """)"

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


dina said:
Hi, Whn I copy/paste what you wrote I get back the
following error message:

Runtime error 3075:

Invalid use of a '.', '!' or an (). ijn expression
OrderDate between #01/01/2003# And #09/01/2003#
(LastName="Smith")'

And when I tried to search only on 'Smith' I got Syntax
error (missing operator) in query expression in () AND '.

I do not understand.

d-
-----Original Message-----
The unbound text boxes are probably Null rather than a zero-length string.
Try something like this:

Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtOrderBeginDate) Then
If Not IsNull(Me.txtOrderEndDate) Then 'End, no begin.
strWhere = "OrderDate <= " & _
Format(Me.txtOrderEndDate, strcJetDate)
End If
Else
If IsNull(Me.txtOrderEndDate) Then 'Begin, no end.
strWhere = "OrderDate >= " & _
Format(Me.txtOrderBeginDate, strcJetDate)
Else 'Both begin and end.
strWhere = "OrderDate Between " & _
Format(Me.txtOrderBeginDate, strcJetDate) & _
" And " & Format(Me.txtOrderEndDate, strcJetDate)
End If
End If

If Not IsNull(Me.txtLastName) Then
If Len(strWhere) = 0 Then
strWhere = "(" & strWhere & ") AND "
Else
strWhere = strWhere & "(LastName = """ & Me.txtLastName & """)"
End If
End If

If Len(strWhere) = 0 Then
'No criteria: show all records
Me.FilterOn = False
Else
Me.Filter = strWhere
Me.FilterOn = True
End If

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


dina said:
Hi,

I have a criteria search page where I have about 10 or
eleven data items that a user may search by. I need the
search to be use the AND boolean string.

How do I write it so that the result is that it returns
for example, the person or people with the last
name 'Smith' who ordered between 01-01-03 and 09-01- 03?
Right now it is returning those which are Smith or
ordered between the chosen dates.


If Not txtLastName = "" Then

If strWHERE <> "" Then strWHERE = strWHERE & " and "

strWHERE = strWHERE & "tblContacts.LastName LIKE '*" &
txtLastName & "*'"

' MsgBox (strWHERE)

End If



If Not ([txtOrderBeginDate]) Or [txtOrderEndDate] = ""
Then

strWHERE = "[OrderDate] Between #" &
[txtOrderBeginDate] _

& "# And #" & [txtOrderEndDate] & "#"



End If
Click to expand...


.
Click to expand...
Click to expand...
 
Hi,

I now get the error message, "You can't assign a value to
this object" when I type in only a name. When I give a
name and an order date range, I get a blank page, even
though there is at least one record which should be
returned. I'm puzzled.

on the starred line:
If Len(strWhere) = 0 Then
'No criteria: show all records
Me.FilterOn = False
Else
* Me.Filter = strWhere
Me.FilterOn = True
End If

d-
-----Original Message-----
It's missing an "AND" in this line:

strWhere = strWhere & " AND (LastName = """ & Me.txtLastName & """)"

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


dina said:
Hi, Whn I copy/paste what you wrote I get back the
following error message:

Runtime error 3075:

Invalid use of a '.', '!' or an (). ijn expression
OrderDate between #01/01/2003# And #09/01/2003#
(LastName="Smith")'

And when I tried to search only on 'Smith' I got Syntax
error (missing operator) in query expression in () AND '.

I do not understand.

d-
-----Original Message-----
The unbound text boxes are probably Null rather than a zero-length string.
Try something like this:

Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtOrderBeginDate) Then
If Not IsNull(Me.txtOrderEndDate) Then 'End, no begin.
strWhere = "OrderDate <= " & _
Format(Me.txtOrderEndDate, strcJetDate)
End If
Else
If IsNull(Me.txtOrderEndDate) Then 'Begin,
Click to expand...
no
end.
strWhere = "OrderDate >= " & _
Format(Me.txtOrderBeginDate, strcJetDate)
Else 'Both begin and end.
strWhere = "OrderDate Between " & _
Format(Me.txtOrderBeginDate, strcJetDate) & _
" And " & Format(Me.txtOrderEndDate, strcJetDate)
End If
End If

If Not IsNull(Me.txtLastName) Then
If Len(strWhere) = 0 Then
strWhere = "(" & strWhere & ") AND "
Else
strWhere = strWhere & "(LastName = """ & Me.txtLastName & """)"
End If
End If

If Len(strWhere) = 0 Then
'No criteria: show all records
Me.FilterOn = False
Else
Me.Filter = strWhere
Me.FilterOn = True
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

Hi,

I have a criteria search page where I have about 10 or
eleven data items that a user may search by. I need the
search to be use the AND boolean string.

How do I write it so that the result is that it returns
for example, the person or people with the last
name 'Smith' who ordered between 01-01-03 and 09-
Click to expand...
01-
03?
Right now it is returning those which are Smith or
ordered between the chosen dates.


If Not txtLastName = "" Then

If strWHERE <> "" Then strWHERE = strWHERE & " and "

strWHERE = strWHERE & "tblContacts.LastName LIKE '*" &
txtLastName & "*'"

' MsgBox (strWHERE)

End If



If Not ([txtOrderBeginDate]) Or [txtOrderEndDate] = ""
Then

strWHERE = "[OrderDate] Between #" &
[txtOrderBeginDate] _

& "# And #" & [txtOrderEndDate] & "#"



End If








.
Click to expand...
Click to expand...


.
Click to expand...
 
Hi Dina

In the newsgroups, our goal is not to write and debug your code for you, but
to provide you with an example of how to approach it.

The idea is to create a string that you can use as the WhereCondition for
OpenReport. The string needs to read like the WHERE condition of a query. To
see the exact string you need, you could create a query that does an example
for you, switch it to SQL View (View menu), and look as the bit that follows
"WHERE".

From there, you can work backwards to achieve what you need in code. That
involves:
- testing if the control IsNull() so you decide whether to use it this time
or not;
- concatenating the various values together with AND.

Hopefully that provides the information you need to not only be able to do
this for this occasion, but also so you have learnt a skill that you can
reuse for other reports as well. This is something you very often need to
do.

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


dina said:
Hi,

I now get the error message, "You can't assign a value to
this object" when I type in only a name. When I give a
name and an order date range, I get a blank page, even
though there is at least one record which should be
returned. I'm puzzled.

on the starred line:
If Len(strWhere) = 0 Then
'No criteria: show all records
Me.FilterOn = False
Else
* Me.Filter = strWhere
Me.FilterOn = True
End If

d-
-----Original Message-----
It's missing an "AND" in this line:

strWhere = strWhere & " AND (LastName = """ & Me.txtLastName & """)"

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


dina said:
Hi, Whn I copy/paste what you wrote I get back the
following error message:

Runtime error 3075:

Invalid use of a '.', '!' or an (). ijn expression
OrderDate between #01/01/2003# And #09/01/2003#
(LastName="Smith")'

And when I tried to search only on 'Smith' I got Syntax
error (missing operator) in query expression in () AND '.

I do not understand.

d-
-----Original Message-----
The unbound text boxes are probably Null rather than a
zero-length string.
Try something like this:

Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtOrderBeginDate) Then
If Not IsNull(Me.txtOrderEndDate) Then 'End, no
begin.
strWhere = "OrderDate <= " & _
Format(Me.txtOrderEndDate, strcJetDate)
End If
Else
If IsNull(Me.txtOrderEndDate) Then 'Begin, no
end.
strWhere = "OrderDate >= " & _
Format(Me.txtOrderBeginDate, strcJetDate)
Else 'Both begin and end.
strWhere = "OrderDate Between " & _
Format(Me.txtOrderBeginDate, strcJetDate) & _
" And " & Format(Me.txtOrderEndDate,
strcJetDate)
End If
End If

If Not IsNull(Me.txtLastName) Then
If Len(strWhere) = 0 Then
strWhere = "(" & strWhere & ") AND "
Else
strWhere = strWhere & "(LastName = """ &
Me.txtLastName & """)"
End If
End If

If Len(strWhere) = 0 Then
'No criteria: show all records
Me.FilterOn = False
Else
Me.Filter = strWhere
Me.FilterOn = True
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

Hi,

I have a criteria search page where I have about 10 or
eleven data items that a user may search by. I need
the
search to be use the AND boolean string.

How do I write it so that the result is that it returns
for example, the person or people with the last
name 'Smith' who ordered between 01-01-03 and 09- 01-
03?
Right now it is returning those which are Smith or
ordered between the chosen dates.


If Not txtLastName = "" Then

If strWHERE <> "" Then strWHERE = strWHERE & " and "

strWHERE = strWHERE & "tblContacts.LastName
LIKE '*" &
txtLastName & "*'"

' MsgBox (strWHERE)

End If



If Not ([txtOrderBeginDate]) Or [txtOrderEndDate]
= ""
Then

strWHERE = "[OrderDate] Between #" &
[txtOrderBeginDate] _

& "# And #" & [txtOrderEndDate] & "#"



End If








.
Click to expand...


.
Click to expand...
Click to expand...
 
Hi,
Thank you for your help. I have been searching high and
low for examples on the microsoft sight and books for
informaiton on how to properly write AND/OR boolean
operators strings for VBA. I haven't found any as of
yet.

I do not understand how to create a true string that
would let a user do the following:

if choose ONLY criteria_A give result
if choose ONLY criteria_B then give result;
if choose both criteria_A AND criteria_B give result
if choose criteria_A or criteria_B give result group
(this would be largest group)

If you know of a site which explains this, it would be
much appreciated.

-dina
-----Original Message-----
Hi Dina

In the newsgroups, our goal is not to write and debug your code for you, but
to provide you with an example of how to approach it.

The idea is to create a string that you can use as the WhereCondition for
OpenReport. The string needs to read like the WHERE condition of a query. To
see the exact string you need, you could create a query that does an example
for you, switch it to SQL View (View menu), and look as the bit that follows
"WHERE".

From there, you can work backwards to achieve what you need in code. That
involves:
- testing if the control IsNull() so you decide whether to use it this time
or not;
- concatenating the various values together with AND.

Hopefully that provides the information you need to not only be able to do
this for this occasion, but also so you have learnt a skill that you can
reuse for other reports as well. This is something you very often need to
do.

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


dina said:
Hi,

I now get the error message, "You can't assign a value to
this object" when I type in only a name. When I give a
name and an order date range, I get a blank page, even
though there is at least one record which should be
returned. I'm puzzled.

on the starred line:
If Len(strWhere) = 0 Then
'No criteria: show all records
Me.FilterOn = False
Else
* Me.Filter = strWhere
Me.FilterOn = True
End If

d-
-----Original Message-----
It's missing an "AND" in this line:

strWhere = strWhere & " AND (LastName = """ & Me.txtLastName & """)"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

Hi, Whn I copy/paste what you wrote I get back the
following error message:

Runtime error 3075:

Invalid use of a '.', '!' or an (). ijn expression
OrderDate between #01/01/2003# And #09/01/2003#
(LastName="Smith")'

And when I tried to search only on 'Smith' I got Syntax
error (missing operator) in query expression in () AND '.

I do not understand.

d-
-----Original Message-----
The unbound text boxes are probably Null rather than a
zero-length string.
Try something like this:

Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtOrderBeginDate) Then
If Not IsNull(Me.txtOrderEndDate) Then 'End, no
begin.
strWhere = "OrderDate <= " & _
Format(Me.txtOrderEndDate, strcJetDate)
End If
Else
If IsNull(Me.txtOrderEndDate)
Click to expand...
Then 'Begin,
no
end.
strWhere = "OrderDate >= " & _
Format(Me.txtOrderBeginDate, strcJetDate)
Else 'Both begin and end.
strWhere = "OrderDate Between " & _
Format(Me.txtOrderBeginDate,
Click to expand...
strcJetDate)
& _
" And " & Format(Me.txtOrderEndDate,
strcJetDate)
End If
End If

If Not IsNull(Me.txtLastName) Then
If Len(strWhere) = 0 Then
strWhere = "(" & strWhere & ") AND "
Else
strWhere = strWhere & "(LastName = """ &
Me.txtLastName & """)"
End If
End If

If Len(strWhere) = 0 Then
'No criteria: show all records
Me.FilterOn = False
Else
Me.Filter = strWhere
Me.FilterOn = True
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

Hi,

I have a criteria search page where I have about
Click to expand...
10
or
eleven data items that a user may search by. I need
the
search to be use the AND boolean string.

How do I write it so that the result is that it returns
for example, the person or people with the last
name 'Smith' who ordered between 01-01-03 and
Click to expand...
09-
01-
03?
Right now it is returning those which are Smith or
ordered between the chosen dates.


If Not txtLastName = "" Then

If strWHERE <> "" Then strWHERE = strWHERE & " and "

strWHERE = strWHERE & "tblContacts.LastName
LIKE '*" &
txtLastName & "*'"

' MsgBox (strWHERE)

End If



If Not ([txtOrderBeginDate]) Or [txtOrderEndDate]
= ""
Then

strWHERE = "[OrderDate] Between #" &
[txtOrderBeginDate] _

& "# And #" & [txtOrderEndDate] & "#"



End If








.



.
Click to expand...
Click to expand...


.
Click to expand...
 
If the user enters only a name, you need to build a string such as:
"LastName = ""Smith"""

If the user enters only the dates, you need to build a string such as:
"OrderDate Between #1/1/2002# And #12/31/2002#"

If the user enters both, you need to build a string such as:
"(LastName = ""Smith"") AND (OrderDate Between #1/1/2002# And
#12/31/2002#)"

Use the IsNull() function to see if the user entered these values.

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


dina said:
Hi,
Thank you for your help. I have been searching high and
low for examples on the microsoft sight and books for
informaiton on how to properly write AND/OR boolean
operators strings for VBA. I haven't found any as of
yet.

I do not understand how to create a true string that
would let a user do the following:

if choose ONLY criteria_A give result
if choose ONLY criteria_B then give result;
if choose both criteria_A AND criteria_B give result
if choose criteria_A or criteria_B give result group
(this would be largest group)

If you know of a site which explains this, it would be
much appreciated.

-dina
-----Original Message-----
Hi Dina

In the newsgroups, our goal is not to write and debug your code for you, but
to provide you with an example of how to approach it.

The idea is to create a string that you can use as the WhereCondition for
OpenReport. The string needs to read like the WHERE condition of a query. To
see the exact string you need, you could create a query that does an example
for you, switch it to SQL View (View menu), and look as the bit that follows
"WHERE".

From there, you can work backwards to achieve what you need in code. That
involves:
- testing if the control IsNull() so you decide whether to use it this time
or not;
- concatenating the various values together with AND.

Hopefully that provides the information you need to not only be able to do
this for this occasion, but also so you have learnt a skill that you can
reuse for other reports as well. This is something you very often need to
do.

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


dina said:
Hi,

I now get the error message, "You can't assign a value to
this object" when I type in only a name. When I give a
name and an order date range, I get a blank page, even
though there is at least one record which should be
returned. I'm puzzled.

on the starred line:
If Len(strWhere) = 0 Then
'No criteria: show all records
Me.FilterOn = False
Else
* Me.Filter = strWhere
Me.FilterOn = True
End If

d-
-----Original Message-----
It's missing an "AND" in this line:

strWhere = strWhere & " AND (LastName = """ &
Me.txtLastName & """)"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

Hi, Whn I copy/paste what you wrote I get back the
following error message:

Runtime error 3075:

Invalid use of a '.', '!' or an (). ijn expression
OrderDate between #01/01/2003# And #09/01/2003#
(LastName="Smith")'

And when I tried to search only on 'Smith' I got Syntax
error (missing operator) in query expression in ()
AND '.

I do not understand.

d-
-----Original Message-----
The unbound text boxes are probably Null rather than a
zero-length string.
Try something like this:

Dim strWhere As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If IsNull(Me.txtOrderBeginDate) Then
If Not IsNull(Me.txtOrderEndDate) Then 'End, no
begin.
strWhere = "OrderDate <= " & _
Format(Me.txtOrderEndDate, strcJetDate)
End If
Else
If IsNull(Me.txtOrderEndDate) Then 'Begin,
no
end.
strWhere = "OrderDate >= " & _
Format(Me.txtOrderBeginDate, strcJetDate)
Else 'Both begin and end.
strWhere = "OrderDate Between " & _
Format(Me.txtOrderBeginDate, strcJetDate)
& _
" And " & Format(Me.txtOrderEndDate,
strcJetDate)
End If
End If

If Not IsNull(Me.txtLastName) Then
If Len(strWhere) = 0 Then
strWhere = "(" & strWhere & ") AND "
Else
strWhere = strWhere & "(LastName = """ &
Me.txtLastName & """)"
End If
End If

If Len(strWhere) = 0 Then
'No criteria: show all records
Me.FilterOn = False
Else
Me.Filter = strWhere
Me.FilterOn = True
End If

--
Allen Browne - Microsoft MVP. Perth, Western
Australia.
Tips for Access users -
http://allenbrowne.com/tips.html

Hi,

I have a criteria search page where I have about 10
or
eleven data items that a user may search by. I need
the
search to be use the AND boolean string.

How do I write it so that the result is that it
returns
for example, the person or people with the last
name 'Smith' who ordered between 01-01-03 and 09-
01-
03?
Right now it is returning those which are Smith or
ordered between the chosen dates.


If Not txtLastName = "" Then

If strWHERE <> "" Then strWHERE = strWHERE & "
and "

strWHERE = strWHERE & "tblContacts.LastName
LIKE '*" &
txtLastName & "*'"

' MsgBox (strWHERE)

End If



If Not ([txtOrderBeginDate]) Or [txtOrderEndDate]
= ""
Then

strWHERE = "[OrderDate] Between #" &
[txtOrderBeginDate] _

& "# And #" & [txtOrderEndDate] & "#"
End If
Click to expand...
Click to expand...
Click to expand...
 
Back
Top