yes/no in Access

Discussion in 'ASP General' started by Rob Meade, Nov 8, 2003.

  1. Rob Meade

    Rob Meade Guest

    ...
    Try 'true' instead of the 1, failing that can you please post the exact
    error message you are getting...and the code you are using with values etc
    (use a response.write)

    Rob
     
    Rob Meade, Nov 8, 2003
    #1
    1. Advertisements

  2. Rob Meade

    middletree Guest

    A couple of days ago, I posted a qusstion as to why my simple form isn't
    working. The error I get in the browser is:
    Data type mismatch in criteria expression.
    /grace/shapethankyou.asp, line 197

    The error I get when I try to run it in Access 2000 is that it's a "Type
    conversion failure"

    The SQL Statement is:
    INSERT INTO
    Personal(FName,LName,PreferredName,DayPhone,EveningPhone,ContactTime,Email,F
    irstBase,Gift1,Gift2,Gift3,babies)VALUES
    ('test','','','','','','','','','','','1')

    All the fields you see there, with the exception of the last one, are text
    fields. Users can manually type whatever they want in the web form. In the
    database, they are mostly defined as text, and one or two are datetime.

    The last one is a checkbox (I have more, but commented out for now until I
    get this one working). I had the checkbox set to a value of "Yes", and I
    got these same errors, so I posted a question at this forum. It was
    explained to me that Access needs to have a 1 or 0 passed to fields defined
    as yes/no. I tried this, as you can from the above SQL insert statement,
    and I'm getting the same error. I even tried "ON", and it didn't work.

    All fields in this statement are defined as not required, and the statement
    works fine when that last field isn't included.

    I'm baffled. Can you help?

    Disclaimer: I'm used to SQL Server, but this church site project has to be
    done in Access.
     
    middletree, Nov 8, 2003
    #2
    1. Advertisements

  3. Rob Meade

    Ray at Guest

    No, use 1 instead of '1'. I suggest never using true/false.

    Ray at home
     
    Ray at, Nov 8, 2003
    #3
  4. Rob Meade

    Dan Brussee Guest

    Why not? In Access, this is the value that is being used, plus a
    boolean is easier to work with than a numeric value of 1 or 0 (or is
    that -1 and 0??).
     
    Dan Brussee, Nov 8, 2003
    #4
  5. Rob Meade

    Ray at Guest

    So that when/if he upgrades to SQL Server ever, he won't have to worry about
    changing all the TRUE/FALSE's to 1/0's.

    Ray at home
     
    Ray at, Nov 8, 2003
    #5
  6. Rob Meade

    Tom B Guest

    or set a constant at the top of the page
    const myTrue=1
    const myFalse=0
     
    Tom B, Nov 9, 2003
    #6
  7. Rob Meade

    middletree Guest

    So you're saying ('test','','','','','','','','','','',1)
    instead of ('test','','','','','','','','','','','1')?

    I thought all values entered in any insert or update statement had to be in
    single quotes. Oh well, I learn things ne all the time. Let's give it a
    shot.

    Nope. Same error. Interestingly, I got the same error in the browser. But
    when I ran it in Access, it gave me the same msg box as before, then asked
    if I wanted to go ahead and run the query anyway. This time, I clicke dYes,
    and it went ahead and put a value of Yes (True, 1, whatever) in the field.
     
    middletree, Nov 9, 2003
    #7
  8. Rob Meade

    middletree Guest

    Tried it, with and without the quotes, same error
     
    middletree, Nov 9, 2003
    #8
  9. Rob Meade

    middletree Guest

    Actually, I did post the exact error msg I was getting in the browser.
     
    middletree, Nov 9, 2003
    #9
  10. Rob Meade

    Ray at Guest

    Numeric values (or boolean) are't entered with ' delimiters. And in Access,
    date columns are delimited with #.

    Are ~all~ of the other columns a text-type of column either text or memo?
    What happens if you try:

    INSERT INTO Personal(FName,babies)VALUES ('test',1)

    If any of your columns don't allow nulls, this will also cause an error, but
    if not, try it. I'm going to guess that your conversion error has to do
    with your ContactTime column, which expects a date. And if that's the case,
    you' have to insert with ## delimiters.

    Ray at home
     
    Ray at, Nov 9, 2003
    #10
  11. Rob Meade

    middletree Guest

    You are correct. Actually, the field Firstbase is the Date/Time field. It
    was the culprit. I took it out, worked fine.
    Short answer is I could make it a field where people typ the date in
    manually, won't hurt anything in this case.

    But just for giggles, how is it supposed to work? I tried this:
    INSERT INTO Personal(FName,FirstBase,Gift2,Gift3,babies)
    VALUES ('test',#,'','',1)

    and this:

    INSERT INTO Personal(FName,FirstBase,Gift2,Gift3,babies)
    VALUES ('test',##,'','',1)

    in Access itself, and the msg box said there was a syntax error. Not a very
    helpful msg box.
     
    middletree, Nov 9, 2003
    #11
  12. Rob Meade

    Dan Brussee Guest

    I would set the date field to allow nulls and put a null there when
    you dont want a date.

    INSERT INTO Persona(FName, FirstBase, Gift2,
    Gift3, babies) VALUES ('test', null, '', '', 1)
     
    Dan Brussee, Nov 9, 2003
    #12
  13. Rob Meade

    Ray at Guest

    You'll have to insert a date into it, or leave it null by not including the
    column at all, if your DB allows it. Or, you can set a default value for
    that column, and if you don't define a value for it when a new record is
    inserted, the default value will be put in. If you aren't going to insert
    anything into that column, don't include it in your INSERT. If you include
    it, you have to give it a value, like #1/1/1900# or something.

    Ray at home
     
    Ray at, Nov 9, 2003
    #13
  14. Rob Meade

    middletree Guest

    Thanks. This has been informative. As usual.


     
    middletree, Nov 9, 2003
    #14
  15. Rob Meade

    ljb Guest

    Only 0 is false. Non 0 is true and that could be -1 or 1 or 99.
     
    ljb, Nov 10, 2003
    #15
  16. Rob Meade

    keyur shah Guest

    Use true/false....

    Keyur Shah
    Verizon Communications
    732-423-0745
     
    keyur shah, Nov 10, 2003
    #16
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.