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:


Private Sub Command9_Click()

Dim strSQL As String
Set db = CurrentDb

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] & "' ;"
db.QueryDefs("PassThroughTest").SQL = strSQL
DoCmd.OpenQuery "PassthroughTest"

End Sub


Here is what I would like the strSQL section to look 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] & "' ;"



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

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..

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] & "' ;"

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!