Creating a Query Definition

[GJP55]

I have a piece of code that changes a query definition and passes some parameters into it. This works fine until I go to run the query.

The query does not return any records with the parameters until I go into the Query design and delete any of the criteria and then re-type the same value back in.

Can anybody help with this and explain why this is happening ?

Thanks

Dim SQLString As String
Dim Qry As QueryDef
Dim DB As Database

Dim SuppID As Integer
Dim SalesMonth As String
Dim CustID As String

Set DB = CurrentDb()
Set Qry = DB.QueryDefs("QrySalesAnalysis")

SuppID = cmbSupplier
SalesMonth = cmbSalesMonth
CustID = cmbCustID

SQLString = "Select [SalesValue] , [SalesMonth],[CustomerID], [SupplierID] FROM [SalesAnalysis] WHERE [SalesMonth]= " & SalesMonth & " And [CustomerID] = " & CustID & " And [SupplierID] = " & SuppID & ";"
Qry.SQL = SQLString
Qry.Close

End Function

 

[PHV]

You have to play with the Parameters collection.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tranman]

GJP,
I think it's because of your syntax--text arguments in where clauses need to be in quotes:

SQLString = "Select [SalesValue] , [SalesMonth],[CustomerID], [SupplierID] FROM [SalesAnalysis] WHERE [SalesMonth]= [red]'[/red]" & SalesMonth & "[red]'[/red] And [CustomerID] = [red]'[/red]" & CustID & "[red]'[/red] And [SupplierID] = " & SuppID & ";"

But numeric arguments like SuppID, don't.

Tranman

 

[GJP55]

That explains it 100%, thanks very much. It was driving me mad.
Thanks also to PHV.