Execute Delete Where clause

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

Can anyone please tell me the correct syntax for a where clause. I am using
execute to delete records from a table. It worked ok when I wanted all
records deleting but now I want to add a where clause and I get error 424
Object Required.

strCountry = Me![Country]

strSQL = "DELETE * FROM DealerSelection WHERE DealerSelection.Country
Code = " & strCountry

db.Execute strSQL, dbFailOnError

Thanks in advance for any help.
Sue
 
Try;

strSQL = "DELETE * FROM DealerSelection " _
& "WHERE (((DealerSelection.Country Code = " & strCountry & "));"

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi
|
| Can anyone please tell me the correct syntax for a where clause. I am
using
| execute to delete records from a table. It worked ok when I wanted all
| records deleting but now I want to add a where clause and I get error 424
| Object Required.
|
| strCountry = Me![Country]
|
| strSQL = "DELETE * FROM DealerSelection WHERE DealerSelection.Country
| Code = " & strCountry
|
| db.Execute strSQL, dbFailOnError
|
| Thanks in advance for any help.
| Sue
 
Thanks... I cheated and created a delete query instead which worked. I want
to do a similar task though now to open a recordset to loop through in code.
Tried using your example but I get an error missing operator run time error
3075. Any ideas?

strSQL = "Select * FROM Dealer Selection " _
& "WHERE (((Dealer Selection.Country Code = " & strCountry & "));"


Set rst = CurrentDb.OpenRecordset(strSQL)

Thanks
Sue
 
Actually if 'strCountry' is a string then try this;

strSQL = "DELETE * FROM DealerSelection " _
& "WHERE (((DealerSelection.Country Code = '" & strCountry & "'));"

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Thanks... I cheated and created a delete query instead which worked. I
want
| to do a similar task though now to open a recordset to loop through in
code.
| Tried using your example but I get an error missing operator run time
error
| 3075. Any ideas?
|
| strSQL = "Select * FROM Dealer Selection " _
| & "WHERE (((Dealer Selection.Country Code = " & strCountry & "));"
|
|
| Set rst = CurrentDb.OpenRecordset(strSQL)
|
| Thanks
| Sue
 
Doesn't look quite right to me. But then I haven't tested it. If that gives
errors, try this variety:

strSQL = "DELETE * FROM DealerSelection WHERE Country Code = '" _
& strCountry & "';"
 
Cool thanks - is there a way to check what is in a recordset eg debug.print
rst ? Also, can it be used to open a form eg
DoCmd.OpenForm "frm Dealer Selections"
Me.RecordSource = rst

Thanks in advance for any help.
Sue


Klatuu said:
Doesn't look quite right to me. But then I haven't tested it. If that gives
errors, try this variety:

strSQL = "DELETE * FROM DealerSelection WHERE Country Code = '" _
& strCountry & "';"


Dave Patrick said:
Try;

strSQL = "DELETE * FROM DealerSelection " _
& "WHERE (((DealerSelection.Country Code = " & strCountry & "));"

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi
|
| Can anyone please tell me the correct syntax for a where clause. I am
using
| execute to delete records from a table. It worked ok when I wanted all
| records deleting but now I want to add a where clause and I get error 424
| Object Required.
|
| strCountry = Me![Country]
|
| strSQL = "DELETE * FROM DealerSelection WHERE DealerSelection.Country
| Code = " & strCountry
|
| db.Execute strSQL, dbFailOnError
|
| Thanks in advance for any help.
| Sue
Click to expand...
Click to expand...
 
I am not sure I understand the questions exactly. As to the debug.print, how
you would do it would depend on what you want to see. The basics would be to
loop throught the fields printing what you need.

As to opening a form, I would suggest setting the recordsource in the Open
event.

hughess7 said:
Cool thanks - is there a way to check what is in a recordset eg debug.print
rst ? Also, can it be used to open a form eg
DoCmd.OpenForm "frm Dealer Selections"
Me.RecordSource = rst

Thanks in advance for any help.
Sue


Klatuu said:
Doesn't look quite right to me. But then I haven't tested it. If that gives
errors, try this variety:

strSQL = "DELETE * FROM DealerSelection WHERE Country Code = '" _
& strCountry & "';"


Dave Patrick said:
Try;

strSQL = "DELETE * FROM DealerSelection " _
& "WHERE (((DealerSelection.Country Code = " & strCountry & "));"

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi
|
| Can anyone please tell me the correct syntax for a where clause. I am
using
| execute to delete records from a table. It worked ok when I wanted all
| records deleting but now I want to add a where clause and I get error 424
| Object Required.
|
| strCountry = Me![Country]
|
| strSQL = "DELETE * FROM DealerSelection WHERE DealerSelection.Country
| Code = " & strCountry
|
| db.Execute strSQL, dbFailOnError
|
| Thanks in advance for any help.
| Sue
Click to expand...
Click to expand...
Click to expand...
 
May I point out that Country Code has a space in it and will cause an error.

strSQL = "DELETE * FROM DealerSelection WHERE [Country Code] = " & strCountry

or if Country Code is a text field, you can add the enclosing quote marks using Chr(34).

