|
 |
access thread: using a combobox value on a chart
Message #1 by "dwight Goossens" <dwight_goossens@g...> on Thu, 28 Nov 2002 10:57:37
|
|
Hi,
I'm having problems with a form where i have a combobox that's filled with
data from a textfield.
So this is the code i use for the combobox after_update event:
Dim strsql As String
strsql = "TRANSFORM Sum([CountOfMACHINE]) AS [SumOfCountOfMACHINE] SELECT
[MACHINE] FROM " _
& "[q_aantal machines_pe_afdeling] GROUP BY [MACHINE] PIVOT
[AFDELING] " _
& "HAVING ((tblAFDELING.AFDELING=""" & CStr(Me.Combo22.Text)
& """));"
Debug.Print strsql
Me.Graph29.RowSource = strsql
Me.Graph29.Requery
And this is the debug output:
TRANSFORM Sum([CountOfMACHINE]) AS [SumOfCountOfMACHINE] SELECT [MACHINE]
FROM [q_aantal machines_pe_afdeling] GROUP BY [MACHINE] PIVOT [AFDELING]
HAVING ((tblAFDELING.AFDELING="Zinkroom"));
The comobox value is correct but I get this error:
Syntax error (missing operator) in query expression '[AFDELING] HAVING
(((tblAFDELING.AFDELING) = "Zinkroom"))'.
What syntax error do I have?
Message #2 by "Bob Bedell" <bobbedell15@m...> on Thu, 28 Nov 2002 17:15:36 +0000
|
|
Hi Dwight,
The TRANSFORM statement doesn't support the HAVING clause. If you're
just trying to limit the columns displayed, you can use the IN predicate
as in:
PIVOT [AFDELING]IN ("Zinkroom");
Best,
Bob
>From: "dwight Goossens" <dwight_goossens@g...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] using a combobox value on a chart
>Date: Thu, 28 Nov 2002 10:57:37
>
>Hi,
>
>I'm having problems with a form where i have a combobox that's filled with
>data from a textfield.
>
>So this is the code i use for the combobox after_update event:
>Dim strsql As String
>
>strsql = "TRANSFORM Sum([CountOfMACHINE]) AS [SumOfCountOfMACHINE] SELECT
>[MACHINE] FROM " _
> & "[q_aantal machines_pe_afdeling] GROUP BY [MACHINE] PIVOT
>[AFDELING] " _
> & "HAVING ((tblAFDELING.AFDELING=""" & CStr(Me.Combo22.Text)
>& """));"
>
>Debug.Print strsql
>Me.Graph29.RowSource = strsql
>Me.Graph29.Requery
>
>And this is the debug output:
>TRANSFORM Sum([CountOfMACHINE]) AS [SumOfCountOfMACHINE] SELECT [MACHINE]
>FROM [q_aantal machines_pe_afdeling] GROUP BY [MACHINE] PIVOT [AFDELING]
>HAVING ((tblAFDELING.AFDELING="Zinkroom"));
>
>The comobox value is correct but I get this error:
>Syntax error (missing operator) in query expression '[AFDELING] HAVING
>(((tblAFDELING.AFDELING) = "Zinkroom"))'.
>
>What syntax error do I have?
_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
Message #3 by "Dwight Goossens" <Dwight_Goossens@g...> on Fri, 29 Nov 2002 10:54:34 +0100
|
|
Hi Bob,
You are right. Now I don't get the error. but the data is not shown right.
The idea is to show a chart depending on the field "tblAFDELING.AFDELING"
(which comes from the combobox) and it has to show the sum of machines and
the machine names from the table tblMACHINE.MACHINE.
This is the code i got now:
Private Sub Form_Load()
Dim strSQL As String
Me.Combo22.SetFocus
strSQL = "TRANSFORM Sum([CountOfMACHINE]) AS [SumOfCountOfMACHINE] SELECT
tblAFDELING.AFDELING, Count(tblMACHINE.MACHINE) AS CountOfMACHINE " _
& "FROM tblMACHINE INNER JOIN (tblAFDELING INNER JOIN tblPROJECT ON
tblAFDELING.ID = " _
& "tblPROJECT.tblAFDELING_ID) ON tblMACHINE.ID
tblPROJECT.tblMACHINE_ID " _
& "GROUP BY tblMACHINE.MACHINE, tblAFDELING.AFDELING " _
& "PIVOT [AFDELING] IN (tblAFDELING.AFDELING=""" &
CStr(Me.Combo22.Text) & """);"
Debug.Print strSQL
Me.Graph30.RowSource = strSQL
Me.Graph30.Requery
End Sub
I've added the mdb. I'm stuck with this problem for two weeks now so I
would appreciate it if you could take a look.
It's the form called "frm_grafiek_aantal_machines_per_afdeling"
(See attached file: archief.zip)
Thanks in advance,
***********************************************************************
Dwight Goossens
mailto: dwight_goossens@g...
tel:+xx xxx/xx.21.37
***********************************************************************
"Bob Bedell"
<bobbedell15@ To: "Access" <access@p...>
msn.com> cc:
Subject: [access] Re: using a combobox value on a chart
28/11/2002
18:15
Please
respond to
"Access"
Hi Dwight,
The TRANSFORM statement doesn't support the HAVING clause. If you're
just trying to limit the columns displayed, you can use the IN predicate
as in:
PIVOT [AFDELING]IN ("Zinkroom");
Best,
Bob
>From: "dwight Goossens" <dwight_goossens@g...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] using a combobox value on a chart
>Date: Thu, 28 Nov 2002 10:57:37
>
>Hi,
>
>I'm having problems with a form where i have a combobox that's filled with
>data from a textfield.
>
>So this is the code i use for the combobox after_update event:
>Dim strsql As String
>
>strsql = "TRANSFORM Sum([CountOfMACHINE]) AS [SumOfCountOfMACHINE] SELECT
>[MACHINE] FROM " _
> & "[q_aantal machines_pe_afdeling] GROUP BY [MACHINE] PIVOT
>[AFDELING] " _
> & "HAVING ((tblAFDELING.AFDELING=""" & CStr(Me.Combo22.Text)
>& """));"
>
>Debug.Print strsql
>Me.Graph29.RowSource = strsql
>Me.Graph29.Requery
>
>And this is the debug output:
>TRANSFORM Sum([CountOfMACHINE]) AS [SumOfCountOfMACHINE] SELECT [MACHINE]
>FROM [q_aantal machines_pe_afdeling] GROUP BY [MACHINE] PIVOT [AFDELING]
>HAVING ((tblAFDELING.AFDELING="Zinkroom"));
>
>The comobox value is correct but I get this error:
>Syntax error (missing operator) in query expression '[AFDELING] HAVING
>(((tblAFDELING.AFDELING) = "Zinkroom"))'.
>
>What syntax error do I have?
_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
Message #4 by "Bob Bedell" <bobbedell15@m...> on Fri, 29 Nov 2002 16:03:03 +0000
|
|
Hi Dwight,
The P2P list doesn't accept attachments, but you can e-mail me directly
at bobbedell15@m... Happy to help if I can.
Best,
Bob
>From: "Dwight Goossens" <Dwight_Goossens@g...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: using a combobox value on a chart
>Date: Fri, 29 Nov 2002 10:54:34 +0100
>
>
>Hi Bob,
>
>You are right. Now I don't get the error. but the data is not shown right.
>The idea is to show a chart depending on the field "tblAFDELING.AFDELING"
>(which comes from the combobox) and it has to show the sum of machines and
>the machine names from the table tblMACHINE.MACHINE.
>
>This is the code i got now:
>
>Private Sub Form_Load()
>Dim strSQL As String
>
>Me.Combo22.SetFocus
>
>strSQL = "TRANSFORM Sum([CountOfMACHINE]) AS [SumOfCountOfMACHINE] SELECT
>tblAFDELING.AFDELING, Count(tblMACHINE.MACHINE) AS CountOfMACHINE " _
> & "FROM tblMACHINE INNER JOIN (tblAFDELING INNER JOIN tblPROJECT
>ON
>tblAFDELING.ID = " _
> & "tblPROJECT.tblAFDELING_ID) ON tblMACHINE.ID
>tblPROJECT.tblMACHINE_ID " _
> & "GROUP BY tblMACHINE.MACHINE, tblAFDELING.AFDELING " _
> & "PIVOT [AFDELING] IN (tblAFDELING.AFDELING=""" &
>CStr(Me.Combo22.Text) & """);"
>
>
>Debug.Print strSQL
>Me.Graph30.RowSource = strSQL
>Me.Graph30.Requery
>End Sub
>
>I've added the mdb. I'm stuck with this problem for two weeks now so I
>would appreciate it if you could take a look.
>It's the form called "frm_grafiek_aantal_machines_per_afdeling"
>
>(See attached file: archief.zip)
>
>Thanks in advance,
>
>***********************************************************************
>Dwight Goossens
>mailto: dwight_goossens@g...
>tel:+xx xxx/xx.21.37
>***********************************************************************
>
>
>
> "Bob Bedell"
> <bobbedell15@ To: "Access"
><access@p...>
> msn.com> cc:
> Subject: [access] Re: using a
>combobox value on a chart
> 28/11/2002
> 18:15
> Please
> respond to
> "Access"
>
>
>
>
>
>
>Hi Dwight,
>
>The TRANSFORM statement doesn't support the HAVING clause. If you're
>just trying to limit the columns displayed, you can use the IN predicate
>as in:
>
>PIVOT [AFDELING]IN ("Zinkroom");
>
>Best,
>
>Bob
>
>
> >From: "dwight Goossens" <dwight_goossens@g...>
> >Reply-To: "Access" <access@p...>
> >To: "Access" <access@p...>
> >Subject: [access] using a combobox value on a chart
> >Date: Thu, 28 Nov 2002 10:57:37
> >
> >Hi,
> >
> >I'm having problems with a form where i have a combobox that's filled
>with
> >data from a textfield.
> >
> >So this is the code i use for the combobox after_update event:
> >Dim strsql As String
> >
> >strsql = "TRANSFORM Sum([CountOfMACHINE]) AS [SumOfCountOfMACHINE] SELECT
> >[MACHINE] FROM " _
> > & "[q_aantal machines_pe_afdeling] GROUP BY [MACHINE] PIVOT
> >[AFDELING] " _
> > & "HAVING ((tblAFDELING.AFDELING=""" & CStr(Me.Combo22.Text)
> >& """));"
> >
> >Debug.Print strsql
> >Me.Graph29.RowSource = strsql
> >Me.Graph29.Requery
> >
> >And this is the debug output:
> >TRANSFORM Sum([CountOfMACHINE]) AS [SumOfCountOfMACHINE] SELECT [MACHINE]
> >FROM [q_aantal machines_pe_afdeling] GROUP BY [MACHINE] PIVOT
>[AFDELING]
> >HAVING ((tblAFDELING.AFDELING="Zinkroom"));
> >
> >The comobox value is correct but I get this error:
> >Syntax error (missing operator) in query expression '[AFDELING] HAVING
> >(((tblAFDELING.AFDELING) = "Zinkroom"))'.
> >
> >What syntax error do I have?
>
>
>_________________________________________________________________
>STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
>http://join.msn.com/?page=features/junkmail
>
>
>
>
>
>
_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus
|
|
|
View Cart
