Change the form source through a VB code

[Pietro]

Hi,

I'm trying to change the form source through a VB code,I've a problem
that i cannot put the whole SQL code in one line,can you help ?
The SCL is:

Form.RecordSource = "SELECT CapAll.ID AS Code, CapAll.[Service Request] AS
SR, CapAll.Subject, CapAll.Turn, CapAll.Language, CapAll.Received,
CapAll.Replied, CapAll.Delayed, IIf([delayed]=1,’Delayed’,’On Time’) AS Tipo,
IIf(Mid([Turn],1,13)=‘Investigation’,’Investigation’,’24hrs CBR’) AS Class,
Format(IIf((DateDiff(‘s’,[received],Now())/3600)>24,0,24-(DateDiff(‘s’,[received],Now())/3600)),’00.00000’)
AS HRS, Format(Trim(Mid([HRS],InStr([HRS],’.’)+1/100000))*60,’00.00000’) AS
MNT, Format(Trim(Mid([MNT],InStr([MNT],’.’)+1/100000))*60,’00.00000’) AS SCS,
Left([hrs],2) & ‘:’ & Left([mnt],2) & ‘:’ & Left([scs],2) AS TimeLeft,
IIf([subject]=‘XCD CAR CONTACT’,’Care Contact’,’Escalations’) AS Type FROM
CapAll"
WHERE (((CapAll.Language)=[forms]![queue]![language2] Or
[forms]![queue]![language2] Is Null) AND ((IIf([delayed]=1,’Delayed’,’On
Time’))=[forms]![queue]![Tipo2] Or [forms]![queue]![Tipo2] Is Null) AND
((IIf(Mid([Turn],1,13)=‘Investigation’,’Investigation’,’24hrs
CBR’))=[forms]![queue]![Class2] Or [forms]![queue]![Class2] Is Null) AND
((Status)=‘Open’) AND ((IIf([subject]=‘XCD CAR CONTACT’,’Care
Contact’,’Escalations’))=[forms]![queue]![Type2] Or [forms]![queue]![Type2]
Is Null))ORDER BY CapAll.Received;"

 

[Douglas J. Steele]

You can use the Line Continuation character (an underscore preceded by a
space):

Form.RecordSource = "SELECT ID AS Code, [Service Request] AS SR, " & _
"Subject, Turn, Language, Received, Replied, Delayed, " & _
"IIf([delayed]=1,'Delayed','On Time') AS Tipo, " & _
"IIf(Mid([Turn],1,13)='Investigation','Investigation','24hrs CBR') AS
Class, " & _

etc.

or you can build up the SQL string piece by piece:

Dim strSQL As String

strSQL = "SELECT ID AS Code, [Service Request] AS SR, "
strSQL = strSQL & "Subject, Turn, Language, Received, "
strSQL = strSQL & "Replied, Delayed, IIf([delayed]=1,'Delayed','On Time')
AS Tipo, "
etc.
Form.RecordSource = strSQL

Note how I've left off the table name in front of each field name. Since
you've only got a single table in your query, there's really no need to
include the table name.

 

[Arvin Meyer [MVP]]

You can use a line continuation character, the underscore (_) or dim and use
a variable, such as:

Dim strSQL As String

strSQL = "SELECT CapAll.ID AS Code, CapAll.[Service Request] AS "
strSQL = strSQL & "SR, CapAll.Subject, CapAll.Turn, CapAll.Language,"

etc. then use:

Form.RecordSource = strSQL