how to builit Query by BVA

[Atif]

Hi,

How can I creat a Query (Select and Crosstab) using VB code.

Thanks

 

[Alex Dybenko]

Hi,
look at CreateQueryDef method in access help

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[ollyb303]

Here's an example that's working for me:

'Declare variables for SQL query
Dim strSQL As String
Dim strTable As String
Dim strStat As String
Dim strGroup As String
Dim strTransform As String
Dim strPivot As String
Dim strDate As String

'Set variables for SQL query
If StatCombo.Value = "SNBE" Then
strTable = "TPERF_STATS_DAILY_CHT"
Else
strTable = "TPERF_STATS_DAILY_CC"
End If

strStat = StatCombo.Value

strGroup = GroupByCombo.Value

If StatCombo.Value = "CALLS_HANDLED" Then
strTransform = "Sum"
Else
strTransform = "Avg"
End If

strPivot = "NOM_DATE"

strDate = "NOM_DATE"

'Construct SQL query
strSQL = "PARAMETERS [Forms]![SelectDate]![StartDate] DateTime,
[Forms]![SelectDate]![EndDate] DateTime; " & _
"TRANSFORM " & strTransform & "(" & strTable & ".[" & strStat & "]) AS
[" & strTransform & "Of" & strStat & "] " & _
"SELECT " & strTable & ".[" & strGroup & "] " & _
"FROM " & strTable & " " & _
"WHERE (((" & strTable & "." & strDate & ") Between
[Forms]![SelectDate]![StartDate] And [Forms]![SelectDate]![EndDate])) " & _
"GROUP BY " & strTable & ".[" & strGroup & "] " & _
"PIVOT " & strTable & "." & strPivot & "; "

'Pass SQL query to Query1
Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.QueryDefs("Query1")
qdf.SQL = strSQL

Obviously you can create the various strings from which the query is built
however you want.

Hope this helps.