Executing 'Transform' SQL in VBA

[Brendan Reynolds]

On which line?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Hi Brendan -

I tried the below and I got an "Object Required" error message.

Here is my SQL:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String


' Dim qry_Training_PCT As String

strSQL = "TRANSFORM 1-(Sum(IIf([%
grade]<70,1,0))/Count([tbl_Main_report]![STUDENT_ID])) AS [PCT_Pass] " & _
"SELECT tbl_Main_Report.Region, tbl_Main_Report.Director " & _
"FROM tbl_Main_Report INNER JOIN STUDENT_FIELDS ON
tbl_Main_Report.STUDENT_ID = STUDENT_FIELDS.STUDENT_ID " & _
"GROUP BY tbl_Main_Report.Region, tbl_Main_Report.Director " &
_
"PIVOT tbl_Main_Report.COMPETENCY_GROUP"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Do Until rst.EOF
Debug.Print rst.Fields(0)
rst.MoveNext
Loop
rst.Close

What exactly do you mean by 'execute' in this context, Leo? Generally
speaking, in code, one executes an action query, and opens a recordset on
a
select query. A cross-tab query is a form of select query, and I don't
have
any difficulty opening a recordset on a cross-tab query ...

Public Sub TestTransform()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

'SQL copied from Quarterly Orders By Product query in Northwind ...
strSQL = "TRANSFORM Sum(CCur([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS " & _
"ProductAmount SELECT Products.ProductName, Orders.CustomerID,
Year([OrderDate]) AS OrderYear " & _
"FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = " & _
"[Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID WHERE (((Orders.OrderDate) " & _
"Between #1/1/1997# And #12/31/1997#)) GROUP BY
Products.ProductName, Orders.CustomerID, Year([OrderDate]) " & _
"PIVOT 'Qtr ' & DatePart('q',[OrderDate],1,0) In ('Qtr 1','Qtr
2','Qtr 3','Qtr 4');"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Do Until rst.EOF
Debug.Print rst.Fields(0)
rst.MoveNext
Loop
rst.Close

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Hi Doug -

Sorry for the delay in response. I've been out of the country for the
past
week.

Anyway, I can't do your below suggestion because I am unable to execute
SQL
in my vba(.mdb) environment. This is due to the 'Transform' part of the
statement that VBA give me a "expected select...." error (i.e. it
doesn't
recognize the 'Transform' part of the statement)

If I could execute Transform in my vba (.mdb), then it would be alot
easier
for me to manipulate the code..
However, i am stuck with the openquery function..


:

Your SQL is going to look something along the lines of

WHERE Field1 = Forms!frm_Reports!cmb_Dir_Rep_Region

Change each of those = signs to

WHERE Field1 LIKE Forms!frm_Reports!cmb_Dir_Rep_Region

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi Doug -

I tried the "*" but I get the same results as when I started (i.e.
nothing
returned in my query). I did not quite understand what you mean by
modifying
the SQL statement so that it uses 'Like' with each of the
parameters.

Also, I can't NOT use parameters because since I am writing my code
in
VBA
(mdb), it doesn't recognize a 'Transform' statement which is
bascially
a
crosstab query. Thus, I have to 'Open Query' as opposed to having
the
flexibility to 'RunSQL'. (If I could run SQL in VBA code in .mdb,
then
my
life will be so much easier in this problem).

Thanks for all your help. Any more thoughts will be great!!


:

No, your Like ""*"" is not correct. You can't change the operator
as
part of
a parameter's value.

Try changing your SQL so that it uses Like with each of the
parameters,
and
then use

If Len(Trim$(Forms!frm_Reports!cmb_Dir_Rep_Region & vbNullString))
<>
0
Then
qdf.Parameters("Forms!frm_Reports!cmb_Dir_Rep_Region") =
Forms!frm_Reports!cmb_Dir_Rep_Region
Else
qdf.Parameters("Forms!frm_Reports!cmb_Dir_Rep_Region") = "*"
End If

(when you don't provide a wildcard character, Like is the same as
=)

Alternatively, don't use parameters at all, and change your SQL to
something
like:

WHERE Field1 = Forms!frm_Reports!cmb_Dir_Rep_Region OR
Forms!frm_Reports!cmb_Dir_Rep_Region IS NULL

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Hi Douglas and Brendan -

I changed my code to "If
Len(Trim$(Forms!frm_Reports!cmb_Dir_Rep_Region &
vbNullString)) > 0"
But my query still returns me a blank query when any of the
combo
boxes is
blank. This is no different from when I was using "If
Forms!frm_Reports!cmb_Dir_Rep_Region <> "" " in the beginnning.
Is my Like ""*"" statement correct?? How can I write a
statement
that
'ignores' the parameter completely when 1 combo box is not
selected?
(this
is
what Ive been trying to do)




:

Len returns a number: the number of characters in the string.

You need

If Len(Trim$(Forms!frm_Reports!cmb_Dir_Rep_Region &
vbNullString)) >
0
Then


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Hi Brendan -

Thanks for the prompt response. I tried your recommmendation
and I
got
a
"Type Mismatch" error.

Here was the code I used:

If Len(Trim$(Forms!frm_Reports!cmb_Dir_Rep_Region &
vbNullString))
<>
""
Then

qdf.Parameters("Forms!frm_Reports!cmb_Dir_Rep_Region") =
Forms!frm_Reports!cmb_Dir_Rep_Region

Else

