SQL Compile Error: Expected End of Statement

[LarryDeLaruelle]

I am trying to create two queries in a module that will update a table when the database is opened. I created the queries to test them and then copied/pasted the SQL view into the module to create the SQL strings. However, when I move off the last line I get the above error; checking help says "Improper use of parentheses . . ."

Here is the code:

strSQL = "INSERT INTO tblOryxMaster ( ClientID, FullName, ServiceDate ) " _
strsql = strsql & "SELECT Client.ClientID, Client.FullName, Client.ServiceDate " _
strsql = strsql & "FROM Client LEFT JOIN tblOryxMaster ON Client.ClientID = tblOryxMaster.ClientID " _
strsql = strsql & "WHERE (((Client.ServiceDate)>=#1/1/2000#) AND " _
strsql = strsql & "((IIf([tblOryxMaster].[clientid]=[client].[clientid],1,0))=0)) ;"

strSQL = "UPDATE tblOryxMaster INNER JOIN ClientProgram " _
strSQL = strsql & "ON tblOryxMaster.ClientID = ClientProgram.ClientID " _
strsql = strsql & "SET tblOryxMaster.DischargeDate = [clientprogram].[dischargedate]" _
strsql = strsql & "WHERE (((tblOryxMaster.DischargeDate) Is Null) " _
strsql = strsql & "AND ((ClientProgram.EndDate)=[clientprogram].[dischargedate]));"

I'm sure the answer is obvious, but I've looked at this and counted parands for so long I've gotten a headache. TGIF.

Thanks for looking at this. [sig]<p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br> [/sig]

 

[Robertd]

Hi Larry
i take you don't actually have the line continuation &quot;_&quot; in the code.

eg
mySQL = &quot;something&quot;
mySQL = mySQL & &quot;something else&quot;
mySQL = mySQL & &quot;something more&quot;
etc

if all the &quot;somethings&quot; are contained in inverted commas then (access97) won't evaluate the strings until you actually run the SQL using
DoCmd.RunSQL mySQL
maybe you have missed a &quot;(&quot; or &quot&quot; when carving the source SQL statement up into segments or missed/added a space?
go back to the queries and make sure they are the same.

also try
msgbox strSQL
instead of docmd.runsql and compare the constructed string to the query SQL just to be sure they are the same!
....

WHERE (

((Client.ServiceDate) >=#1/1/2000#)
AND
((IIf([tblOryxMaster].[clientid]=[client].[clientid],1,0)
=0))

);
hth
Robert

[sig][/sig]

 

[LarryDeLaruelle]

Thanks for the response Robert.

The code I pasted to the module is exactly as I copied it from the SQL view of the query that I created and tested (works OK). All I did was add line continuation where it seemed appropriate and then concantenated the existing string with the next piece.

I'm wondering if I have to put the literals into separate quotes:

&quot;>=#&quot; & 01/01/2000 & &quot;# . . . etc&quot;, and

&quot;[ClientID],1,0)='&quot; & 0 & &quot;'));&quot;

Let me know if I understood your response or if you have any other thoughts.

Once again, thanks. [sig]<p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br> [/sig]

 

[RoyVidar]

Hi Larry!

Roberttd is quite right. You do not need, or rather, you can't use, line continuation mark the way you bild your SQL-string.

Line continuation is used when you build it for instance like this:

strSQL = &quot;INSERT INTO tblOryxMaster ( ClientID, FullName, ServiceDate ) &quot; & _
&quot;SELECT Client.ClientID, Client.FullName, Client.ServiceDate &quot; & _
&quot;FROM Client LEFT JOIN tblOryxMaster ON Client.ClientID = &quot; & _ ...

So without excamining your code thouroughly, i'll advice you to remove the line continuation mark and try out Roberttd's suggestions.

Enjoy,

Roy-Vidar [sig][/sig]

 

[Robertd]

Hi Larry

for SQL dates paste this into a global module you can then pass a date to it and it will do the rest
you might have to change the day/month to cater for that strange format you guys use ;-)


Public Function DateSQLFormat(Yourdate As Variant)
' RobWen Communications 6-Jul-98
' returns a date format mm/dd/yy for SQL statements
Dim TempDate As String
If IsNull(Yourdate) Then
Yourdate = Date
End If
TempDate = &quot; #&quot;
TempDate = TempDate & Month(Yourdate)
TempDate = TempDate & &quot;/&quot;
TempDate = TempDate & Day(Yourdate)
TempDate = TempDate & &quot;/&quot;
TempDate = TempDate & Right$(Year(Yourdate), 2)
TempDate = TempDate & &quot;# &quot;
DateSQLFormat = TempDate
End Function

then you can call it like this

mySQL = &quot;UPDATE SuppInvoice INNER JOIN SuppOSInvWork ON&quot;
mySQL = mySQL & &quot; SuppInvoice.SuppInvID = SuppOSInvWork.SuppInvID&quot;
mySQL = mySQL & &quot; SET SuppInvoice.CompDate =&quot;
mySQL = mySQL & DateSQLFormat(myTransDate)
mySQL = mySQL & &quot; WHERE (((SuppInvoice.CompDate) Is Null)&quot;
mySQL = mySQL & &quot; AND ((SuppOSInvWork.SuppID)=&quot;
mySQL = mySQL & mySuppID & &quot&quot;
mySQL = mySQL & &quot; AND (([Owing]-[ThisPmntTot])=0));&quot;


don't worry about the line wrap around. just assemble the SQL statement in small chunks no line continuation

call it like this
' hides run sql prompt and others
DoCmd.SetWarnings (False)
DoCmd.RunSQL mySQL 'update BankAccountCheques table
' all ways turn back on
DoCmd.SetWarnings (True)

i use SQL like this in applications its very powerful

s-)

Robert


[sig][/sig]

 

[LarryDeLaruelle]

Duh!!!!

Thanks folks. Sometimes I need a two by four up side the head before I see an obvious error.

I'll blame it on still being a beginner with SQL (although I still should have known better).

Thanks again. [sig]<p>Larry De Laruelle<br><a href=mailto:larry1de@yahoo.com>larry1de@yahoo.com</a><br><a href= > </a><br> [/sig]

 

[petermeachem]

also be careful wher you have something like

_
strsql = strsql & &quot;SET tblOryxMaster.DischargeDate = [clientprogram].[dischargedate]&quot;
strsql = strsql & &quot;WHERE (((tblOryxMaster.DischargeDate) Is Null) &quot;

that you actually have a space between the dischargedate] and the where.

I make this mistake round about once a week

[sig][/sig]