SQL: error in transform statement

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

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

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

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

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  

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

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?

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

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

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

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