SQL String

  • Thread starter Thread starter Bryan Hughes
  • Start date Start date
B

Bryan Hughes

Hello,

How should I write this string to make it a working SQL string?

strSQL = "SELECT DISTINCTROW tblMain_File.CFID, tblMain_File.IOD,
tblMain_File.FCAD, tblMain_File.FST, IIf([FST]<>"NACF" Or
"NA","OPEN","CLOSED") AS FS, tblFST.COD, tblFST.CM, tblFST.ARS,
tblFST.CSAPP, tblFST.FAN, tblFST.FL, tblFST.FSP, tblFST.FSTC,
IIf([CID]<>"",[CID],"NOT A CLIENT") AS ClientID, tblPrimary_Contact.FN & " "
& tblPrimary_Contact.LN AS Name, tblPrimary_Contact.SA,
tblPrimary_Contact.City, tblPrimary_Contact.State,
tblPrimary_Contact.ZipCode, tblPrimary_Contact.HP, tblPrimary_Contact.CP,
tblPrimary_Contact.MP, tblPrimary_Contact.WP
FROM (tblMain_File INNER JOIN tblFST ON tblMain_File.CFID = tblFST.CFID)
INNER JOIN tblPrimary_Contact ON (tblMain_File.CFID =
tblPrimary_Contact.CFID) AND (tblFST.CFID = tblPrimary_Contact.CFID)
WHERE (((tblMain_File.CFID) = [Forms]![frmNew_Client_Details]![txtCFID]))
GROUP BY tblMain_File.CFID, tblMain_File.IOD, tblMain_File.FCAD,
tblMain_File.FST, IIf([FST]<>"NACF" Or "NA","OPEN","CLOSED"), tblFST.COD,
tblFST.CM, tblFST.ARS, tblFST.CSAPP, tblFST.FAN, tblFST.FL, tblFST.FSP,
tblFST.FSTC, IIf([CID]<>"",[CID],"NOT A CLIENT"), tblPrimary_Contact.FN & "
" & tblPrimary_Contact.LN, tblPrimary_Contact.SA, tblPrimary_Contact.City,
tblPrimary_Contact.State, tblPrimary_Contact.ZipCode, tblPrimary_Contact.HP,
tblPrimary_Contact.CP, tblPrimary_Contact.MP, tblPrimary_Contact.WP;"

Thank you
-Bryan
 
Hello,
I again here is what I have come up with so far:

strSQL = "SELECT DISTINCTROW tblMain_File.CFID, tblMain_File.IOD, " _
& "tblMain_File.FCAD, tblMain_File.FST, " _
& "IIf(tblMain_File.[FST]<> 'NACF' Or 'NA','OPEN','CLOSED') AS
FS, " _
& "tblFST.COD, tblFST.CM, tblFST.ARS, tblFST.CSAPP, tblFST.FAN,
" _
& "tblFST.FL, tblFST.FSP, tblFST.FSTC, " _
& "
IIf(tblPrimary_Contact.[CID]<>'',tblPrimary_Contact.[CID],'NOT A CLIENT') AS
ClientID, " _
& "(tblPrimary_Contact.[FN]+' ') tblPrimary_Contact.LN AS Name,
tblPrimary_Contact.SA, " _
& "tblPrimary_Contact.City, tblPrimary_Contact.State,
tblPrimary_Contact.ZipCode, " _
& "tblPrimary_Contact.HP, tblPrimary_Contact.CP,
tblPrimary_Contact.MP, tblPrimary_Contact.WP " _
& "FROM (tblMain_File INNER JOIN tblFST ON tblMain_File.CFID =
tblFST.CFID) INNER JOIN " _
& " tblPrimary_Contact ON (tblMain_File.CFID =
tblPrimary_Contact.CFID) " _
& "AND (tblFST.CFID = tblPrimary_Contact.CFID) " _
& "WHERE (((tblMain_File.CFID) ='" & strCFID & "' " _
& "GROUP BY tblMain_File.CFID, tblMain_File.IOD,
tblMain_File.FCAD, tblMain_File.FST, " _
& "IIf(tblMain_File.[FST]<>'NACF' Or 'NA','OPEN','CLOSED'),
tblFST.COD, tblFST.CM, tblFST.ARS, " _ & "tblFST.CSAPP,
tblFST.FAN, tblFST.FL, tblFST.FSP, tblFST.FSTC, " _
&
"IIf(tblPrimary_Contact.[CID]<>'',tblPrimary_Contact.[CID],'NOT A CLIENT'),
" _
& "(tblPrimary_Contact.[FN] ' ') tblPrimary_Contact.LN,
tblPrimary_Contact.SA, tblPrimary_Contact.City, " _ &
"tblPrimary_Contact.State, tblPrimary_Contact.ZipCode,
tblPrimary_Contact.HP, tblPrimary_Contact.CP, " _ &
"tblPrimary_Contact.MP, tblPrimary_Contact.WP;"

