Solved

SQL: error in transform statement

Posted on 2000-02-16
12
507 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 70 total points
ID: 2527164
could you post your table definition here (just the fields used in your sql)...
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

809 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