Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-14-2015, 07:40 AM   #1
nstratton
Newly Registered User
 
Join Date: Aug 2015
Location: AL, USA
Posts: 85
Thanks: 9
Thanked 0 Times in 0 Posts
nstratton is on a distinguished road
Set SQL through VBA

I am attempting to build SQL on a form load that will populate a chart on the form. The form is unbound with its sole purpose to hold the chart, no other information is need on the form. I am using the below code to get it together however I keep running into Error 3143 Syntax Error in TRANSFORM Statement.

This is a testing type of form and I just copied the SQL over from a form that I know works without using VBA to build it. The difference being I am wanting to use a multi-select listbox to narrow down the results. The bolded line is where the debugger takes me but that doesn't really help me any.

There is more code under this but it is used for formatting the chart and not relevant to the issue.
Note: The database is being built in Access 2010 desktop version.

Code:
Private Sub Form_Load()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim chtWeek As Chart

'Set chtWeek = Me.Graph0.Object
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryTest")

For Each varItem In Forms!frmChartMenu!lstSuppliers.ItemsSelected
   strCriteria = strCriteria & ",'" & Forms!frmChartMenu!lstSuppliers.ItemData(varItem) & "'"
Next varItem

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "PARAMETERS [Forms]![frmChartMenu]![lstSuppliers] Text ( 255 ), [Forms]![frmChartMenu]![txtTargets] Text ( 255 );" & _
         "TRANSFORM Count(*) As [Count]" & _
         "SELECT Format([FileDate],'Short Date') AS Expr1, [Forms]![frmChartMenu]![txtTargets] AS [DailyGoal] " & _
         "FROM tblRawData" & _
         "WHERE tblRawData.Supplier IN(" & strCriteria & ")" & _
         "GROUP BY (Format([FileDate],'Short Date')), (Int([FileDate])), [Forms]![frmChartMenu]![txtTargets]" & _
         "PIVOT qryTest.[Supplier];"

         
         
qdf.SQL = strSQL

DoCmd.OpenQuery "qryTest"

Set db = Nothing
Set qdf = Nothing

nstratton is offline   Reply With Quote
Old 09-14-2015, 07:50 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,356
Thanks: 159
Thanked 1,706 Times in 1,676 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Set SQL through VBA

Put a debug.print strSQL in before you set the qdf. I think it's because you can't refer to the form controls in SQL , it doesn't understand them, as they are an access object.
Set the parameters as variables outside the StrSQL definition then add those variables in to your SQL statement.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 09-14-2015, 07:53 AM   #3
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,705
Thanks: 2
Thanked 2,073 Times in 2,028 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Set SQL through VBA

Use a Debug.Print to find out if the strSQL is correct setup.
Take the output from the Debug.Print (from the Immediate window) and paste it into a new query.

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
nstratton (09-16-2015)
Old 09-14-2015, 08:03 AM   #4
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,066
Thanks: 79
Thanked 2,003 Times in 1,951 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Set SQL through VBA

As the others have said, you have a mixture of variables and form controls.
You need to "render" the form control values before substituting into the SQL.
Also, in the strSQL, I would start each line with a space character
eg
Code:
 " FROM tblRawData" & _
 " WHERE tblRawData.Supplier IN(" & strCriteria & ")" & _
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 09-14-2015, 08:38 AM   #5
nstratton
Newly Registered User
 
Join Date: Aug 2015
Location: AL, USA
Posts: 85
Thanks: 9
Thanked 0 Times in 0 Posts
nstratton is on a distinguished road
Re: Set SQL through VBA

So, for example, everytime I need to use the listbox, in SQL I would define it as strCriteria? Even in the parameters section?
When I posted the immediate window into a new query, I appeared as one block of jumbled mess. Once I put everything on its own line I discovered an issue with the PIVOT line. Corrected that and the query ran correctly. Made the same change in VBA with no luck.
nstratton is offline   Reply With Quote
Old 09-14-2015, 08:45 AM   #6
nstratton
Newly Registered User
 
Join Date: Aug 2015
Location: AL, USA
Posts: 85
Thanks: 9
Thanked 0 Times in 0 Posts
nstratton is on a distinguished road
Re: Set SQL through VBA

