SQL statements from VBA

[John Southern]

I am in need of help to produce code to give mw a SQL string that produces the right result from a query.

Here is the code I am using

 
Sub addBudget(FROM1)
Dim MName as String
Mname = MonthName(Month(FROM1), True)
strSQL = "SELECT Budget.F1, Budget." & Mname & "," & "Mname" & " & [Expr1] & [F1] As Expr2 INTO SelBudget FROM Budget"
CurrentDb.QueryDefs("QryMonthBudget").SQL = strSQL
DoCmd.OpenQuery "QryMonthBudget"
End Sub

The table "Budget" has columns headed with abbreviated Month names and rows headed with sales catagories (F1). The data being the budgeted sales each month for each catagory.

The query is simply designed to give the budget for each catagory for a selected month.
My problem concerns Expr2. This is meant to be a string containing the abbreviated month name,the year (Expr1), and the sales catagory (F1).


My code causes the query to take"Mname" as a parameter. If I remove the inner quotes then the query takes Mname to be the same as Budget.Mname and gives the value of that field.
How should I write the code ?
Any suggestions would be highly appreciated.
John

[TheShabz]

your SQL statement as written written as if it wasn't in VBA is (parentheses around form objects denoting variables)

SELECT Budget.F1, Budget.(Mname), Mname & [Expr1] & [F1] As Expr2 INTO SelBudget FROM Budget

Just going off your post, I dont know what you want done (SQL-wise). If you can, make the query you want in the query builder and paste in the SQL. i'll format it to fit in your VBA.

[John Southern]

Thanks for your response.
Here is a relevant SQL statement copied from an MSQuery grid which did the job

SELECT Budget.F1, Budget.FEB, "FEB" AS Expr1, Year(Now()) AS Expr2, [Expr1] & [Expr2] & [F1] AS Expr3
FROM Budget;

When entered via VBA the FEB in Budget.FEB is derived from an entered date. The "FEB" I have put in manualy in the above SQL,but it needs to be derived from the same entered date, and this is where I am stuck.
Expr3 is a string which I need as a link later on in the project.
Hope you can help
John

[TheShabz]

so what is FEB will be entered from Mname on the form. for that you want

strSQL = "SELECT Budget.F1, Budget." & Me.Mname & ", ""FEB"" AS Expr1, Year(Now()) AS Expr2, [Expr1] & [Expr2] & [F1] AS Expr3" & _
"FROM Budget"

The resulting recordset would contain fields:
F1
FEB
Expr1 (every record will hold the value "FEB")
Expr2 (holding the current year)
Expr3 ("FEB"yearF1)

Now, a few things I would change:
1. rename those Expr fields as something relevant.
2. if FEB is referring to February, consider making that dynamic so you wont have to change the month every time.

[John Southern]

Thanks for your suggestion, the VBA protested at the Me. but when that was removed all was well. Your second point about the "Feb" being dynamic gets me to the crux of my problem. If I put the variable Mname into the SQL string in my code then instead of the String "Jan" or "Feb"
or whichever month it is, which I want, I get the values in Budget.Jan (or Budget.Feb or whatever) as the query result for Expr1. If I put the variable Mname into the code with double quotes I get the the string "Mname" as the value of Expr1.

In otherwords I cant find a way of coding the SQL string to give me the result of the dynamic month name.
John

[TheShabz]

First off, I'm curious as to why Me.Mname was throwing it off. Is Mname not an object on a form that this whole thing is being run from? Without the Me. section, budget.Mname would be referring to the Mname field in a table named budget. Do you have a field in budget named Mname? If so, then that should be all you need. You'll get a field named Mname with the corresponding month.

Let's try another angle here. Let me know the goal of this query. Tell me what F1, Mname and all your Expr fields are meant to do. Also tell me where this information is coming from and what is being used to execute this query. I think I'll have a clearer picture then.

[John Southern]

