Creating Crosstab Query

[Guest]

I've got a form that sets query parameters and creates the following SQL statement

SQLstmt = strSelect & " [SELECT " & strUnionSelect1 &
" WHERE rpm_date >= " & Format(startdate, "\#mm/dd/yyyy\#") &
" AND rpm_date <= " & Format(EndDate, "\#mm/dd/yyyy\#") &
" UNION SELECT " & strUnionSelect2 &
" WHERE trade_date >= " & Format(startdate, "\#mm/dd/yyyy\#") &
" AND trade_date <= " & Format(EndDate, "\#mm/dd/yyyy\#") &
"]. AS [%$##@_Alias]" &
" WHERE " & strWhere &
" ORDER BY " & strOrde

How can I make a crosstab query from this?
Thanks in advance.

 

[Guest]

The best way I have ever done this is to use the Query Wizard to build the cross-tab query you want, then switch to the SQL view from design view, then copy the SQL string into your VBA code and then edit the string however you need to put in the variables

The following code shows how to create a stored-query named "qryTmp" from your SQL string

Dim cat As New ADOX.Catalo
Dim cmd As New ADODB.Comman

Set cat.ActiveConnection = CurrentProject.Connectio
cmd.CommandText = strSQ
cat.Views.Append "qryTmp", cm

docmd.openquery "qryTmp

Hope this make sense and helps

----- Bob Ewers wrote: ----

I've got a form that sets query parameters and creates the following SQL statement

SQLstmt = strSelect & " [SELECT " & strUnionSelect1 &
" WHERE rpm_date >= " & Format(startdate, "\#mm/dd/yyyy\#") &
" AND rpm_date <= " & Format(EndDate, "\#mm/dd/yyyy\#") &
" UNION SELECT " & strUnionSelect2 &
" WHERE trade_date >= " & Format(startdate, "\#mm/dd/yyyy\#") &
" AND trade_date <= " & Format(EndDate, "\#mm/dd/yyyy\#") &
"]. AS [%$##@_Alias]" &
" WHERE " & strWhere &
" ORDER BY " & strOrde

How can I make a crosstab query from this?
Thanks in advance.

 

[Guest]

This will be very useful...unfortunately, I got a compile error on the line (Dim cat As New ADOX.Catalog
Error: User defined type not defined. Do I need to add a new reference. I really don't know anything about user-defined types or the connection line. Below is the code as i have it. Thanks

Dim allmystringvariable
Dim twodatevariable
Dim cat As New ADOX.Catalog(*compile error
Dim cmd As New ADODB.Comman
Set cat.ActiveConnection = CurrentProject.Connectio

'bunch of stuff to piece together the SQLstm

SQLstmt = "see full text below

cmd.CommandText = SQLstm
cat.Views.Append "qryTmp", cm

DoCmd.OpenQuery "qryTmp

----- JP_Denver wrote: ----

The best way I have ever done this is to use the Query Wizard to build the cross-tab query you want, then switch to the SQL view from design view, then copy the SQL string into your VBA code and then edit the string however you need to put in the variables

The following code shows how to create a stored-query named "qryTmp" from your SQL string

Dim cat As New ADOX.Catalo
Dim cmd As New ADODB.Comman

Set cat.ActiveConnection = CurrentProject.Connectio
cmd.CommandText = strSQ
cat.Views.Append "qryTmp", cm

docmd.openquery "qryTmp

Hope this make sense and helps

----- Bob Ewers wrote: ----

I've got a form that sets query parameters and creates the following SQL statement

SQLstmt = strSelect & " [SELECT " & strUnionSelect1 &
" WHERE rpm_date >= " & Format(startdate, "\#mm/dd/yyyy\#") &
" AND rpm_date <= " & Format(EndDate, "\#mm/dd/yyyy\#") &
" UNION SELECT " & strUnionSelect2 &
" WHERE trade_date >= " & Format(startdate, "\#mm/dd/yyyy\#") &
" AND trade_date <= " & Format(EndDate, "\#mm/dd/yyyy\#") &
"]. AS [%$##@_Alias]" &
" WHERE " & strWhere &
" ORDER BY " & strOrde

How can I make a crosstab query from this?
Thanks in advance.

 

[Guest]

Nevermind...the answer is yes I need to add a new reference. thanks so much....this wil help in many different ways.