SQL in VBA

  • Thread starter Thread starter PC User
  • Start date Start date
P

PC User

I'm trying to take a SQL statement from the query editor and insert it
into a VBA code, but I'm not familiar on how to do this. It a long
statement and I would like to break it into several lines. Someone's
help would be appreciated.

SQL Code:
==========================================
SELECT tsubProgramList.[PM ID], tsubProgramList.[PM Description],
tsubProgramList.Facility, tsubProgramList.ResponsibleParty,
tsubProgramList.FrequencyOfService,
IIf([tsubProgramList]![FrequencyOfService]="Annually",DateAdd("yyyy",1,[tsubProgramList]![InitialDate]),
IIf([tsubProgramList]![FrequencyOfService]="Semiannually",DateAdd("m",6,[tsubProgramList]![InitialDate]),
IIf([tsubProgramList]![FrequencyOfService]="Quarterly",DateAdd("q",1,[tsubProgramList]![InitialDate]),
IIf([tsubProgramList]![FrequencyOfService]="Monthly",DateAdd("m",1,[tsubProgramList]![InitialDate]),0))))
AS DueDate
FROM tsubProgramList
WHERE
(((IIf([tsubProgramList]![FrequencyOfService]="Annually",DateAdd("yyyy",1,[tsubProgramList]![InitialDate]),
IIf([tsubProgramList]![FrequencyOfService]="Semiannually",DateAdd("m",6,[tsubProgramList]![InitialDate]),
IIf([tsubProgramList]![FrequencyOfService]="Quarterly",DateAdd("q",1,[tsubProgramList]![InitialDate]),
IIf([tsubProgramList]![FrequencyOfService]="Monthly",DateAdd("m",1,[tsubProgramList]![InitialDate]),0)))))
<Date()))
ORDER BY tsubProgramList.Facility;
===========================================

I need to put this into VBA using a string variable strSQL.
Thanks,

PC
 
In
PC User typed:
I'm trying to take a SQL statement from the query editor
and insert it
into a VBA code, but I'm not familiar on how to do this.
It a long
statement and I would like to break it into several lines.
Someone's
help would be appreciated.

SQL Code:
==========================================
SELECT tsubProgramList.[PM ID], tsubProgramList.[PM
Description],
tsubProgramList.Facility,
tsubProgramList.ResponsibleParty,
tsubProgramList.FrequencyOfService,
IIf([tsubProgramList]![FrequencyOfService]="Annually",DateAdd("yyyy",1,[tsubProgramList]![InitialDate]),
IIf([tsubProgramList]![FrequencyOfService]="Semiannually",DateAdd("m",6,[tsubProgramList]![InitialDate]),
IIf([tsubProgramList]![FrequencyOfService]="Quarterly",DateAdd("q",1,[tsubProgramList]![InitialDate]),
IIf([tsubProgramList]![FrequencyOfService]="Monthly",DateAdd("m",1,[tsubProgramList]![InitialDate]),0))))
AS DueDate
FROM tsubProgramList
WHERE
(((IIf([tsubProgramList]![FrequencyOfService]="Annually",DateAdd("yyyy",1,[tsubProgramList]![InitialDate]),
IIf([tsubProgramList]![FrequencyOfService]="Semiannually",DateAdd("m",6,[tsubProgramList]![InitialDate]),
IIf([tsubProgramList]![FrequencyOfService]="Quarterly",DateAdd("q",1,[tsubProgramList]![InitialDate]),
IIf([tsubProgramList]![FrequencyOfService]="Monthly",DateAdd("m",1,[tsubProgramList]![InitialDate]),0)))))
<Date()))
ORDER BY tsubProgramList.Facility;
===========================================

I need to put this into VBA using a string variable
strSQL.
Thanks,

PC
Click to expand...

Use the concatenation operator & to sequentially add a piece
at a time

strSQL = "SELECT tsubProgramList.[PM ID],"
strSQL = strSQL & "tsubProgramList.[PM Description],"
strSQL = strSQL & "tsubProgramList.Facility,
tsubProgramList.ResponsibleParty,"

and so on...


--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/
Download Free Copy
----
 
a different approach to Nick


strSQL ="SELECT tsubProgramList.[PM ID], " _
&" tsubProgramList.[PM Description]," _
&" tsubProgramList.Facility, tsubProgramList.ResponsibleParty," _

and so on

note the blank before the underscore
 
Back
Top