Thanks for your patience. The purpose of the whole exercise is to produce a report which shows This years sales, Last years Sale and Budget sales for any given month chosen by the user. Because the sales statistics and the budget are only available in slightly incompatible formats I need to derive a field _Month&Year&Sales_ for the budget to act as a linking field when I produce the final report.
The table budget looks like this :-

	DEC	       JAN	         FEB	          MAR   
MER	27033	33791	33791	43718
FLM	91793	114741	114741	148446
GUM	46306	57883	57883	66565
LAB	38189	47736	47736	47480
Carriage	610	762	762	919
Artwork	4270	5337	5337	6430
Total	208201	260251	260251	313557

The purpose of the query is for the user to choose a month (FROM1 is the start date of that month Nmonth is derived from it) so that the sales value for that month for each of the sales categories (F1) is shown along with the year and the name of the month. so the result should look like:-

MER		33791	JAN	2010	JAN2010MER	
FLM		114741	JAN	2010	JAN2010FLM	
GUM		57883	JAN	2010	JAN2010GUM	
LAB		47736	JAN	2010	JAN2010LAB	
Carriage		762	JAN	2010	JAN2010Carriage	
Artwork		5337	JAN	2010	JAN2010Artwork	
Total		260251	JAN	2010	JAN2010Total

The variable FROM1 is also used to pluck the relevant data from the sales info where I have not had the same problem producing a field like the fifth field above.
I hope this makes the problem ( to find a way to produce the third field "JAN" above so it can be derived from the input date FROM1) clearer.
I am sorry this is such a long post, please hang on in there till we get it sorted!

[TheShabz]

How is the date criteria being provided to access by the user. Is there a form with a textbox/combobox/etc?

[John Southern]

The code below is fired by a command button on a "menu" form

 
Sub Comparisons()
Dim FROM1 As Date
Dim FROM2 As Date
Dim TO1 As Date
Dim TO2 As Date
Dim strSQL As String
Dim strSQL2 As String
FROM1 = InputBox("Enter Date from")
 
 
TO1 = InputBox("Enter Date to")
 
FROM2 = DateAdd("yyyy", -1, CDate(FROM1))
TO2 = DateAdd("yyyy", -1, CDate(TO1))
Call addBudget(FROM1)

What I am trying to do is to enable the user to select any period , FROM1 to TO1, then derive the same period from last year FROM2 and TO2, and then get the relevant budget figures, link them all up by the concatenated string I have talked about and produce a report comparing sales by catagory for a period this year, the same period last year and the budgeted sales for the same period.

John

[TheShabz]

Ok then I assume addBudget(FROM1) is where Nmonth is generated. Now thinking about it more, Expr1 is useless for you. to make it dynamic, just use Nmonth.

So
strSQL = "SELECT budget.F1, budget." & Nmonth & ", " & Nmonth & Year(Now()) & " & [F1] AS Expr2 INTO SelBudget FROM Budget"

that string should then become (assuming Nmonth = "Jan":
SELECT budget.F1, budget.JAN, JAN2010 & [F1] AS Expr2 INTO SelBudget FROM Budget

See if that works.

[John Southern]

Thanks for your reply.

When I tried your code I got a modification of the problem that led me to this forum in the first place.
The query treats the phrase " & NMonth & Year(Now()) & " as a parameter FEB2010 and asks for a value. If I enter the value "FEB2010" the query then gives me the right answer.
I suspect that this is just a matter of getting quote marks spaces and &'s in the right place. I have been struggling with translating SQL statements into VBA strings for a while now. can you recommend a useful book ?
John

[TheShabz]

I think we need to to go from:
JAN2010 & [F1] AS Expr2
to:
"JAN2010" & [F1] AS Expr2
this should do the trick:
strSQL = "SELECT budget.F1, budget." & Nmonth & ","" " & Nmonth & Year(Now()) & """ & [F1] AS Expr2 INTO SelBudget FROM Budget"

[John Southern]

I am very glad to be able to mark this thread as solved. Thanks for your help.

I would still like to know where I can find out about the general principles involved in writing SQL statements in VBA code. After all three sets of inverted commas in a row is a bit odd.