Solved

SQL: error in transform statement

Posted on 2000-02-16
12
516 Views
Last Modified: 2012-08-13
I have created a crosstab query in Access and then converted this to SQL to include in a Private sub:


strSQL = "TRANSFORM [Count(BASEPLAN.SORTCODE)] AS HOST," _
 & " SELECT CONTROL1.PROJ_ENG," _
 & " FROM (BASEPLAN INNER JOIN CONTROL1 ON BASEPLAN.SORTCODE = CONTROL1.SORTCODE) INNER JOIN Modules ON BASEPLAN.SORTCODE = Modules.SORTCODE" _
 & " WHERE CONTROL1.ABPACK Between #" & Format(dteFirst, "mm\/dd\/yy") & "# And #" & Format(dteSecond, "mm\/dd\/yy") & "#" _
 & " AND CONTROL1.PROJ_ENG = '" & strEngName & "'" _
 & " GROUP BY CONTROL1.PROJ_ENG " _
 & " PIVOT BASEPLAN.APZ"

Forms!frmkpi1!subformKPIAPZCODES.Form.RecordSource = strSQL

I recieve a syntax error in transform statement.

Any ideas on what is wrong with this?
0
Comment
Question by:sicando
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
12 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 2526758
Hello sicando,

I think there's extra comma in first line...

should be like this:

strSQL = "TRANSFORM [Count(BASEPLAN.SORTCODE)] AS HOST" _
 & " SELECT CONTROL1.PROJ_ENG," _
....

Regards,
Paasky

0
 

Author Comment

by:sicando
ID: 2527044
You're right there is an extra comma.  I feel really stupid!!  However this still does not have the query running.  I am still recieving the same error when I run it from the form.  The data should populate the following fieids in a subform

PROG_ENG with a name
 P51 P62 RSS SPS XHG XHS (in APZ field) with the count of sortcode for each of the APZ types

I can view this information in a graph and table using the following code
 ElseIf Me.optPerformance = 7 Then
   
    strSQL = "TRANSFORM Count(CONTROL1.SORTCODE) AS CountOfSORTCODE" _
            & " SELECT CONTROL1.PROJ_ENG FROM MEMOID, (BASEPLAN INNER JOIN CONTROL1 ON BASEPLAN.SORTCODE = CONTROL1.SORTCODE) INNER JOIN Modules ON BASEPLAN.SORTCODE = Modules.SORTCODE" _
            & " WHERE CONTROL1.PROJ_ENG Not In ('ROSS','ECVN','AFRY','AWBL','BLTS','JSHT','PRAY','AAJN','DYKI','DCON','ECBR') AND CONTROL1.ABPACK Between #" & Format(dteFirst, "mm\/dd\/yy") & "# And #" & Format(dteSecond, "mm\/dd\/yy") & "#" _
            & " GROUP BY CONTROL1.PROJ_ENG" _
            & " PIVOT BASEPLAN.APZ;"
           
    Me.gphPerformance.RowSource = strSQL

You will notice that the criteria are different, but the principle is the same.

Any more suggestions for getting around this syntax error appriciated.

THanks Sicando



0
 
LVL 10

Accepted Solution

by:
paasky earned 70 total points
ID: 2527164
could you post your table definition here (just the fields used in your sql)...
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 10

Expert Comment

by:paasky
ID: 2527178
seems that there's something wrong with these lines:

....
            & " SELECT CONTROL1.PROJ_ENG FROM MEMOID, (BASEPLAN INNER JOIN CONTROL1 ON BASEPLAN.SORTCODE = CONTROL1.SORTCODE) INNER JOIN Modules ON BASEPLAN.SORTCODE = Modules.SORTCODE" _
....

but it would help me to understand better if I could see the tables and fields..

Paasky
0
 
LVL 10

Expert Comment

by:brewdog
ID: 2527486
No, your last post doesn't have any problem. sicando is using a table Control1 but doesn't join it to any of the other tables. Check out the WHERE statement.

So, sicando, this one works:

TRANSFORM Count(CONTROL1.SORTCODE) AS CountOfSORTCODE
SELECT CONTROL1.PROJ_ENG
FROM MEMOID, (BASEPLAN INNER JOIN CONTROL1 ON BASEPLAN.SORTCODE = CONTROL1.SORTCODE) INNER JOIN Modules ON BASEPLAN.SORTCODE = Modules.SORTCODE
WHERE CONTROL1.PROJ_ENG Not In ('ROSS','ECVN','AFRY','AWBL','BLTS','JSHT','PRAY','AAJN','DYKI','DCON','ECBR') AND CONTROL1.ABPACK Between #" & Format(dteFirst, "mm\/dd\/yy") & "# And #" & Format(dteSecond, "mm\/dd\/yy") & "#
GROUP BY CONTROL1.PROJ_ENG
PIVOT BASEPLAN.APZ;
                               