Please help with my mistakes

-Bryan
 
Bryan,
This is a bit much for me to wade through for the accuracy of your SQL,
but the METHOD of writing this to a string variable would be like this:

Dim strSQL as String
strSQL = "SELECT DISTINCTROW tblMain_File.CFID, tblMain_File.IOD, "
strSQL = strSQL & "tblMain_File.FCAD, tblMain_File.FST, "
strSQL = strSQL & "IIf(tblMain_File.[FST]<> 'NACF' Or 'NA','OPEN','CLOSED')
AS FS, "
strSQL = etc.

Then to verify that you are getting it right,
add a Message Box after the final strSQL = is complete, but before the
actual running of the SQL.

MsgBox strSQL

Step through the code.
The message box will show exactly what the strSQL is returning.
Now you can go back and correct any errors in syntax or logic,
and it will be much easier to find the troublesome line.
When it's correct delete the message box.

Note... a quick glance at the 3rd strSQL line above shows
me at least one error in your coding.

IIf(tblMain_File.[FST]<> 'NACF' Or 'NA','OPEN','CLOSED')
should read
IIf(tblMain_File.[FST]<> 'NACF' Or tblMain_File.[FST]<>
'NA','OPEN','CLOSED')

You must repeat the criteria field each time.
Again, I'm not going through the rest of it. My head hurts already. :-(

Now a question.
What do you intend to do with this SQL?
You cannot run it using RunSQL or CurrentDb.Execute because it's not an
action query.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Bryan Hughes said:
Hello,
I again here is what I have come up with so far:

strSQL = "SELECT DISTINCTROW tblMain_File.CFID, tblMain_File.IOD, " _
& "tblMain_File.FCAD, tblMain_File.FST, " _
& "IIf(tblMain_File.[FST]<> 'NACF' Or 'NA','OPEN','CLOSED') AS
FS, " _
& "tblFST.COD, tblFST.CM, tblFST.ARS, tblFST.CSAPP, tblFST.FAN,
" _
& "tblFST.FL, tblFST.FSP, tblFST.FSTC, " _
& "
IIf(tblPrimary_Contact.[CID]<>'',tblPrimary_Contact.[CID],'NOT A CLIENT') AS
ClientID, " _
& "(tblPrimary_Contact.[FN]+' ') tblPrimary_Contact.LN AS Name,
tblPrimary_Contact.SA, " _
& "tblPrimary_Contact.City, tblPrimary_Contact.State,
tblPrimary_Contact.ZipCode, " _
& "tblPrimary_Contact.HP, tblPrimary_Contact.CP,
tblPrimary_Contact.MP, tblPrimary_Contact.WP " _
& "FROM (tblMain_File INNER JOIN tblFST ON tblMain_File.CFID =
tblFST.CFID) INNER JOIN " _
& " tblPrimary_Contact ON (tblMain_File.CFID =
tblPrimary_Contact.CFID) " _
& "AND (tblFST.CFID = tblPrimary_Contact.CFID) " _
& "WHERE (((tblMain_File.CFID) ='" & strCFID & "' " _
& "GROUP BY tblMain_File.CFID, tblMain_File.IOD,
tblMain_File.FCAD, tblMain_File.FST, " _
& "IIf(tblMain_File.[FST]<>'NACF' Or 'NA','OPEN','CLOSED'),
tblFST.COD, tblFST.CM, tblFST.ARS, " _ & "tblFST.CSAPP,
tblFST.FAN, tblFST.FL, tblFST.FSP, tblFST.FSTC, " _
&
"IIf(tblPrimary_Contact.[CID]<>'',tblPrimary_Contact.[CID],'NOT A CLIENT'),
" _
& "(tblPrimary_Contact.[FN] ' ') tblPrimary_Contact.LN,
tblPrimary_Contact.SA, tblPrimary_Contact.City, " _ &
"tblPrimary_Contact.State, tblPrimary_Contact.ZipCode,
tblPrimary_Contact.HP, tblPrimary_Contact.CP, " _ &
"tblPrimary_Contact.MP, tblPrimary_Contact.WP;"