What I ended up doing was adding a Chr(13) & Chr(10) to the end of the PARAMETERS line and it worked. Not really sure why that worked.

Since that worked like expected, how do I make it the rowsource for a chart?
I assume it is Me.GraphName.RecordSource = strSQL or something close to it.
The charts won't work if the main query is in crosstab format (or I can't get it to at least) so I would need the main query to remain SELECT and then the chart by formatted as CROSSTAB

Last edited by nstratton; 09-14-2015 at 10:04 AM. Reason: Follow Up Question
nstratton is offline   Reply With Quote
Old 09-14-2015, 08:27 PM   #7
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,705
Thanks: 2
Thanked 2,073 Times in 2,028 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Set SQL through VBA

Quote:
Originally Posted by nstratton View Post
..
When I posted the immediate window into a new query, I appeared as one block of jumbled mess. Once I put everything on its own line I discovered an issue with the PIVOT line. Corrected that and the query ran correctly.
Exactly what I expected.
Quote:
Originally Posted by nstratton View Post
..
The charts won't work if the main query is in crosstab format ...
It shouldn't be a problem or else I'm misunderstanding what you're writing.
Post a stripped down version of your database with some sample data + name of the form.

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 09-15-2015, 04:16 AM   #8
nstratton
Newly Registered User
 
Join Date: Aug 2015
Location: AL, USA
Posts: 85
Thanks: 9
Thanked 0 Times in 0 Posts
nstratton is on a distinguished road
Re: Set SQL through VBA

I have attached the database. I removed everything but what this question is dealing with. Since my post I learned I have actually been building the charts wrong and I am not sure if the code I posted will help me anymore.

I am apparently supposed to be building at least 7 different charts broken down by various criteria then 1 extra where they can pick their own criteria which is where the listbox came in.

I have attached a powerpoint of the different graph types they want. All of which need to be able to be filtered by at least a date range. The goals are calculated by Production Days * Daily Target which are fields on the ChartMenu form.

If there is a way to create these charts without having to create a crazy amount of them for every situation imaginable I would greatly appreciate some guidance.

I feel like I didn't explain my process of what I have been doing to create the charts. I have a base query, in this case qryCharts that all charts are based on initially to use the Chart Wizard. I then go into the chart rowsource and adjust it as needed. So what ends up happening is the base query remains select and the chart rowsource query becomes crosstab or I assume it will in some cases. Any changes I make to the base query do not always make it to the chart because I modify its rowsource.
Attached Files
File Type: accdb Database1.0 - Copy.accdb (1.79 MB, 44 views)
File Type: zip FTTQ Srummary Report 09032015.zip (345.4 KB, 38 views)

Last edited by nstratton; 09-15-2015 at 07:18 AM. Reason: Explanation of what I have been doing
nstratton is offline   Reply With Quote
Old 09-15-2015, 09:08 PM   #9
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,705
Thanks: 2
Thanked 2,073 Times in 2,028 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Set SQL through VBA

Quote:
Originally Posted by nstratton View Post
I have attached the database. I removed everything but what this question is dealing with. Since my post I learned I have actually been building the charts wrong and I am not sure if the code I posted will help me anymore.
If this is so, then this thread probably not have the same relevance as originally where your question was how to set SQL using VBA.
And also your second problem "The graph will not work if the main query is in Crosstab format" which was what I responded to.

I can't help you building the reports you want shown in the Powerpoint file, I don't know your data good enough and I don't have the time, sorry.
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 09-16-2015, 02:58 AM   #10
nstratton
Newly Registered User
 
Join Date: Aug 2015
Location: AL, USA
Posts: 85
Thanks: 9
Thanked 0 Times in 0 Posts
nstratton is on a distinguished road
Re: Set SQL through VBA

It's all good. I appreciate your input. The original question was answered so I will mark it solved and give the thanks where appropriate. The rest was just pushing my luck and seeing what the geniuses on this site might be able to come up with. As you could probably see, the data is not very friendly for charts since there are no actual numbers and having to use counts and such. Thank you everyone, you guys have helped tremendously in my first database project and hopefully not last

nstratton is offline   Reply With Quote
Reply

Tags
access 2010 , charts , sql vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 07:28 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World