Solved

SQL: error in transform statement

Posted on 2000-02-16
12
497 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
  • 6
  • 3
  • 3
12 Comments
 
LVL 10

Expert Comment

by:paasky
Comment Utility
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
Comment Utility
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
Comment Utility
could you post your table definition here (just the fields used in your sql)...
0
 
LVL 10

Expert Comment

by:paasky
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:sicando
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
compress/resize an image to fit a control in ms access 3 23
Access 2010 3 15
Access Date Query 28 29
DCount using "OR" 4 16
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now