but this one doesn't:

TRANSFORM [Count(BASEPLAN.SORTCODE)] AS HOST
SELECT CONTROL1.PROJ_ENG
FROM (BASEPLAN INNER JOIN CONTROL1 ON BASEPLAN.SORTCODE = CONTROL1.SORTCODE) INNER JOIN Modules ON BASEPLAN.SORTCODE = Modules.SORTCODE
WHERE CONTROL1.ABPACK Between #" & Format(dteFirst, "mm\/dd\/yy") & "# And #" & Format(dteSecond, "mm\/dd\/yy") & "#
AND CONTROL1.PROJ_ENG = '" & strEngName & "'"
GROUP BY CONTROL1.PROJ_ENG
PIVOT BASEPLAN.APZ

Three things I'll say:

1. Remove the brackets from around the Count, etc., in the Transform statement that isn't working. Access usually assumes that anything in brackets is either a field or table name, and it might be getting confused.

2. You had extra commas at the end of both the first two lines, after the Transform statement and the Select statement. But you only have one field, so ditch the comma at the end of the second line, too.

3. Have you tried to run the query from the database window first? I usually do that to make sure the SQL string makes sense to Access before I throw it into code.

Hope these ideas help . . .

brewdog
0
 

Author Comment

by:sicando
ID: 2527718
By inserting the above line in the statement I am now able to view the record count on the form.  

MEMOID is a seperate table with employee ids as one field and their names as the other.

Modules is another unrelated table.

For some reason this seems to work.  I guess I have to now check the validity of my query!!

One last thing, if possible.  The fields that do not return a result are displaying "Name?".  How can I set the field so when a null value is returned it is just blank?

Thanks

Sicando  
0
 

Author Comment

by:sicando
ID: 2527733
You're right there is an extra comma.  I feel really stupid!!  However this still does not have the query running.  I am still recieving the same error when I run it from the form.  The data should populate the following fieids in a subform

PROG_ENG with a name
 P51 P62 RSS SPS XHG XHS (in APZ field) with the count of sortcode for each of the APZ types

I can view this information in a graph and table using the following code
 ElseIf Me.optPerformance = 7 Then
   
    strSQL = "TRANSFORM Count(CONTROL1.SORTCODE) AS CountOfSORTCODE" _
            & " SELECT CONTROL1.PROJ_ENG FROM MEMOID, (BASEPLAN INNER JOIN CONTROL1 ON BASEPLAN.SORTCODE = CONTROL1.SORTCODE) INNER JOIN Modules ON BASEPLAN.SORTCODE = Modules.SORTCODE" _
            & " WHERE CONTROL1.PROJ_ENG Not In ('ROSS','ECVN','AFRY','AWBL','BLTS','JSHT','PRAY','AAJN','DYKI','DCON','ECBR') AND CONTROL1.ABPACK Between #" & Format(dteFirst, "mm\/dd\/yy") & "# And #" & Format(dteSecond, "mm\/dd\/yy") & "#" _
            & " GROUP BY CONTROL1.PROJ_ENG" _
            & " PIVOT BASEPLAN.APZ;"
           
    Me.gphPerformance.RowSource = strSQL

You will notice that the criteria are different, but the principle is the same.

Any more suggestions for getting around this syntax error appriciated.

THanks Sicando



0
 
LVL 10

Expert Comment

by:brewdog
ID: 2527839
So we have solved your original problem, right? What was it that did it? Do you know?

As for the #NAME! you're seeing on screen, that usually isn't because of Null values but because Access isn't recognizing a field name or expression. Could that be the case here?
0
 

Author Comment

by:sicando
ID: 2527932
Hi Brewdog,

Do you remember scottsanpedro? I work with him.  He says Hi.  We always find your help informative and friendly.  Thanks.

The solved version now reads:

