compile error: Invalid use of property

[jerry28ph]

Hi ALL,

I'm trying to run this query using command button to update my table using the supplied field value and calculated values on my main table, but it seems there's a problem on running this script. It gave me "Compile Error - Invalid use of property" on Set cs_datefrom line. cs_datefrom is Date/Time data type on my table and I used date formatting but still giving the same error. Is there a missing on my query?


strSQL = "UPDATE tab_Summary "
Set cs_datefrom = "'" & Me.[Smdr Qry_Total_Direct subform1].Form!Format([Text2], "mm/dd/yyyy") & "'," & _
in_int_ext = "'" & Me.[Smdr Qry_Total_Direct subform1].Form![Text2] & "' & "
in_ext_caller = "'" & Me.[Smdr Qry_Total_Queue subform].Form![Text2] & "');"

WHERE pc_extNno = Me.Text353

DoCmd.RunSQL strSQL


Please i need your help.
Thanks and more power to all.

Regards,
Jerry

 

[pbaldy]

Sure; everything after the first line is outside the strSQL string you're building. You might try building it all on one line first, to get the SQL syntax correct, then break it onto multiple lines for readability. That way you're only debugging one problem at a time.

 

[jerry28ph]

hi pbaldy,

i broke it into 3 separate lines (3 fields) like this:

strSQL = "UPDATE tab_Summary"
Set cs_datefrom = "'" & Me.[Smdr Qry_Total_Direct subform1].Form!Format([Text2], "mm/dd/yyyy") & _
in_int_ext = "'" & in_int_ext = "'" & Me.[Smdr Qry_Total_Direct subform1].Form![Text2] & _
in_ext_caller = "'" & in_ext_caller = "'" & Me.[Smdr Qry_Total_Queue subform].Form![Text2] & "');"


WHERE pc_extNno = Me.Text353

DoCmd.RunSQL strSQL


..but still gives me the same compile error: Invalid use of property on "Set cs_datefrom" line.
PLease advise.

Thank you.
Jerry

Sure; everything after the first line is outside the strSQL string you're building. You might try building it all on one line first, to get the SQL syntax correct, then break it onto multiple lines for readability. That way you're only debugging one problem at a time.

 

[MSAccessRookie]

hi pbaldy,

i broke it into 3 separate lines (3 fields) like this:

strSQL = "UPDATE tab_Summary"
Set cs_datefrom = "'" & Me.[Smdr Qry_Total_Direct subform1].Form!Format([Text2], "mm/dd/yyyy") & _
in_int_ext = "'" & in_int_ext = "'" & Me.[Smdr Qry_Total_Direct subform1].Form![Text2] & _
in_ext_caller = "'" & in_ext_caller = "'" & Me.[Smdr Qry_Total_Queue subform].Form![Text2] & "');"


WHERE pc_extNno = Me.Text353

DoCmd.RunSQL strSQL


..but still gives me the same compile error: Invalid use of property on "Set cs_datefrom" line.
PLease advise.

Thank you.
Jerry

1. Like pbaldy said, the SQL Definition statement is incorrect. It ends after strSQL = "UPDATE tab_Summary". Everything after this is considered a new statement, so the SET is treated like a new command, and since there is a valid VB SET Command, and your SET statement is not formatted according to the required rules, you get the Syntax Error.
2. I believe that the Format Commands are also improperly placed