Assigning multiple parameters using a form in vb

[Tetol]

I have a form with 3 combo boxes that I would like to uses to filter records in query. I used the If..Then statement

I have written the code to run a query if one of the 3 boxes has value in it and it works.

If Not IsNull(Me.[Combo1]) And IsNull(Me.[Combo2]) And IsNull(Me.[Combo3]) Then

strMySQL = "Select * from myQuery Where MyQuery.[Value1]= '" & Me.[Combo1] & "'".

End IF
Set strSQL = CurrentDb.CreateQueryDef("MyQuery", strMySQL)
DoCmd.OpenQuery "MyQuery"

However, if there are two boxes with values in them, the code I have below to assign the values does not work. I think it has something to do with how I am placing all the "" for the second parameter.


strMySQL = "Select * from myQuery Where myQuery.[Value1]= '" & Me.[Combo1] & "'" And "myQuery.[Value2] = '" & Me.[Combo2] & "'"

I also need to have the code run if all the 3 boxes have values in them.

Then I would like to include an Order By clause

Please help.

 

[TheAceMan1]

How are ya Tetol . . .

[blue]Current : Me.[Combo1] & "'" And "myQuery.[Value2] = '" & Me.[Combo2] & "'"
ShouldBe: Me.[Combo1] & [green][b]"' AND myQuery.[Value2] = '"[/b][/green] & Me.[Combo2] & "'"[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Tetol]

The AceMan1- Thanks but the the query resulted in blank. It placed the value of Combo1 as the criteria for both value 1 ans value 2. It ignored the value in Combo 2 so no data was pulled. Here is the statement:

Dim strSQL As Object
Dim strMySQL As String
Dim strDrop As String

strDrop = "Drop Table MyQuery"
On Error Resume Next
CurrentDb.Execute strDrop, dbFailOnError

If Not IsNull(Me.[Combo1]) And Not IsNull(Me.[Combo2]) And IsNull(Me.[Combo3]) Then

strMySQL = "Select * from MyQuery Where MyQuery.[Value1]= '" & Me.[Combo1] & "' And MyQuery.[Value2] = '" & Me.[Combo2] & "'"

End IF

Set strSQL = CurrentDb.CreateQueryDef("MyQuery", strMySQL)DoCmd.OpenQuery "MyQuery"

 

[TheAceMan1]

Tetol . . .

Be aware ... I was revealing correction only for the section shown:

[blue]"Select * from myQuery Where myQuery.[Value1]= '" & [purple][b]Me.[Combo1] & "'" And "myQuery.[Value2] = '" & Me.[Combo2] & "'"[/b][/purple][/blue]

In any event, to clean this up post back the data types of [blue]Value1, Value2, Value3[/blue], and post the [blue]RowSource[/blue] of the comboboxes. If any [blue]RowSource[/blue] is a query post the SQL.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Tetol . . .

I found quite a bit wrong with your code so I worked up something earlier today. Note that once the query [blue]myQuery[/blue] exist, all you have to do is build your SQL and replace that for [blue]myQuery[/blue]. Since we need to know if the query exists ahead of time, in a module in the modules window, copy/paste the following support routine:

[blue]Public Function QueryExist(qryName As String) As Boolean
   Dim db As DAO.Database, qdf As DAO.QueryDef
   Dim rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT Name, Type " & _
         "FROM MSysObjects " & _
         "WHERE (([Name]='" & qryName & "') AND ([Type]=5));"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then QueryExist = True
   
   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

You can use it to tell if a query exists.

Finally ... in the code module of the form, copy/paste the following routine. [blue]you![/blue] substitute proper names in [purple]purple[/purple]:

[blue]Public Sub fltrMyQuery()
   Dim db As DAO.Database, qdf As DAO.QueryDef, SQL As String
   Dim ctlName As String, fldName As String, idx As Integer
   
   Set db = CurrentDb
   SQL = "SELECT * FROM [[purple][B][I]YourTableName[/I][/B][/purple]] WHERE (1=1)"
   
   [green]'Create query if it doesn't exist.[/green]
   If Not QueryExist("myQuery") Then
      Set qdf = db.CreateQueryDef("myQuery", SQL)
      Set qdf = Nothing
   End If

   [green]'Append comparisons to the where clause.[/green]
   For idx = 1 To 3
      ctlName = Choose(idx, "Combo1", "Combo2", "Combo3")
      fldName = Choose(idx, "Value1", "Value2", "Value3")
      
      [green]'Append only if data available.[/green]
      If Trim(Me(ctlName) & "") <> "" Then
         SQL = SQL & " AND ([" & fldName & "]='" & Me(ctlName).Column(1) & "')"
      End If
   Next

   SQL = SQL & ";"
   [green]'Debug.Print SQL[/green]
   
   [green]'Modify the SQL of myQuery[/green]
   db.QueryDefs("myQuery").SQL = SQL
   
   [green]'Open the query.[/green]
   DoCmd.OpenQuery "myQuery"
   
   Set db = Nothing

