VB syntax error

[cptcanada]

HI, I am getting a sytax error saying that I have a comma that is not in a correct place. I have re-written this code many times over in idfferent forms and I am still getting the runtime syntax error,

Thanks for the help

mStringSQL = "DELETE Driver_daily_lo gs.emp_id, Driver_daily_lo gs.date_recieve d, " _
& "Driver_daily_l ogs.violation, Driver_daily_lo gs.form_manner, Driver_daily_lo gs.log_status, " _
& "Driver_daily_l ogs.status_date , Driver_daily_lo gs.date_destroy ed " _
& "Driver_daily_l ogs.comments, Driver_daily_lo gs.log_id " _
& "FROM Driver_daily_lo gs"
& "Where GetDate() > date_destroyed "

[jeffstl]

HI, I am getting a sytax error saying that I have a comma that is not in a correct place. I have re-written this code many times over in idfferent forms and I am still getting the runtime syntax error,

Thanks for the help

mStringSQL = "DELETE Driver_daily_lo gs.emp_id, Driver_daily_lo gs.date_recieve d, " _
& "Driver_daily_l ogs.violation, Driver_daily_lo gs.form_manner, Driver_daily_lo gs.log_status, " _
& "Driver_daily_l ogs.status_date , Driver_daily_lo gs.date_destroy ed " _
& "Driver_daily_l ogs.comments, Driver_daily_lo gs.log_id " _
& "FROM Driver_daily_lo gs"
& "Where GetDate() > date_destroyed "

Your missing a comma between

& "Driver_daily_l ogs.status_date , Driver_daily_lo gs.date_destroy ed " _
& "Driver_daily_l ogs.comments, Driver_daily_lo gs.log_id " _

SQL can be frustrating because when the querys get really big it is hard to troubleshoot them. But be patient, seperate out sections if you have to in notepad or something and go over them carefully.

[cptcanada]

Thanks for the help, I am just getting used to doing SQL in VB

[jeffstl]

Thanks for the help, I am just getting used to doing SQL in VB

No problem

Yeah I understand. Like I said the bigger the SQL query the more difficult it becomes to troubleshoot, so sometimes you just have to do the old "stare and compare" by eye.

[cptcanada]

No problem

Yeah I understand. Like I said the bigger the SQL query the more difficult it becomes to troubleshoot, so sometimes you just have to do the old "stare and compare" by eye.

Now even with adding in the comma that was missing I am still getiign an erro message saying incorrect syntax near ' , '


mStringSQL = "DELETE Driver_daily_lo gs.emp_id, Driver_daily_lo gs.date_recieve d, " _
& "Driver_daily_l ogs.violation, Driver_daily_lo gs.form_manner, Driver_daily_lo gs.log_status, " _
& "Driver_daily_l ogs.status_date , Driver_daily_lo gs.date_destroy ed, " _
& "Driver_daily_l ogs.comments, Driver_daily_lo gs.log_id " _
& "FROM Driver_daily_lo gs " _
& "Where GetDate() > date_destroyed "

[jeffstl]

Now even with adding in the comma that was missing I am still getiign an erro message saying incorrect syntax near ' , '


mStringSQL = "DELETE Driver_daily_lo gs.emp_id, Driver_daily_lo gs.date_recieve d, " _
& "Driver_daily_l ogs.violation, Driver_daily_lo gs.form_manner, Driver_daily_lo gs.log_status, " _
& "Driver_daily_l ogs.status_date , Driver_daily_lo gs.date_destroy ed, " _
& "Driver_daily_l ogs.comments, Driver_daily_lo gs.log_id " _
& "FROM Driver_daily_lo gs " _
& "Where GetDate() > date_destroyed "

Ok. Off hand I dont see the problem but first lets try to simplify your problems. I assume that all of those columns in your query are pretty much the whole of a "record". So instead of doing all that mess just do this

DELETE * FROM Driver_daily_lo gs WHERE CURRENT_TIMESTA MP > date_destroyed

Much easier....

Then, the next thing is I have no idea how date_destroyed is formatted. Is it a Day? is it mm/dd/yyyy? is it a julian date?

You have to make sure whatever SQL returnes for CURRENT_TIMESTA MP, or whatever you end up using, has to match the same format as whatever is stored in date_destroyed.

[cptcanada]

ya date_destroyed is in that format

[cptcanada]

sorry the date is in dd/mm/yyyy

[jeffstl]

ya date_destroyed is in that format

Which format?

CURRENT_TIMESTA MP?
CURRENT_DATE?
CURRENT_TIME?

Each of these qill return a different formatted data so which are you using and which one best matches date_destroyed?

Also make sure that date_destroyed is in fact a date data type in your database.

You may have to play with the SQL CAST function if it isnt.

[cptcanada]

and now when i use
mStringSQL1 = "DELETE * FROM Driver_daily_lo gs WHERE CURRENT_TIMESTA MP > date_destroyed"

it is giving me an erro on the * saying that it is incorrect syntax near '*'

[cptcanada]

i am using the current_date

[jeffstl]

sorry the date is in dd/mm/yyyy

OK. So you want CURRENT_DATE then probably.

[jeffstl]

and now when i use
mStringSQL1 = "DELETE * FROM Driver_daily_lo gs WHERE CURRENT_TIMESTA MP > date_destroyed"

it is giving me an erro on the * saying that it is incorrect syntax near '*'

Really? Wow. Um. What kind of database are you using?

You could just try it without the * because that should technically work as well.

mStringSQL1 = "DELETE FROM Driver_daily_lo gs WHERE CURRENT_DATE > date_destroyed"

[cptcanada]

I am uisng SQL server 2005, and for some reason it donts like any date format i am trying to use i get an error saying that the current_date is incorrect syntax