The immediate IIF() is a VBA (VB) function. When you include it in an SQL
string, you have to concatenate the *result* of the IIF(). SQL doesn't know
what to do when it encounters the IIF().
Also it is *really* hard to read the SQL statement like you have it. Try this:
strSQL = "SELECT QFaxes.LogInID, QFaxes.OPID,"
strSQL = strSQL & " QFaxes.AsrName, QFaxes.Acct,"
strSQL = strSQL & " QFaxes.Rpt, QFaxes.BillEffDte,"
strSQL = strSQL & " QFaxes.PolEffDt, QFaxes.Policy,"
strSQL = strSQL & " QFaxes.InsName, QFaxes.ProdCd,"
strSQL = strSQL & " QFaxes.ProdName, QFaxes.State, QFaxes.City,"
strSQL = strSQL & " TP.Zip, QF.TranType, TP.[Contact Name],"
strSQL = strSQL & " TP.[Phone Number], TP.[Fax Number],"
strSQL = strSQL & " QFaxes.Gross, QFaxes.Comm, QFaxes.Net,"
strSQL = strSQL & " QFaxes.Ext, QFaxes.Keep, QFaxes.RptDt,"
'select the return phone number
Select Case [team]
Case "AON", "Marsh", "Swett", "Western", "Willis"
strSQL = strSQL & " (866) 594-4360"
Else
strSQL = strSQL & " (866) 594-4361"
End Select
strSQL = strSQL & " AS ReturnToll,"
'select return fax number
Select Case [team]
Case "AON", "Marsh", "Swett", "Western", "Willis"
strSQL = strSQL & " (877) 867-9495"
Else
strSQL = strSQL & " (800) 842-9569"
End Select
strSQL = strSQL & " AS ReturnFax"
If [SPCPol] <> "----------" Then
strSQL = strSQL & " QFaxes.[Policy]"
Else
strSQL = strSQL & " "
End If
strSQL = strSQL & " AS [Ref#]"
strSQL = strSQL & " FROM qfaxes AS QF INNER JOIN"
strSQL = strSQL & " TProdInfo AS TP ON QFaxes.ProdCd = TP.ProdCd"
strSQL = strSQL & " WHERE (QFaxes.Keep=True)"
If IsNull([contact name]) Then
strName = "/Name=Accounting.Dept"
Else
strName = "/Name=" & [contact name]
End If
strFaxNo = "/Fax=" & [fax number]
strFax = strName & strFaxNo & "/<
[email protected]>"
'-------------------------------
'for debugging. view output in the immediate window
' press ctl-G to open window
'delete or comment out after debugging complete
Debug.Print strSQL
'-------------------------------
DoCmd.SendObject acSendReport, "RPCDemandFax", acFormatRTF, strFax, , ,
"Outstanding Property Casualty Policies", "Please Review the Following
Outstanding Policies", False
Set a breakpoint on the "Docmd" statement and look at the SQL in the
immediate window (open it by typing <ctl-G>).
Is the SQL what you expect??
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
lilbit27 said:
My recommendation is to copy the SELECT statement and paste it into a blank
query open in SQL view, and see if you can get it to run there (the error
messages are a little more helpful). Once it's working there, you can paste
it back into your VBA code.
Carl Rapson
am trying to start a loop code but I keep getting a syntax error in
my
select statement.:
Dim rsfax As DAO.Recordset
Dim rsPastDues As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rsPastDues =
Forms.FPastDueMainView.FPastDues.Form.RecordsetClone
Set rsFax = db.OpenRecordset("SELECT qfaxes.LogInID, qfaxes.OPID,
qfaxes.AsrName, qfaxes.Acct, qfaxes.Rpt, qfaxes.BillEffDte,
qfaxes.PolEffDt, qfaxes.Policy, qfaxes.InsName, qfaxes.ProdCd,
qfaxes.ProdName, qfaxes.State, qfaxes.City, TProdInfo.Zip,
qfaxes.TranType, TProdInfo.[Contact Name], TProdInfo.[Phone Number],
TProdInfo.[Fax Number], qfaxes.Gross, qfaxes.Comm, qfaxes.Net,
qfaxes.Ext, qfaxes.Keep, qfaxes.RptDt, IIf([Team] In
(""AON"",""Marsh"",""Swett"",""Western"",""Willis""),""(866)
594-4360"",""(866) 594-4361"") AS ReturnToll, IIf([Team] In
(""AON"",""Marsh"",""Swett"",""Western"",""Willis""),""(877)
867-9495"",""(800) 842-9569"") AS ReturnFax,
IIf([SPCPol]<>"----------",[Policy],"") AS [Ref#], qfaxes.Email", _
FROM qfaxes INNER JOIN TProdInfo ON qfaxes.ProdCd =
TProdInfo.ProdCd",
_
WHERE (((qfaxes.Keep)=True) AND ((qfaxes.Email)=fOSUserName()))"
DoCmd.SendObject acSendReport, "RPCDemandFax", acFormatRTF,
strFax, , , "Outstanding Property Casualty Policies", "Please Review
the Following Outstanding Policies", False- Hide quoted text -
- Show quoted text
I have the select stament working, but I can't get my fax code to
work.
Private Sub cmdSendFax_Click()
Dim rst As DAO.Recordset
Dim rsPastDues As DAO.Recordset
Dim db As DAO.Database
Dim strName As String
Dim strFaxNo As String
Dim strFax As String
strSQL = "SELECT QFaxes.LogInID, QFaxes.OPID, QFaxes.AsrName,
QFaxes.Acct, QFaxes.Rpt, QFaxes.BillEffDte, " & _
" QFaxes.PolEffDt, QFaxes.Policy, QFaxes.InsName, QFaxes.ProdCd,
QFaxes.ProdName, QFaxes.State, QFaxes.City, " & _
" TP.Zip, QF.TranType, TP.[Contact Name], TP.[Phone Number], TP.[Fax
Number], " & _
" QFaxes.Gross, QFaxes.Comm, QFaxes.Net, QFaxes.Ext, QFaxes.Keep,
QFaxes.RptDt, " & _
" IIf([Team] In ('AON','Marsh','Swett','Western','Willis'), '(866)
594-4360', '(866) 594-4361') AS ReturnToll, " & _
" IIf([Team] In ('AON','Marsh','Swett','Western','Willis'), '(877)
867-9495', '(800) 842-9569') AS ReturnFax, " & _
" IIf([SPCPol]<>'----------', QFaxes.[Policy], ' ') AS [Ref#] " & _
" FROM qfaxes AS QF INNER JOIN " & _
" TProdInfo AS TP ON QFaxes.ProdCd = TP.ProdCd " & _
" WHERE (QFaxes.Keep=True)"
If IsNull([contact name]) Then
strName = "/Name=Accounting.Dept"
Else
strName = "/Name=" & [contact name]
End If
strFaxNo = "/Fax=" & [fax number]
strFax = strName & strFaxNo & "/<
[email protected]>"
DoCmd.SendObject acSendReport, "RPCDemandFax", acFormatRTF,
strFax, , , "Outstanding Property Casualty Policies", "Please Review
the Following Outstanding Policies", False
End Sub- Hide quoted text -
- Show quoted text -
