Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    7

    Question Unanswered: need help with sql

    Hi,

    I need some help.

    I am trying to delete records from a table where an in-use flag is set to the number zero (integer format in the table).

    I am using these lines:

    SQL = "delete from parts_category_tbl where still_in_use = 0"
    DoCmd.RunSQL SQL

    But when I run it, I get an "Enter parameter value box" with "still_in_use" parameter. If I enter a number, a warning message that I am about to delete all the records appears.

    Can somebody help please?

    Chris

  2. #2
    Join Date
    Mar 2004
    Location
    San Diego
    Posts
    45
    Check your spelling and character placement. That is usually my problem when I see the Parameter box. also, although probably not necessary, you might want to go ahead and say

    SQL = "delete from [parts_category_tbl] where [parts_category_tbl].[still_in_use] = 0"

    try it in the query builder, then check out your SQL. That might help.

  3. #3
    Join Date
    Aug 2004
    Posts
    7
    Thanks.

    A few minutes after posting (after around 30 minutes trying to figure out what was wrong!!!) dioscovered that I was using the wrong table!!!

    Obviously the field still_in_use was not there!

    Thanks anyway.

  4. #4
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    Try:
    SQL = "delete from parts_category_tbl where parts_category_tbl.still_in_use = 0;"
    DoCmd.RunSQL SQL

    It that doesn't work try not to use "_" in your table/field names instead make them:
    tblPartsCategory
    stillInUse
    MS may stop after the first "_"; it's how they name procedures ie. "procName_click".

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    um, no

    underscores in table and column names are just fine
    Last edited by r937; 08-30-04 at 18:20.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2004
    Posts
    7
    Try opening the table as an attachment and delete the record.
    If it won't delete there is something wrong with the definition.
    I.e. No unique index
    Null fields where nulls are not allowed.
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •