Solved: Change Query Criteria

[jmenche]

Hi,

I have a query called 'qryModel01' that has a field called 'Vendor'. I want to add two command buttons on a form that will change the criteria for Vendor. One button, called 'Internal', will make the Vendor criteria 'Cadbury Adams'. The other button, called 'External', will make the criteria blank so all Vendors are chosen.

Can someone help me out?

Thanks!!! :-)

[Tommy]

Private Sub CommandButtonInternal_Click()
qryModel01 = "SELECT * FROM TABEL WHERE VENDOR = 'Cadbury Adams'"
'run query
End Sub
Private Sub CommandButtonExternal_Click()
qryModel01 = "SELECT * FROM TABEL WHERE VENDOR <> '' ORDER BY VENDOR"
'run query
End Sub

[jmenche]

Tommy,

Thanks for the help but it does not work. Nothing is dimensionalized. qryModel01 is a saved query.

I was also thinking that a custom function might do the trick but I cannot seem to be able to come up with a string that will allow me to select all in criteria. The correct criteria in a normal query is "Like "*"" however I cannot nest quotations in VBA.

Can anyone help?

[Tommy]

nest quotations "Like ""*"""

[jmenche]

Tommy,

I built this custom function and use it as criteria in a query. The query produces no records with the Else condition.

:-(


Function ModelCriteria() As String
If [Forms]![main menu]![ogPickModel].Value = 1 Then
ModelCriteria = "Cadbury Adams"
Else
ModelCriteria = "Like ""*"""
End If

End Function

[Tommy]

I would be a lot more help if you posted the database with confidential infromation deleted. I just like to know what I am working with.

Function ModelCriteria() As String
If [Forms]![main menu]![ogPickModel].Value = 1 Then
ModelCriteria = "= Cadbury Adams"
Else
ModelCriteria = "Like ""*"""
End If

End Function

[xCav8r]

:hi: At first glance this looks like a misguided approach to accomplishing something otherwise simpler, but here's a procedure you can use to change the SQL of saved queries.


' borrowing from Tommy
Private Sub CommandButtonInternal_Click()
ChangeQuerySQL "qryModel01", "SELECT * FROM tblName WHERE Vendor = 'Cadbury Adams';"
End Sub

Private Sub CommandButtonExternal_Click()
ChangeQuerySQL "qryModel01", "SELECT * FROM tblName WHERE Vendor <> '' ORDER BY VENDOR;"
End Sub

'put this in a standard module
Public Function ChangeQuerySQL(QueryName As String, NewSQL As String) As Boolean
'---------------------------------------------------------------------------
' Desc: When given a query name and valid SQL statement, it will
' change the SQL of the query. Returns TRUE if the change
' was successful; FALSE, if it wasn't.
' Arguments: QueryName -- must be a query saved in the current database
' NewSQL -- must be a valid SQL statement; otherwise, no
' change can or will be made.
'---------------------------------------------------------------------------
On Error GoTo ChangeQuerySQL_Error
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Set db = CurrentDb
Set qdf = db.QueryDefs(QueryName)
qdf.SQL = NewSQL
ChangeQuerySQL = True

' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
ChangeQuerySQL_Exit:
qdf.Close
db.Close
Set qdf = Nothing
Set db = Nothing
On Error GoTo 0
Exit Function
ChangeQuerySQL_Error:
ChangeQuerySQL = False
Debug.Print Err.Description
Resume ChangeQuerySQL_Exit
End Function




I would be a lot more help if you posted the database with confidential infromation deleted. I just like to know what I am working with.


If I only had a dime for every time I thought that. ;)

[jmenche]

Thanks all!

I have to admit that I got my answer from the Microsoft discussion groups :-(.

My problem was how to pass All as a parametized(sp?) criteria using a form. They suggested this and it works great (easy too).

Assuming that you have an option group with two options and if the value is 1 you want "Vendor A" and if the value is 2 you want "All". In your query type this into the Vendor field criteria: Like IIF(form!formname!og.value=1,"Vendor A", "*").

Thanks again for your help.

[xCav8r]

jmenche, :hi:

I'm glad that you got what you were looking for, and I'm especially thankful that you posted the solution to your own problem here--even though you got it elsewhere. It helps us keep the forum tidy for future users. http://vbaexpress.com/forum/images/smilies/001.gif

I should say that there are many ways to affect the WHERE clauses in your SQL statements. Writing parameters into the query that look at a form is one method, but the other approaches that Tommy and I posted achieve the same result--except that what Tommy posted (and I copied) only returns results when the vendor field isn't empty. If you want to weed out blanks using the method you're currently using, you should add the additional parameter to your saved query.

Anyway, happy that you're up and running as desired. :thumb