SQL VBA Mulitple Lines

[jj72uk]

Hi All,

Been scratching my head and trawling through google for the last hour with no luck.

I have a SQL query written for VBA but due to the length it covers more than one line:

Sub refreshform()
Dim xteam As Integer
Dim sSQL As String
xteam = InputBox(Prompt:="Team Number Please.")

DoCmd.RunSQL ("SELECT tbl_store_" & xteam & ".Date, tbl_store_" & xteam & ".Partner, tbl_store_" & xteam & ".User, Sum(IIf([tbl_store_" & xteam & ".Retained]="Yes",1,0)) AS Saves, Sum(IIf([tbl_store_" & xteam & ".Retained]="Yes",0,1)) AS [No], Count([tbl_store_" & xteam & ".Retained]) AS [Total Logged], ([Saves]+[stdrens])/([rencalls]+[retcalls]) AS [Call_To_Save %], Sum(IIf([tbl_store_" & xteam & ".Type]="Standard Renewal",1,0)) AS StdRens, Sum(IIf([tbl_lean_" & xteam & ".Call Type]="Renewal Call",1,0)) AS RenCalls, Sum(IIf([tbl_lean_" & xteam & ".Call Type]="Retention Call",1,0)) AS RetCalls, [rencalls]/([rencalls]+[retcalls]) AS [RCP %]
FROM tbl_store_" & xteam & " INNER JOIN tbl_lean_" & xteam & " ON (tbl_store_" & xteam & ".User = tbl_lean_" & xteam & ".User) AND (tbl_store_" & xteam & ".Date = tbl_lean_" & xteam & ".Date) AND (tbl_store_" & xteam & ".Time = tbl_lean_" & xteam & ".Time) AND (tbl_store_" & xteam & ".[Policy Number] = tbl_lean_" & xteam & ".[Policy Number])
WHERE (((tbl_store_" & xteam & ".TLID) Like [Forms]![Frm_Main]![Cbo_TeamLeader]) AND ((tbl_store_" & xteam & ".Product) Like [Forms]![Frm_Main]![Cbo_Product]))
GROUP BY tbl_store_" & xteam & ".Date, tbl_store_" & xteam & ".Partner, tbl_store_" & xteam & ".User
HAVING (((tbl_store_" & xteam & ".Date)=[Forms]![Frm_Main]![Txt_Date]) AND ((tbl_store_" & xteam & ".Partner) Like [Forms]![Frm_Main]![Cbo_Partner]));")
 
End Sub

I get a complie error.

Just to go through what I have tried.

I've tried adding " " across each line with a & _ on the end... doesnt work.

I've tried doing each line with sSQL = blah blah
and a obj.execute(sSQL) ..doesnt work

I've tried doing each line with sSQL =
followed by the next line sSQL = sSQL & .....

Doesnt work...

Any help would be greatly appricated.

 

[vbaInet]

Save the sql statement to a string variable, comment out the other lines, run the sub and print the variable to the Immediate Window using Debug.Print.

 

[jj72uk]

Sorry, that just flew over my head.

Can you expand a bit more?

 

[vbaInet]

Dim strSQL as String

strsql = "SELECT ..."

Debug.Print strSQL

Run the code and look in the immediate window what is printed.

 

[boblarson]

Okay, you have several issues. One, your bracketing has problems. Also you have at least one, if not two, fields that are named with Access Reserved Words (date is one of them and I'm not sure about User). So, those were put into square brackets. Also, you can't use double quotes for referring to things like 'Standard Renewal' - you need single quotes as shown. Then you also need delimiters for text and date fields. So, if you are referring to a form reference for a text field you need to use quotes. Also, dates require the use of octothorpes (#) and I've used them below. And using LIKE doesn't make sense unless you use the wild cards. Otherwise it is just EQUAL to. If you want to use LIKE you need to include wildcards and I've tried below - but not completely sure which are text and which are numeric fields.

Hopefully this helps:

Dim strSQL As String
 
strSQL = "SELECT tbl_store_" & xteam & ".[COLOR=red][B][[/B][/COLOR]Date[COLOR=red][B]][/B][/COLOR], tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Partner[COLOR=red][B]][/B][/COLOR], tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]User[COLOR=red][B]][/B][/COLOR], Sum(IIf(tbl_store_" & xteam & ".[COLOR=red][B][[/B][/COLOR]Retained]="Yes",1,0)) AS [URL="http://www.access-programmers.co.uk/forums/showthread.php?t=202696#"]Saves[/URL], [B][COLOR=red]" & _[/COLOR][/B]
"Sum(IIf(tbl_store_" & xteam & ".[COLOR=red][B][[/B][/COLOR]Retained]=[COLOR=red][B]'[/B][/COLOR]Yes[B][COLOR=red]'[/COLOR][/B],0,1)) AS [No], Count(tbl_store_" & xteam & ".[COLOR=red][B][[/B][/COLOR]Retained]) AS [Total Logged], ([Saves]+[stdrens])/([rencalls]+[retcalls]) AS [Call_To_Save %], [B][COLOR=red]" & _[/COLOR][/B]
"Sum(IIf(tbl_store_" & xteam & ".[COLOR=red][B][[/B][/COLOR]Type]=[COLOR=red][B]'[/B]Standard[/COLOR] Renewal[B][COLOR=red]'[/COLOR][/B],1,0)) AS StdRens, Sum(IIf(tbl_lean_" & xteam & ".[COLOR=red][B][[/B][/COLOR]Call Type]=[COLOR=red][B]'[/B][/COLOR]Renewal Call[B][COLOR=red]"[/COLOR][/B],1,0)) AS RenCalls, Sum(IIf(tbl_lean_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Call Type]=[B][COLOR=red]'[/COLOR][/B]Retention Call[B][COLOR=red]'[/COLOR][/B],1,0)) AS RetCalls, [rencalls]/([rencalls]+[retcalls]) AS [RCP %] [B][COLOR=red]" & _[/COLOR][/B]
"FROM tbl_store_" & xteam & " INNER JOIN tbl_lean_" & xteam & " ON (tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]User[B][COLOR=red]][/COLOR][/B] = tbl_lean_" & xteam & ".[B][COLOR=red][[/COLOR][/B]User[B][COLOR=red]][/COLOR][/B]) AND (tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Date[B][COLOR=red]][/COLOR][/B] = tbl_lean_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Date[B][COLOR=red]][/COLOR][/B]) AND (tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Time[B][COLOR=red]][/COLOR][/B] = tbl_lean_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Time[B][COLOR=red]][/COLOR][/B]) AND (tbl_store_" & xteam & ".[Policy Number] = tbl_lean_" & xteam & ".[Policy Number]) [B][COLOR=red]" & _[/COLOR][/B]
WHERE (((tbl_store_" & xteam & ".TLID) Like [COLOR=red][B]'*" &[/B][/COLOR] Forms!Frm_Main.Cbo_TeamLeader & [B][COLOR=red]"*'[/COLOR][/B]) AND ((tbl_store_" & xteam & ".Product) Like [B][COLOR=red]'*" &[/COLOR][/B] Forms!Frm_Main.Cbo_Product)) [B][COLOR=red]" & _[/COLOR][/B]
GROUP BY tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Date[B][COLOR=red]][/COLOR][/B], tbl_store_" & xteam & ".Partner, tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]User[B][COLOR=red]] " & _[/COLOR][/B]
HAVING (((tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Date[B][COLOR=red]][/COLOR][/B])=[COLOR=red][B]#" & [/B][/COLOR]Forms!Frm_Main.Txt_Date [B][COLOR=red]& "#[/COLOR][/B]) AND ((tbl_store_" & xteam & ".Partner) Like [B][COLOR=red]'*" & [/COLOR][/B]Forms!Frm_Main.Cbo_Partner [B][COLOR=red]& "*'[/COLOR][/B]));")
 
DoCmd.RunSQL strSQL

 

[jj72uk]

Hi Bob,

Thank you for the time spent looking over my question and answering it so well.

I've tried the code and get a compile error.

I didn't write the SQL i made the query up in access, which I know works, and then just looked at the SQL to put it into VBA to allow the user to input a team number and the query will adapt to different linked table numbers depending on user input.

I'm not to sure about the LIKE ... all I know is that in the combo box they can choose an ID and or they can choose a * to display all data. The combo boxes under the LIKE would all be charcter.

My VBA/SQL skills are limited as I'm more adept to writing SAS code.

 

[boblarson]

I've tried the code and get a compile error.

.

Looks like I missed a couple of quotes (see in blue) and also had a paren outside of the semi-colon at the end:

Dim strSQL As String
 
strSQL = "SELECT tbl_store_" & xteam & ".[COLOR=red][B][[/B][/COLOR]Date[COLOR=red][B]][/B][/COLOR], tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Partner[COLOR=red][B]][/B][/COLOR], tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]User[COLOR=red][B]][/B][/COLOR], Sum(IIf(tbl_store_" & xteam & ".[COLOR=red][B][[/B][/COLOR]Retained]="Yes",1,0)) AS [URL="http://www.access-programmers.co.uk/forums/showthread.php?t=202696#"]Saves[/URL], [B][COLOR=red]" & _[/COLOR][/B]
"Sum(IIf(tbl_store_" & xteam & ".[COLOR=red][B][[/B][/COLOR]Retained]=[COLOR=red][B]'[/B][/COLOR]Yes[B][COLOR=red]'[/COLOR][/B],0,1)) AS [No], Count(tbl_store_" & xteam & ".[COLOR=red][B][[/B][/COLOR]Retained]) AS [Total Logged], ([Saves]+[stdrens])/([rencalls]+[retcalls]) AS [Call_To_Save %], [B][COLOR=red]" & _[/COLOR][/B]
"Sum(IIf(tbl_store_" & xteam & ".[COLOR=red][B][[/B][/COLOR]Type]=[COLOR=red][B]'[/B]Standard[/COLOR] Renewal[B][COLOR=red]'[/COLOR][/B],1,0)) AS StdRens, Sum(IIf(tbl_lean_" & xteam & ".[COLOR=red][B][[/B][/COLOR]Call Type]=[COLOR=red][B]'[/B][/COLOR]Renewal Call[B][COLOR=red]"[/COLOR][/B],1,0)) AS RenCalls, Sum(IIf(tbl_lean_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Call Type]=[B][COLOR=red]'[/COLOR][/B]Retention Call[B][COLOR=red]'[/COLOR][/B],1,0)) AS RetCalls, [rencalls]/([rencalls]+[retcalls]) AS [RCP %] [B][COLOR=red]" & _[/COLOR][/B]
"FROM tbl_store_" & xteam & " INNER JOIN tbl_lean_" & xteam & " ON (tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]User[B][COLOR=red]][/COLOR][/B] = tbl_lean_" & xteam & ".[B][COLOR=red][[/COLOR][/B]User[B][COLOR=red]][/COLOR][/B]) AND (tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Date[B][COLOR=red]][/COLOR][/B] = tbl_lean_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Date[B][COLOR=red]][/COLOR][/B]) AND (tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Time[B][COLOR=red]][/COLOR][/B] = tbl_lean_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Time[B][COLOR=red]][/COLOR][/B]) AND (tbl_store_" & xteam & ".[Policy Number] = tbl_lean_" & xteam & ".[Policy Number]) [B][COLOR=red]" & _[/COLOR][/B]
[SIZE=3][COLOR=blue][B]"[/B][/COLOR][/SIZE]WHERE (((tbl_store_" & xteam & ".TLID) Like [COLOR=red][B]'*" &[/B][/COLOR] Forms!Frm_Main.Cbo_TeamLeader & [B][COLOR=red]"*'[/COLOR][/B]) AND ((tbl_store_" & xteam & ".Product) Like [B][COLOR=red]'*" &[/COLOR][/B] Forms!Frm_Main.Cbo_Product)) [B][COLOR=red]" & _[/COLOR][/B]
[B][SIZE=3][COLOR=blue]"[/COLOR][/SIZE][/B]GROUP BY tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Date[B][COLOR=red]][/COLOR][/B], tbl_store_" & xteam & ".Partner, tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]User[B][COLOR=red]] " & _[/COLOR][/B]
[SIZE=3][COLOR=blue][B]"[/B][/COLOR][/SIZE]HAVING (((tbl_store_" & xteam & ".[B][COLOR=red][[/COLOR][/B]Date[B][COLOR=red]][/COLOR][/B])=[COLOR=red][B]#" & [/B][/COLOR]Forms!Frm_Main.Txt_Date [B][COLOR=red]& "#[/COLOR][/B]) AND ((tbl_store_" & xteam & ".Partner) Like [B][COLOR=red]'*" & [/COLOR][/B]Forms!Frm_Main.Cbo_Partner [B][COLOR=red]& "*'[/COLOR][/B]));[SIZE=3][COLOR=blue][B]"[/B][/COLOR][/SIZE]
 
