Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL: error in transform statement

Posted on 2000-02-16
12
Medium Priority
?
528 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
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 140 total points
ID: 2527164
could you post your table definition here (just the fields used in your sql)...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

926 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