End Sub[/blue]

To run the routine:

[blue]   Call fltrMyQuery[/blue]

Success still depends on the datatypes of value1 thru value3 and wether the combo's are returning the right column. Presently the code code considers the 3 combo's return text. The routine handles all combinations of the combo's as well. If the datatypes are indeed text, then you should have no problem ... otherwise modification will be in order.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Tetol]

Aceman1 - I just got back to this issue toay. Thanks for the responses. However I have a question for each of the methods we have tried.

For the first method:
The code below is now working except when the value has a ' in it as in "St. Mary's" or "St Joseph's". If the value is "St. Mary" of St. Joseph", it works.

"Select * from myQuery Where myQuery.[Value1]= '" & Me.[Combo1] & "'" And "myQuery.[Value2] = '" & Me.[Combo2] & "'"

Is there a way to get values that has ' to not throw an error?


For the second method:
I pasted the codes as above and ran the routine from the command button's "On click" property. But it threw an error

It appears the content of combo2 is being read as value3 and the content of combo3 is being read as value2.

Also, what does the (1=1) stand for?

Thanks so much, whichever method works is ok with me.

 

[Tetol]

FYI

Combo1 is a date data type
Combo2 is a test data type
combo3 is a text data type

Thanks

 

[PHV]

Is there a way to get values that has ' to not throw an error
Replace this:[tt]
& Me.[myCombo][/tt]
with this:[tt]
& Replace(Me.[myCombo], "'", "''")[/tt]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

Tetol . . .

[blue]Combo1 is a date data type
Combo2 is a [red]test data type[/red]
combo3 is a text data type[/blue]

There's no such data type as [red]Test[/red]. Its probably numeric or text. Which one? [green]Provide examples of the data in the combo's.[/green]

We also need to know which column in the [blue]RowSource[/blue] of the combo's the data lies. For this just post back the [blue]Row Source[/blue] of each combo.

I need to modify the code in the second method but not without the above info.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Tetol]

Here are the details -

Combo1 is a date data type e.g. 02/2011

Row Source: SELECT DISTINCT qryReportingPeriod.ReportPeriod FROM qryReportingPeriod;


Combo2 is a text data type e.g. Alab, Miss

Row Source:
SELECT tblA.StateCode, tblA.StateName
FROM tblA
ORDER BY tblA.[StateCode], tblA.[StateName];

Bound Column = 1

Combo3 is a text data type e.g. St. Mary's, Pensacola, Baptist Hospital

Row Source:
SELECT tblB.FacilityName, tblB.StateCode
FROM tblB
WHERE (((tblB.StateCode)=[Forms]![frmDialog_EHRMetrics_Statistics]![Combo2]) AND ((tblB.FacilityStatus)="Opened") AND ((tblB.FacilityTypeID)=1))
ORDER BY tblB.StateCode, tblB.FacilityName;

Bound Column = 1

Thank you

 

[Tetol]

FYI - The tip by PHV also worked but would still be interested in the other method.

Thank you

 

[TheAceMan1]

Tetol . . .

There's a big problem with [blue]Combo1[/blue] your date combo. [blue]It was revealed to me during my simulation of your combo's. [red]Something I've never come across before.[/red] [/blue] SO Pay ATTENTION! ... You say:

[blue]Combo1 is a date data type e.g. 02/2011[/blue]

Thats month & year ... as far as a date format is concerned ... can be done. However I've noticed that with this format, at any level (table/form) [red]the combo turnacates to the first of the month![/red] ... as follows:

[blue]Actual Date [red]Combo Return[/red]
*********** [red]************[/red]
Dec 25,1959  [red]12/01/1959[/red]
Apr 11 1990  [red]04/01/1990[/red]
Apr 29 1990  [red]04/01/1990[/red]
Feb 12 2010  [red]02/01/2010[/red]
Jan 22 2011  [red]01/01/2011[/red][/blue]

I can't explain why ... but do you see my point?

Your format at least needs to include [blue]month,day,year[/blue] and I'm asking my [blue]Tek-Tips[/blue] colleges to hone in on this. This is a first for me ... an as such a first for others.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]