DoCmd.RunSQL strSQL

 

[jj72uk]

Ahh!!

I tried something so similar to that....

How would I then set this to a sub forms record source?

I've tried:

Forms![frm_NEW_consultant conversion].RecordSource = strsql

And attaching it to a command button but it doesn't even give me the input box?!

 

[boblarson]

Ahh!!

I tried something so similar to that....

How would I then set this to a sub forms record source?

I've tried:

Forms![frm_NEW_consultant conversion].RecordSource = strsql

Which is correct if you are calling it from another form.

And attaching it to a command button but it doesn't even give me the input box?!

What input box?

 

[jj72uk]

If you refer to post #1 my code shows

Dim xteam As Integer

xteam = InputBox(Prompt:="Team Number Please.")

Which ties in with the " & xteam & " within the SQL code

 

[boblarson]

Ah, okay. So if it isn't generating that, we have to find out if the click event is really working. So, you didn't add the button and then rename it after adding the code, did you? That will cause a disconnect. You would have to go move the code from the old name to the new one.

If that isn't it, make sure that [Event Procedure] is in the event property for the click event of the button.

And if that isn't it, then set a breakpoint on the first line of code in the click event that Access will let you set the breakpoint (it can't be on a declaration, or such, but on something that actually does something) and see if the event is firing.