compound query

M

Mark

Hi,
I'm using an unbound form that contains 15 fields from
which the User can enter data to search for records. The
form has fields formatted as text, number, and date/time.
A command button 'On Click' event triggers the query and
returns the records. Usually, a User will not select more
than five fields at the most. My question is; "What is the
proper syntax for when the User does not query on fields
that contain numbers or date/time entries to keep the
query from erroring out?" Often a User will query only on
the END_DATE1 or CONTRACTR1 fields. Alternatively, would
there be another option. A portion of the code is posted
below:


If Not IsNull([Forms]![Form1]![PO_NUMBER]) Then
strWhere = "PO_NUMBER = " & [Forms]![Form1]!
[PO_NUMBER1]
Else
strWhere = "DOLLR_AMT = " & ([Forms]![Form1]!
[DOLLR_AMT1])
strWhere = strWhere & " AND CONTRACTR Like ""*" &
([Forms]![Form1]![CONTRACTR1]) & "*"""
strWhere = strWhere & " AND ORIGINATR Like ""*" &
([Forms]![Form1]![ORIGINATR1]) & "*"""
strWhere = strWhere & " AND STRT_DATE = #" &
([Forms]![Form1]![STRT_DATE1]) & "#"
strWhere = strWhere & " AND END_DATE = #" &
([Forms]![Form1]![END_DATE1]) & "#"
strWhere = strWhere & " AND DESCRIPTN Like ""*" &
([Forms]![Form1]![DESCRIPTN1]) & "*"""
End If

DoCmd.OpenForm "FindContractForm", WhereCondition:=strWhere


Any help would be greatly appreciated. Thanks.
Mark
 
M

Mark

Thanks, Duane, I appreciate your help, but I don't
understand what the purpose of this statement is->
strWhere = " 1 = 1 "
Mark
-----Original Message-----
I start with
strWhere = " 1 = 1 "
Then whenever I need to add to this, it will always be
strWhere = strWhere & " And [SomeField] = "
I would check each control for null and if not null then add to strWhere.
--
Duane Hookom
MS Access MVP


Mark said:
Hi,
I'm using an unbound form that contains 15 fields from
which the User can enter data to search for records. The
form has fields formatted as text, number, and date/time.
A command button 'On Click' event triggers the query and
returns the records. Usually, a User will not select more
than five fields at the most. My question is; "What is the
proper syntax for when the User does not query on fields
that contain numbers or date/time entries to keep the
query from erroring out?" Often a User will query only on
the END_DATE1 or CONTRACTR1 fields. Alternatively, would
there be another option. A portion of the code is posted
below:


If Not IsNull([Forms]![Form1]![PO_NUMBER]) Then
strWhere = "PO_NUMBER = " & [Forms]![Form1]!
[PO_NUMBER1]
Else
strWhere = "DOLLR_AMT = " & ([Forms]![Form1]!
[DOLLR_AMT1])
strWhere = strWhere & " AND CONTRACTR Like ""*" &
([Forms]![Form1]![CONTRACTR1]) & "*"""
strWhere = strWhere & " AND ORIGINATR Like ""*" &
([Forms]![Form1]![ORIGINATR1]) & "*"""
strWhere = strWhere & " AND STRT_DATE = #" &
([Forms]![Form1]![STRT_DATE1]) & "#"
strWhere = strWhere & " AND END_DATE = #" &
([Forms]![Form1]![END_DATE1]) & "#"
strWhere = strWhere & " AND DESCRIPTN Like ""*" &
([Forms]![Form1]![DESCRIPTN1]) & "*"""
End If

DoCmd.OpenForm "FindContractForm", WhereCondition:=strWhere


Any help would be greatly appreciated. Thanks.
Mark
Click to expand...


.
Click to expand...
 
D

Duane Hookom

Here is how I would set this up
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull([Forms]![Form1]![PO_NUMBER]) Then
strWhere = strWhere & " AND PO_NUMBER = " & [Forms]![Form1]!
[PO_NUMBER1]
End If
If Not IsNull(Forms]![Form1]![DOLLR_AMT1]) Then
strWhere = strWhere & " AND DOLLR_AMT = " &
([Forms]![Form1]![DOLLR_AMT1])
End If
If Not IsNull([Forms]![Form1]![CONTRACTR1]) Then
strWhere = strWhere & " AND CONTRACTR Like ""*" & _
([Forms]![Form1]![CONTRACTR1]) & "*"""
End If
'etc
DoCmd.OpenForm "FindContractForm", WhereCondition:=strWhere

If I don't start with 1-1 then I will have to check in each block of code to
see if I need to use " AND " or not.


--
Duane Hookom
MS Access MVP


Mark said:
Thanks, Duane, I appreciate your help, but I don't
understand what the purpose of this statement is->
strWhere = " 1 = 1 "
Mark
-----Original Message-----
I start with
strWhere = " 1 = 1 "
Then whenever I need to add to this, it will always be
strWhere = strWhere & " And [SomeField] = "
I would check each control for null and if not null then add to strWhere.
--
Duane Hookom
MS Access MVP