Please help with my mistakes

-Bryan
Click to expand...
 
Thanks Fred,

I plan on using this Query in a module or class module, so I can print a
report form multiple forms.
(Place into report with design view)

It is a bit long.

Any other suggestions would be helpful.

Thank you
-Bryan
 
Fred,

Here is the final string. Thanks for your help, that worked perfect for
testing.

strSQL = "SELECT DISTINCTROW tblMain_File.CFID, "
strSQL = strSQL & "tblMain_File.IOD, tblMain_File.FCAD, "
strSQL = strSQL & "tblMain_File.FST, "
strSQL = strSQL & "IIf(tblMain_File.[FST]<> 'NACF' Or tblMain_File.[FST]<>
'NA','OPEN','CLOSED') AS FS, "
strSQL = strSQL & "tblFST.COD, tblFST.CM, tblFST.ARS, tblFST.CSAPP, "
strSQL = strSQL & "tblFST.FAN, tblFST.FL, tblFST.FSP, tblFST.FSTC, "
strSQL = strSQL &
"IIf(tblPrimary_Contact.[CID]<>'',tblPrimary_Contact.[CID],'NOT A CLIENT')
AS ClientID, "
strSQL = strSQL & "tblPrimary_Contact.[FN], tblPrimary_Contact.LN , "
strSQL = strSQL & " tblPrimary_Contact.SA, tblPrimary_Contact.City, "
strSQL = strSQL & "tblPrimary_Contact.State, tblPrimary_Contact.ZipCode, "
strSQL = strSQL & "tblPrimary_Contact.HP, tblPrimary_Contact.CP,
tblPrimary_Contact.MP, tblPrimary_Contact.WP "
strSQL = strSQL & "FROM (tblMain_File INNER JOIN tblFST ON
tblMain_File.[CFID] = tblFST.[CFID]) "
strSQL = strSQL & "INNER JOIN tblPrimary_Contact ON (tblMain_File.[CFID] =
tblPrimary_Contact.[CFID]) "
strSQL = strSQL & "AND (tblFST.[CFID] = tblPrimary_Contact.[CFID]) "
strSQL = strSQL & "WHERE tblMain_File.CFID ='" & strCFID & "' "
strSQL = strSQL & "GROUP BY tblMain_File.CFID, tblMain_File.IOD,
tblMain_File.FCAD, "
strSQL = strSQL & "tblMain_File.FST, "
strSQL = strSQL & "IIf(tblMain_File.[FST]<>'NACF' Or tblMain_File.[FST] <>
'NA','OPEN','CLOSED'), "
strSQL = strSQL & " tblFST.COD, tblFST.CM, tblFST.ARS, tblFST.CSAPP,
tblFST.FAN, tblFST.FL, "
strSQL = strSQL & "tblFST.FSP, tblFST.FSTC, "
strSQL = strSQL &
"IIf(tblPrimary_Contact.[CID]<>'',tblPrimary_Contact.[CID],'NOT A CLIENT'),
"
strSQL = strSQL & "tblPrimary_Contact.[FN], tblPrimary_Contact.LN,
tblPrimary_Contact.SA, "
strSQL = strSQL & "tblPrimary_Contact.City, tblPrimary_Contact.State, "
strSQL = strSQL & "tblPrimary_Contact.ZipCode, tblPrimary_Contact.HP,
tblPrimary_Contact.CP, "
strSQL = strSQL & "tblPrimary_Contact.MP, tblPrimary_Contact.WP;"

MsgBox "End Of string."

One question what is the best way to do a concatenating on a first name
field and a last name field in a SQL string?

-Thanks again
-Bryan
 
Back
Top