Writing Clean SQL code with Line Breaks

[ObiBenKenobi]

Hi there, I have an Access 2007 front-end with a SQL 2008 backend. I am successfully using a form with combo boxes to filter my passthrough query by using a command button to rewrite the SQL string based on the data on the form.

My problem is that I am having difficulty writing clean script in my On_Click VBA code. Currently, I have everything posted in one VB line and it works fine. If I throw in some “& _ “ to break it up, I start getting errors. The line break works fine if I’m only using SELECT commands, but it doesn’t like the breaks when I add WHERE criteria.

Here is the working On_Click code:

[FONT=Calibri][SIZE=3]Private Sub Command9_Click()[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]Dim strSQL As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Set db = CurrentDb[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]strSQL = "SELECT C_CASE_EXTID AS 'CASE NUMBER',OU_T_ORG_UNIT_NM AS 'TEAM', C_STAT_AID_STAT_CD AS 'FEDERAL AID STATUS', C_CRNT_STAT_CD AS 'CASE STATUS', C_CASE_PROC_CD AS 'INTAKE STATUS', C_NON_COOP_STAT_CD AS 'COOPERATION', OU_O_ORG_UNIT_DESC_TXT AS' MANAGING OFFICE'from CASE_CAS WHERE C_MNG_CNTY_FIPS_CD = '" & [Forms]![frm_Select_Office].[Combo0] & "' and OU_T_ORG_UNIT_NM = '" & [Forms]![frm_Main_Form].[Team] & "' and C_CRNT_STAT_CD = '" & [Forms]![frm_Main_Form].[CaseStatus] & "' and C_STAT_AID_STAT_CD= '" & [Forms]![frm_Main_Form].[FedAidStatus] & "' ;"[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]db.QueryDefs("PassThroughTest").SQL = strSQL[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]DoCmd.OpenQuery "PassthroughTest"[/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]

Here is what I would like the strSQL section to look like:

[FONT=Calibri][SIZE=3]strSQL = "SELECT C_CASE_EXTID AS 'CASE NUMBER'” & _ [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]“,OU_T_ORG_UNIT_NM AS 'TEAM'” & _ [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]“, C_STAT_AID_STAT_CD AS ‘FEDERAL AID STATUS’” & _ [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]“, C_CRNT_STAT_CD AS ‘CASE STATUS’” & _ [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]“, C_CASE_PROC_CD AS ‘INTAKE STATUS’” & _ [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]“, C_NON_COOP_STAT_CD AS ‘COOPERATION’” & _ [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]“, OU_O_ORG_UNIT_DESC_TXT AS’ MANAGING OFFICE’” & _ [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]“from CASE_CAS” & _ [/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]“WHERE” & _ [/SIZE][/FONT]
 
[FONT=Calibri][SIZE=3]“”C_MNG_CNTY_FIPS_CD = '" & [Forms]![frm_Select_Office].[Combo0]” & _ [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]& "' and OU_T_ORG_UNIT_NM = '" & [Forms]![frm_Main_Form].[Team] & _ [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]& "' and C_CRNT_STAT_CD = '" & [Forms]![frm_Main_Form].[CaseStatus] & _ [/SIZE][/FONT]
[FONT=Calibri][SIZE=3]& "' and C_STAT_AID_STAT_CD= '" & [Forms]![frm_Main_Form].[FedAidStatus] & "' ;"[/SIZE][/FONT]

I’m hoping this is a simple case of me leaving out parentheses or spaces somewhere.

 

[Kiwiman]

Howzit

You can build sql this way

strSQL = "SELECT C_CASE_EXTID AS 'CASE NUMBER',OU_T_ORG_UNIT_NM AS 'TEAM', "
strsql = strsql & "C_STAT_AID_STAT_CD AS 'FEDERAL AID STATUS', C_CRNT_STAT_CD AS 'CASE STATUS', "
strsql = strsql & "C_CASE_PROC_CD AS 'INTAKE STATUS', C_NON_COOP_STAT_CD AS 'COOPERATION', "
strsql = strsql & "OU_O_ORG_UNIT_DESC_TXT AS 'MANAGING OFFICE' "
strsql = strsql & "from CASE_CAS "
strsql = strsql & "WHERE C_MNG_CNTY_FIPS_CD = '" & [Forms]![frm_Select_Office].[Combo0] & "' "
strsql = strsql & "and OU_T_ORG_UNIT_NM = '" & [Forms]![frm_Main_Form].[Team] & "' "
strsql = strsql & "and C_CRNT_STAT_CD = '" & [Forms]![frm_Main_Form].[CaseStatus] & "' "
strsql = strsql & "and C_STAT_AID_STAT_CD= '" & [Forms]![frm_Main_Form].[FedAidStatus] & "' ;"
db.QueryDefs("PassThroughTest").SQL = strSQL
DoCmd.OpenQuery "PassthroughTest"
 
End Sub

To vba sql land take you we will..

 

[Kiwiman]

Howzit

When you build your SQL up, you need to ensure that you cater for spaces that are required in your SQL stmt.

For example you have:

“, OU_O_ORG_UNIT_DESC_TXT AS’ MANAGING OFFICE’” & _ 
“from CASE_CAS” & _ 
 
“WHERE” & _ 
 
“”C_MNG_CNTY_FIPS_CD = '" & [Forms]![frm_Select_Office].[Combo0]” & _

Which when built will show as ...' MANAGING OFFICE'from CASE_CASWHERE

But sql requires a gap between the Select part and the From part and the Where part etc

...' MANAGING OFFICE' from CASE_CAS WHERE blah blah blah

Your required format would look pretty much like :

strSQL = "SELECT C_CASE_EXTID AS 'CASE NUMBER',OU_T_ORG_UNIT_NM AS 'TEAM', " & _
"C_STAT_AID_STAT_CD AS 'FEDERAL AID STATUS', C_CRNT_STAT_CD AS 'CASE STATUS', " & _
"C_CASE_PROC_CD AS 'INTAKE STATUS', C_NON_COOP_STAT_CD AS 'COOPERATION', " & _
"OU_O_ORG_UNIT_DESC_TXT AS 'MANAGING OFFICE' " & _
"from CASE_CAS " & _
"WHERE C_MNG_CNTY_FIPS_CD = '" & [Forms]![frm_Select_Office].[Combo0] & "' " & _
"and OU_T_ORG_UNIT_NM = '" & [Forms]![frm_Main_Form].[Team] & "' " & _
and C_CRNT_STAT_CD = '" & [Forms]![frm_Main_Form].[CaseStatus] & "' " & _
"and C_STAT_AID_STAT_CD= '" & [Forms]![frm_Main_Form].[FedAidStatus] & "' ;"

 

[ObiBenKenobi]

Well, kiss my grits, it worked. That greatly simplifies my life.

I'd give you double thanks for the Yodaese if I could.

At first I didn't know how you could've guessed I liked Star Wars... but then remembered my screen name. Does my ignorance know no bounds?

Thanks again!