Mark said:
Hi,
I'm using an unbound form that contains 15 fields from
which the User can enter data to search for records. The
form has fields formatted as text, number, and date/time.
A command button 'On Click' event triggers the query and
returns the records. Usually, a User will not select more
than five fields at the most. My question is; "What is the
proper syntax for when the User does not query on fields
that contain numbers or date/time entries to keep the
query from erroring out?" Often a User will query only on
the END_DATE1 or CONTRACTR1 fields. Alternatively, would
there be another option. A portion of the code is posted
below:


If Not IsNull([Forms]![Form1]![PO_NUMBER]) Then
strWhere = "PO_NUMBER = " & [Forms]![Form1]!
[PO_NUMBER1]
Else
strWhere = "DOLLR_AMT = " & ([Forms]![Form1]!
[DOLLR_AMT1])
strWhere = strWhere & " AND CONTRACTR Like ""*" &
([Forms]![Form1]![CONTRACTR1]) & "*"""
strWhere = strWhere & " AND ORIGINATR Like ""*" &
([Forms]![Form1]![ORIGINATR1]) & "*"""
strWhere = strWhere & " AND STRT_DATE = #" &
([Forms]![Form1]![STRT_DATE1]) & "#"
strWhere = strWhere & " AND END_DATE = #" &
([Forms]![Form1]![END_DATE1]) & "#"
strWhere = strWhere & " AND DESCRIPTN Like ""*" &
([Forms]![Form1]![DESCRIPTN1]) & "*"""
End If

DoCmd.OpenForm "FindContractForm", WhereCondition:=strWhere


Any help would be greatly appreciated. Thanks.
Mark
Click to expand...


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

Mark

Thanks for explaining that, Duane, I appreciate all your
help and it appears to be working correctly.
Mark
-----Original Message-----
Here is how I would set this up
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull([Forms]![Form1]![PO_NUMBER]) Then
strWhere = strWhere & " AND PO_NUMBER = " & [Forms]![Form1]!
[PO_NUMBER1]
End If
If Not IsNull(Forms]![Form1]![DOLLR_AMT1]) Then
strWhere = strWhere & " AND DOLLR_AMT = " &
([Forms]![Form1]![DOLLR_AMT1])
End If
If Not IsNull([Forms]![Form1]![CONTRACTR1]) Then
strWhere = strWhere & " AND CONTRACTR Like ""*" & _
([Forms]![Form1]![CONTRACTR1]) & "*"""
End If
'etc
DoCmd.OpenForm "FindContractForm", WhereCondition:=strWhere

If I don't start with 1-1 then I will have to check in each block of code to
see if I need to use " AND " or not.


--
Duane Hookom
MS Access MVP


Mark said:
Thanks, Duane, I appreciate your help, but I don't
understand what the purpose of this statement is->
strWhere = " 1 = 1 "
Mark
-----Original Message-----
I start with
strWhere = " 1 = 1 "
Then whenever I need to add to this, it will always be
strWhere = strWhere & " And [SomeField] = "
I would check each control for null and if not null
Click to expand...
then
add to strWhere.
--
Duane Hookom
MS Access MVP


Hi,
I'm using an unbound form that contains 15 fields from
which the User can enter data to search for records. The
form has fields formatted as text, number, and date/time.
A command button 'On Click' event triggers the query and
returns the records. Usually, a User will not select more
than five fields at the most. My question is; "What
Click to expand...
is
the
proper syntax for when the User does not query on fields
that contain numbers or date/time entries to keep the
query from erroring out?" Often a User will query
Click to expand...
only
on
the END_DATE1 or CONTRACTR1 fields. Alternatively, would
there be another option. A portion of the code is posted
below:


If Not IsNull([Forms]![Form1]![PO_NUMBER]) Then
strWhere = "PO_NUMBER = " & [Forms]![Form1]!
[PO_NUMBER1]
Else
strWhere = "DOLLR_AMT = " & ([Forms]![Form1]!
[DOLLR_AMT1])
strWhere = strWhere & " AND CONTRACTR
Click to expand...
Like ""*"
&
([Forms]![Form1]![CONTRACTR1]) & "*"""
strWhere = strWhere & " AND ORIGINATR
Click to expand...
Like ""*"
&
([Forms]![Form1]![ORIGINATR1]) & "*"""
strWhere = strWhere & " AND STRT_DATE = #" &
([Forms]![Form1]![STRT_DATE1]) & "#"
strWhere = strWhere & " AND END_DATE = #" &
([Forms]![Form1]![END_DATE1]) & "#"
strWhere = strWhere & " AND DESCRIPTN
Click to expand...
Like ""*"
&
([Forms]![Form1]![DESCRIPTN1]) & "*"""
End If

DoCmd.OpenForm "FindContractForm", WhereCondition:=strWhere


Any help would be greatly appreciated. Thanks.
Mark



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


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query two subforms from 3 combo boxes 3
Using a textbox as a criteria for a query 1
Select AS query problem 3
Date query parameters entered from form not working 1
Passing a variable on a form to a pass-through query 2
Creating data via code 12
Multi Select List Box 4
Parameter Query 3

Top