strSQL = "TRANSFORM Count(BASEPLAN.SORTCODE)" _
  & " SELECT CONTROL1.PROJ_ENG FROM MEMOID, (BASEPLAN INNER JOIN CONTROL1 ON BASEPLAN.SORTCODE = CONTROL1.SORTCODE) INNER JOIN Modules ON BASEPLAN.SORTCODE = Modules.SORTCODE" _
 & " WHERE CONTROL1.ABPACK Between #" & Format(dteFirst, "mm\/dd\/yy") & "# And #" & Format(dteSecond, "mm\/dd\/yy") & "#" _
 & " AND CONTROL1.PROJ_ENG = '" & strEngName & "'" _
 & " GROUP BY CONTROL1.PROJ_ENG " _
 & " PIVOT BASEPLAN.APZ"

I'm still not convinced I am using the right tables, but I guess that is my problem.  When testing in the database, the SQL view automatically draws reference to MEMOID and Modules tables, although they are not specified in the crosstab query design view.  It works, which is a break through, however I'm wondering a)why and b)is a valid query.

What do you reckon?

The #NAME? IS appearing when there are no records for that field. I have tested this for all the fields.  I have also compared this subform with others on the same form where a null value results in the field being left blank.


Shall I open this as a seperate question for you?

Sicando




0
 

Author Comment

by:sicando
ID: 2527933
Hi Brewdog,

Do you remember scottsanpedro? I work with him.  He says Hi.  We always find your help informative and friendly.  Thanks.

The solved version now reads:

strSQL = "TRANSFORM Count(BASEPLAN.SORTCODE)" _
  & " SELECT CONTROL1.PROJ_ENG FROM MEMOID, (BASEPLAN INNER JOIN CONTROL1 ON BASEPLAN.SORTCODE = CONTROL1.SORTCODE) INNER JOIN Modules ON BASEPLAN.SORTCODE = Modules.SORTCODE" _
 & " WHERE CONTROL1.ABPACK Between #" & Format(dteFirst, "mm\/dd\/yy") & "# And #" & Format(dteSecond, "mm\/dd\/yy") & "#" _
 & " AND CONTROL1.PROJ_ENG = '" & strEngName & "'" _
 & " GROUP BY CONTROL1.PROJ_ENG " _
 & " PIVOT BASEPLAN.APZ"

I'm still not convinced I am using the right tables, but I guess that is my problem.  When testing in the database, the SQL view automatically draws reference to MEMOID and Modules tables, although they are not specified in the crosstab query design view.  It works, which is a break through, however I'm wondering a)why and b)is a valid query.

What do you reckon?

The #NAME? IS appearing when there are no records for that field. I have tested this for all the fields.  I have also compared this subform with others on the same form where a null value results in the field being left blank.


Shall I open this as a seperate question for you?

Sicando




0
 
LVL 10

Expert Comment

by:brewdog
ID: 2527972
Tell Scott hi for me. Always a pleasure working with quality guys like you two. :o)

**I'm still not convinced I am using the right tables, but I guess that is my problem.

Yeah, unfortunately I can't answer that one for you. :o)

**When testing in the database, the SQL view automatically draws reference to MEMOID and Modules tables, although they are not specified in the crosstab query design view.

By references do you mean relationships/links? And wait a minute, you don't add those tables into the query design but they show up in the SQL view?

**It works, which is a break through, however I'm wondering a) why and b) is it a valid query. What do you reckon?

The query syntax is fine, so that's why the query works. Like you said, it may not be pulling the information you want, but it is syntactically correct. I'll leave (b) up to you.

**The #NAME? IS appearing when there are no records for that field. I have tested this for all the fields.  I have also compared this subform with others on the same form where a null value results in the field being left blank.

Are the fields in the other subform (where blanks appear rather than #NAME!) (a) based off the same kind of Crosstab query and (b) bound fields (having a ControlSource specified in advance)?

**Shall I open this as a seperate question for you?

That's up to you. I'm not exactly sure how to proceed with the different subforms/different results idea right now, so it might be best to put that as a separate question. If I were you, though, I'd work on making sure the query is pulling what you want and how you want it first, so that you know exactly why the query works as you have built it. That will give you a better handle on explaining your situation.

brewdog
0
 

Author Comment

by:sicando
ID: 2528044
The fields in the other subforms are all from select queries.  The one I am dealing with here is the only crosstab query.  Its the first time I have tried to incoporate a crosstab query in a sub procedure.  All fields are bound but the record sourse in not defined. I had to do this for it to work.

I have to go to college now so I will say thank you.  

Paasky pointed me in the right direction originally and in that way helped me solve the original problem.  Yuor help is greatly appriciated, but I should award the points to him.

I will carry on the NAME? thing in a seperate question tomorrow if it hassles me.

Cheers

Simon


0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question