vba sql delete query problem

[52]

I'm trying to delete from a query where there are two fields that must be matched. This works with one variable (or the other), but when I add the second, it doesn't work - I get a Run-time error 3061 - Too few parameters. Expected 1.

CurrentDb.Execute "DELETE FROM tblCurrent WHERE [Year] = " & "2005" & " AND PID = " & [Forms]![currentform]![PID] & ";"

Thanks for any suggestions.

 

[MakeItSo]

Try this:
CurrentDb.Execute "DELETE * FROM tblCurrent WHERE [Year] = " & "2005" & " AND PID = " & Me![PID] & ";"
;-)
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[KenReay]

Hi

You do not say which of the two work, but it is worth remembering that if a column is a string, you need to bound the criteria in ' marks, so

CurrentDb.Execute "DELETE FROM tblCurrent WHERE [Year] = " & "2005" & " AND PID = '" & [Forms]![currentform]![PID] & "';"
may be more appropriate

also if PID is on the current form, you do not need the FORMS! syntax, so

CurrentDb.Execute "DELETE FROM tblCurrent WHERE [Year] = " & "2005" & " AND PID = " & Me.[PID] & ";"

CurrentDb.Execute "DELETE FROM tblCurrent WHERE [Year] = " & "2005" & " AND PID = '" & Me.[PID] & "';"


also relevant is the fact that you have used a reserved word (ie Year) as a column name, this is not a good idea


Regards

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

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

UK

 

[52]

Both of the deletes work individually - not sure if it's possible to do this or not...

tried makeitso's code - thanks - (but didn't work.)

 

[MakeItSo]

Found another one:
enclose your year in single quotes:
CurrentDb.Execute "DELETE * FROM tblCurrent WHERE [Year] = '" & "2005" & "' AND PID = " & Me![PID] & ";"

 

[ninash]

Hi 52

Forgive me for being a little thick but....

You have a record set open and you are going to delete the record you are looking at and another record(s) from another record set????

or are you just wanting to delete all records where the current PID match the stated year???

I said it might sound thick

Regards
Tony

 

[KenReay]

Hi

Have you taken onboard my comments about using a reserved word (Year) as a column name ?

Regards

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

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

UK

 

[52]

My 'year' is not really named year - it's baseyear, but to be more clear in explaining, i used the term 'year'-- sorry for the confusion.

Also, When i change to single quotes, it's read as a comment out.

Yes, it's my goal to delete a record from a table from an opened database that meets two criteria - one which comes from a form and one that is defined (year=2005).

It's just funny that they work singly and not with both criteria.

 

[MakeItSo]

I meant single quotes within the string, i.e. before your quote end and directly after the next quote begin:
[Space added for clearer visibility]
WHERE [Year] = ' " & "2005" & " ' AND ...
;-)

 

[KenReay]

Hi

OK, so can we see the exact SQL rather than an amended version, also please state, are the columns in the criteria of type numeric or string ?

Regards

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

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

UK

 

[52]

Thanks for your help. Here's what finally worked... I tried the single quotes and couldn't get that to work..

CurrentDb.Execute "DELETE FROM tblCurrent WHERE [Year] = " & """2005""" & " AND PID = " & """" & Me![PID] & """" & ";"