Delete Sql script problem 2

[mych]

As a relative beginner I'm having problems with a SQL delete script. I created a query and then viewed it in SQL view copied it and then used it in my code.

The coded version is

    DoCmd.SetWarnings False
        strSQL = "DELETE TblProjectStats.ProjectID, TblProjectStats.ProjectName, "
        strSQL = strSQL & "TblProjectStats.LID, TblProjectStats.InUseBy "
        strSQL = strSQL & "FROM TblProjectStats "
        strSQL = strSQL & "WHERE (TblProjectStats.InUseBy = " & GlbUserID & ");"
        DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

Basically I need to delete a row from the table TblProjectStats where InUseBy = GlbUserID. The table contains 4 fields.

Problem is I have had all sorts of errors some mentioning I have too many or too few ( and the final error was a type mismatch. All fields in TblProjectStats are Text fields apart from LID which is a Date. GlbUserID is Text and is a global field derived a startup using Environ("username") to find out who is logged on.

Grateful for any help

Thanks
Mych

 

[PHV]

Have you tried this ?
strSQL = "DELETE FROM TblProjectStats WHERE InUseBy=" & GlbUserID & ";"
If InUseBy isn't numeric, then :
strSQL = "DELETE FROM TblProjectStats WHERE InUseBy='" & GlbUserID & "';"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KenReay]

hi

DoCmd.SetWarnings False
strSQL = "DELETE FROM TblProjectStats WHERE InUseBy = '" & GlbUserID & "';"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

You do not need to list the individual columns, you are deleting a row

If the field in the WHERE clause is a string, you need to bound it with quotes

If the field is a date you need to bound it with #

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mych]

Thanks Guys,

Here I was struggling with a SQL script created from design view and in the end its just a one liner. I've got a long way to go.

Thnaks