qdf.Parameters("Forms!frm_Reports!cmb_Dir_Rep_Region") =
"Like
""*"" "

End If

Did I miss something? Please help...


:

In the example you posted, you are only checking for an
empty
string,
you
are not checking for a Null value. But this is clearly not
the
actual
code,
as it is not capitalized the way VBA would capitalize it,
so
I
can't
tell
for sure whether the mistake is in the actual code, or
only
in
the
newsgroup
post. In situations like this, it's always a good idea to
copy
and
paste
the
actual code rather than retyping it.

If the error is in the actual code, try replacing
ControlName
<>
""
with
Len(Trim$(ControlName & vbNullString)) <> 0. That will
catch
Null
values,
empty strings, and strings consisting only of spaces.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making
it
impossible
for
me to use a real e-mail address in public newsgroups.
E-mail
replies
to
this post will be deleted without being read. Any e-mail
claiming to
be
from brenreyn at indigo dot ie that is not digitally
signed
by
me
with a
GlobalSign digital certificate is a forgery and should be
deleted
without
being read. Follow-up questions should in general be
posted
to
the
newsgroup, but if you have a good reason to send me
e-mail,
you'll
find
a useable e-mail address at the URL above.


Hi Tom and Brendan -

The problem I am facing is not in the parameters. My
"qdf.parameter"
statements seem to be working fine in VBA.

However, what I am trying to do is to write a code that
allows
the
user to
execute the SQL statement based on what is selected in
my
combo
boxes.
So
for
example, I have 4 combo boxes (Region, Country, Company,
Division),
and I
want the user to have the flexibility to select any (1
or
more) of
the
combo
boxes.

Thus, I know I have to write a "qdf.parameters"
statement
to
define
the
parameters in each of the 4 combo boxes. But what I
don't
know
is
how
to
write a statement to account for combo boxes that were
not
selected
(i.e.
have NULL or "" values in them).

I tried the following but it returns me nothing in my
query
when a
combo
box
is not selected.

If forms!frm_Reports!cmb_Region <> "" Then

qdf.parameters("forms!frm_Reports!cmb_Region") =
forms!frm_Reports!cmb_Region

Else

qdf.parameters("forms!frm_Reports!cmb_Region") = "Like
""*"" "

End If

This doesn't seem to work. Please advise!
Thanks so much!

:

As Tom indicates elsewhere in this thread, it seems the
problem
is
the
parameters. To set parameters via VBA, you can use the
Parameters
collection
of the QueryDef object ...

Dim qdf As QueryDef
Set qdf = "NameOfQuery"
qdf.Parameters("NameOfParameter") = ValueOfParameter

Now you can open a recordset using the OpenRecordset
method
of
the
QueryDef
instead of the more commonly used OpenRecordset method
of
a
Database
object
....

Dim rst As DAO.Recordset
Set rst = qdf.OpenRecordset

Obviously, as Tom says, this means you will have to
save
the
SQL
statement
as a saved query. If you're building the SQL statement
dynamically,
you
can
still do that by manipulating the SQL property of the
saved
query
....

Set qdf = "NameOfQuery"
qdf.SQL = "your SQL statement here"
qdf.Parameters("NameOfParameter")=ValueOfParameter

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in
making
it
impossible
for
me to use a real e-mail address in public newsgroups.
E-mail
replies
to
this post will be deleted without being read. Any
e-mail
claiming
to
be
from brenreyn at indigo dot ie that is not digitally
signed
by me
with a
GlobalSign digital certificate is a forgery and should
be
deleted
without
being read. Follow-up questions should in general be
posted
to
the
newsgroup, but if you have a good reason to send me
e-mail,
you'll
find
a useable e-mail address at the URL above.


Yes. I am using a MDB database. I tried Naresh's
recommendation
and
i
still
see the same error. Does this mean there is
absolutely
no
way
of
running a
'transform' statement in VBA??

I am lookin to write an SQL statement which has
parameters
that
changes
according to whether certain combo boxes have been
selected. So
for
example,
in one case I may have parameter A used, and in other
cases
I
may
have
parameters B,C used instead. Thus, the crosstab SQL
query
changes
in
length.
Is this not possible to do in code??

:

Are you executing this query against a Jet (MDB)
database?
Not
SQL
Server
or
MSDE? TRANSFROM is a proprietary feature of Jet, and
will
not
work
with
any
current release of SQL Server.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in
making
it
impossible
for
me to use a real e-mail address in public
newsgroups.
E-mail
replies
to
this post will be deleted without being read. Any
e-mail
claiming
to
be
from brenreyn at indigo dot ie that is not digitally
signed by
me
with
a
GlobalSign digital certificate is a forgery and
should
be
deleted
without
being read. Follow-up questions should in general be
posted to
the
newsgroup, but if you have a good reason to send me
e-mail,
you'll
find
a useable e-mail address at the URL above.


message
Hi -

I have a crosstab query that I am trying to
execute
in
the
Visual
Basic
environment. The SQL statement starts with a
'TRANSFORM'
command
and
I
get
an
error that says:

"Invalid SQL statement; expected 'DELETE',
'INSERT',
'PROCEDURE',
'SELECT',
or 'UPDATE'"

Why is it that Visual Basic does not seem to
recognize
the
TRANSFORM
(CrossTab) SQL statement?

I would appreciate it if you could provide me with
some
advise
on
how
to
execute 'TRANSFORM' SQL statements in VB.

Thanks much!