Multiple parameter query - need Help!!!

  • Thread starter Thread starter Irina
  • Start date Start date
I

Irina

I have a select query that is using multiple parameters to create a report
for a user that I am having issues with. The parameters get passed in via
textfields in a form. The selection is done from a single table. For example,
I want the user to be able to
specify employee name, company, job code, department, and other criteria. I
also would like the user to be able to leave fields blank, for example, no
company, or no job code, so the results would be all companies, all job codes.
My problem is that the query only filters based on the first 2 parameter
inputs : name and company, all the other
dont work. What is the best way to get this to work?
thanks for any help you can provide.
 
Answered for you May 8th in microsoft.public.access.queries

Subject: Multiple parameter query - need Help!!!
 
Do you get any error messages?

Would you post the SQL of the current query for the report?

I want the user to be able to
specify employee name, company, job code, department, and other criteria.
Click to expand...

How many fields and what are the field names you want to search on?

HTH
 
Hi Steve,
The design of this db is as follows,
I have a form where user specifies the fields for the report via checking
appropriate checkboxes, then clicks "Done", another form comes up to
select the filters/criteria for report with 6 variables: name - text field,
company - combo box, department combo box, jobtitle - text field,
workLocation - text field,
citizenship - text. I am trying to create a query that would use all those
user
inputs to filter the table where all this info is stored.
Someone suggested to me to build sql statement using AfterUpdate and then
call a function to build code. so that is what I tried to do.
But I am getting errors such as type mismatch in VBA
but not if I run the query as a regular query.
For example, this piece of code does not work
it does not filter based on individual name, instead I get an error
If I run the select qry by itself I get all the names in the table
sql_code = "SELECT tblClientReport.*, tblClientReport.Individual FROM
tblClientReport WHERE (((tblClientReport.Individual) Like
[forms]![frmClientReportFields2].[Individual] & " * "));"


----I appreciate your help
 
Hi Irina,
sql_code = "SELECT tblClientReport.*, tblClientReport.Individual FROM
tblClientReport WHERE (((tblClientReport.Individual) Like
[forms]![frmClientReportFields2].[Individual] & " * "));"
Click to expand...

This statement is missing a text delimiter, a quote and an "&" between LIKE
and [forms].

sql_code = "SELECT tblClientReport.* FROM tblClientReport WHERE
tblClientReport.Individual Like '" &
[Forms]![frmClientReportFields2].[Individual] & "*';"

Expanded, after the LIKE is : ....Individual Like ' " & [Forms]!.....

One way to generate the report:

Set the report recordsource to a query or SQL string where the SQL is:

"SELECT tblClientReport.* FROM tblClientReport"

Then add this code to a button to open the report:

Watch for line wrap!!!!!

'-------code beg------------
Public Sub cmdPrintReport_Click()
Dim stDocName As String
Dim strWhere As String

stDocName = "rptClientReport"

strWhere = ""

'pay attention to the field type - text and date types need delimiters

' name - text field - needs delimiters
If Not IsNull([Forms]![frmClientReportFields2]![Individual]) Then
strWhere = strWhere & " tblClientReport.Individual Like '"
strWhere = strWhere & [Forms]![frmClientReportFields2].[Individual] &
"*' AND"
End If

'jobtitle - text field, - needs delimiters
If Not IsNull([Forms]![frmClientReportFields2]![JOBTITLE]) Then
strWhere = strWhere & " tblClientReport.JOBTITLE = '"
strWhere = strWhere & [Forms]![frmClientReportFields2].[JOBTITLE] & "'
AND"
End If

'workLocation - text field, - needs delimiters
If Not IsNull([Forms]![frmClientReportFields2]![WORKLOCATION]) Then
strWhere = strWhere & " tblClientReport.WORKLOCATION = '"
strWhere = strWhere & [Forms]![frmClientReportFields2].[WORKLOCATION]
& "' AND"
End If

'citizenship - text.- needs delimiters
If Not IsNull([Forms]![frmClientReportFields2]![CITIZENSHIP]) Then
strWhere = strWhere & " tblClientReport.CITIZENSHIP = '"
strWhere = strWhere & [Forms]![frmClientReportFields2].[CITIZENSHIP] &
"' AND"
End If

'company - combo box,
If Not IsNull([Forms]![frmClientReportFields2]![COMPANY]) Then
strWhere = strWhere & " tblClientReport.company = "
strWhere = strWhere & [Forms]![frmClientReportFields2].[COMPANY] & "
AND"
End If

' department combo box,
If Not IsNull([Forms]![frmClientReportFields2]![DEPARTMENT]) Then
strWhere = strWhere & " tblClientReport.DEPARTMENT Like "
strWhere = strWhere & [Forms]![frmClientReportFields2].[DEPARTMENT] &
" AND"
End If

'remove the last 4 chars --> " AND"
If Len(strWhere) > 4 Then
strWhere = Left(strWhere, Len(strWhere) - 4)
End If

' MsgBox strWhere

' open the report using the "WhereCondition" clause
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

End Sub
'-------code end------------


Another way is to use the code above, but make these changes:
(<snip> means I didn't want to show the code again)

ADD:

Dim strSQL as string

strSQL = "SELECT tblClientReport.* FROM tblClientReport"
<snip>


CHANGE:
<snip>
'remove the last 4 chars --> " AND"
If Len(strWhere) > 4 Then
strWhere = Left(strWhere, Len(strWhere) - 4)
strWhere = " Where " & strWhere
End If

' MsgBox strWhere

' create the SQL statement
strSQL = strSQL & strWhere

' open the report using the "WhereCondition" clause
DoCmd.OpenReport stDocName, acViewPreview, , , , strSQL

End Sub


In the Open event of the report, enter:

Me.RecordSource = Me.OpenArgs


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Irina said:
Hi Steve,
The design of this db is as follows,
I have a form where user specifies the fields for the report via checking
appropriate checkboxes, then clicks "Done", another form comes up to
select the filters/criteria for report with 6 variables: name - text field,
company - combo box, department combo box, jobtitle - text field,
workLocation - text field,
citizenship - text. I am trying to create a query that would use all those
user
inputs to filter the table where all this info is stored.
Someone suggested to me to build sql statement using AfterUpdate and then
call a function to build code. so that is what I tried to do.
But I am getting errors such as type mismatch in VBA
but not if I run the query as a regular query.
For example, this piece of code does not work
it does not filter based on individual name, instead I get an error
If I run the select qry by itself I get all the names in the table
sql_code = "SELECT tblClientReport.*, tblClientReport.Individual FROM
tblClientReport WHERE (((tblClientReport.Individual) Like
[forms]![frmClientReportFields2].[Individual] & " * "));"


----I appreciate your help

Steve Sanford said:
Do you get any error messages?

Would you post the SQL of the current query for the report?



How many fields and what are the field names you want to search on?

HTH
Click to expand...
Click to expand...
 
Back
Top