strSQL = "DELETE * FROM DealerSelection WHERE [Country Code] = " & Chr(34) &
strCountry & Chr(34)
I am not sure I understand the questions exactly. As to the debug.print, how
you would do it would depend on what you want to see. The basics would be to
loop throught the fields printing what you need.

As to opening a form, I would suggest setting the recordsource in the Open
event.

hughess7 said:
Cool thanks - is there a way to check what is in a recordset eg debug.print
rst ? Also, can it be used to open a form eg
DoCmd.OpenForm "frm Dealer Selections"
Me.RecordSource = rst

Thanks in advance for any help.
Sue


Klatuu said:
Doesn't look quite right to me. But then I haven't tested it. If that gives
errors, try this variety:

strSQL = "DELETE * FROM DealerSelection WHERE Country Code = '" _
& strCountry & "';"


:

Try;

strSQL = "DELETE * FROM DealerSelection " _
& "WHERE (((DealerSelection.Country Code = " & strCountry & "));"

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi
|
| Can anyone please tell me the correct syntax for a where clause. I am
using
| execute to delete records from a table. It worked ok when I wanted all
| records deleting but now I want to add a where clause and I get error 424
| Object Required.
|
| strCountry = Me![Country]
|
| strSQL = "DELETE * FROM DealerSelection WHERE DealerSelection.Country
| Code = " & strCountry
|
| db.Execute strSQL, dbFailOnError
|
| Thanks in advance for any help.
| Sue
Click to expand...
Click to expand...
Click to expand...
 
Good catch, John

strSQL = "DELETE * FROM DealerSelection WHERE [Country Code] = '" &
strCountry & "'"

will work also

John Spencer (MVP) said:
May I point out that Country Code has a space in it and will cause an error.

strSQL = "DELETE * FROM DealerSelection WHERE [Country Code] = " & strCountry

or if Country Code is a text field, you can add the enclosing quote marks using Chr(34).

strSQL = "DELETE * FROM DealerSelection WHERE [Country Code] = " & Chr(34) &
strCountry & Chr(34)
I am not sure I understand the questions exactly. As to the debug.print, how
you would do it would depend on what you want to see. The basics would be to
loop throught the fields printing what you need.

As to opening a form, I would suggest setting the recordsource in the Open
event.

hughess7 said:
Cool thanks - is there a way to check what is in a recordset eg debug.print
rst ? Also, can it be used to open a form eg
DoCmd.OpenForm "frm Dealer Selections"
Me.RecordSource = rst

Thanks in advance for any help.
Sue


:

Doesn't look quite right to me. But then I haven't tested it. If that gives
errors, try this variety:

strSQL = "DELETE * FROM DealerSelection WHERE Country Code = '" _
& strCountry & "';"


:

Try;

strSQL = "DELETE * FROM DealerSelection " _
& "WHERE (((DealerSelection.Country Code = " & strCountry & "));"

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi
|
| Can anyone please tell me the correct syntax for a where clause. I am
using
| execute to delete records from a table. It worked ok when I wanted all
| records deleting but now I want to add a where clause and I get error 424
| Object Required.
|
| strCountry = Me![Country]
|
| strSQL = "DELETE * FROM DealerSelection WHERE DealerSelection.Country
| Code = " & strCountry
|
| db.Execute strSQL, dbFailOnError
|
| Thanks in advance for any help.
| Sue
Click to expand...
Click to expand...
Click to expand...
 
Thanks all... working a treat!

Sue


Klatuu said:
Good catch, John

strSQL = "DELETE * FROM DealerSelection WHERE [Country Code] = '" &
strCountry & "'"

will work also

John Spencer (MVP) said:
May I point out that Country Code has a space in it and will cause an error.

strSQL = "DELETE * FROM DealerSelection WHERE [Country Code] = " & strCountry

or if Country Code is a text field, you can add the enclosing quote marks using Chr(34).

strSQL = "DELETE * FROM DealerSelection WHERE [Country Code] = " & Chr(34) &
strCountry & Chr(34)
I am not sure I understand the questions exactly. As to the debug.print, how
you would do it would depend on what you want to see. The basics would be to
loop throught the fields printing what you need.

As to opening a form, I would suggest setting the recordsource in the Open
event.

:

Cool thanks - is there a way to check what is in a recordset eg debug.print
rst ? Also, can it be used to open a form eg
DoCmd.OpenForm "frm Dealer Selections"
Me.RecordSource = rst

Thanks in advance for any help.
Sue


:

Doesn't look quite right to me. But then I haven't tested it. If that gives
errors, try this variety:

strSQL = "DELETE * FROM DealerSelection WHERE Country Code = '" _
& strCountry & "';"


:

Try;

strSQL = "DELETE * FROM DealerSelection " _
& "WHERE (((DealerSelection.Country Code = " & strCountry & "));"

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi
|
| Can anyone please tell me the correct syntax for a where clause. I am
using
| execute to delete records from a table. It worked ok when I wanted all
| records deleting but now I want to add a where clause and I get error 424
| Object Required.
|
| strCountry = Me![Country]
|
| strSQL = "DELETE * FROM DealerSelection WHERE DealerSelection.Country
| Code = " & strCountry
|
| db.Execute strSQL, dbFailOnError
|
| Thanks in advance for any help.
| Sue
Click to expand...
Click to expand...
Click to expand...
 
Back
Top