Need help with run time error 3075 - syntax error for SQL Statement.

stephenlecomptejr
stephenlecomptejr used Ask the Experts™
on
Need help with identifying syntax error:

Here's the output of SQL string:

DELETE tempTimesheetWeek.PMHours, tempTimesheetWeek.AMHours, tempTimesheetWeek.[08WorkDate], tempTimesheetWeek.[01WorkDate] FROM tempTimesheetWeek WHERE (tempTimesheetWeek.PMHours = 0 Or tempTimesheetWeek.PMHours IsNull)  AND tempTimesheetWeek.AMHours >0 AND tempTimesheetWeek.[01WorkDate] = #12/13/2018#

Open in new window


Please note image of syntax error message
sql-syntax-msg.PNG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
change isnull to is null
DELETE tempTimesheetWeek.PMHours, tempTimesheetWeek.AMHours, tempTimesheetWeek.[08WorkDate], tempTimesheetWeek.[01WorkDate] FROM tempTimesheetWeek WHERE (tempTimesheetWeek.PMHours = 0 Or tempTimesheetWeek.PMHours Is Null)  AND tempTimesheetWeek.AMHours >0 AND tempTimesheetWeek.[01WorkDate] = #12/13/2018#

Open in new window

ste5anSenior Developer

Commented:
Simplify it:

DELETE FROM tempTimesheetWeek
WHERE Nz(PMHours, 0) = 0 AND AMHours > 0 AND [01WorkDate] = #12/13/2018#;

Open in new window

Just to be clear, you cannot delete specific fields in a table. You delete ROWS, i.e. the entire row.
Other than that, I would stick to using Is Null, which is plan SQL and doesn't involving function calls. It is faster.

DELETE FROM tempTimesheetWeek WHERE (tempTimesheetWeek.PMHours = 0 Or tempTimesheetWeek.PMHours Is Null)  AND tempTimesheetWeek.AMHours >0 AND tempTimesheetWeek.[01WorkDate] = #12/13/2018#

Open in new window

Author

Commented:
Appreciate everyone's replies.  Thanks for the help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial