Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1

    Answered: Insert Null Values in date Fields

    Greetings again,

    Still volunteering to develop a database for a local food bank. Still, unfortunately, doing it alone (would love to program with others).

    Access does not appear to allow insertion of a null value in a date field via raw SQL ( i.e. docmd. runsql + an INSERT INTO statement). The ugly solution I have come up with is to test for a null field (or a non-date field), and if either is true, substitute with a dummy field ( 1/1/1900). Works, but is ugly; I would much prefer to leave a blank field. I also don't want to start switching between date, string and variant field types (the latter two of which permit null fields), especially because I have to deal with different regional date formats (i.e. French and English).

    I have yet to find a workable solution for inserting a null value into a date field (after an hour Googling). Any ideas? There must be something out there: if I move a record into a bound form, and then delete a date field -- NO PROBLEM. Bound forms, I gather, accept and work with null date fields.

    J. Smith
    Gatineau, Quebec, Canada

  2. Best Answer
    Posted by Sinndho

    "If the concerned column allows NULLs ("Required" property of the column in the Table Designer: see Healdem's post), this works in Access:
    Code:
    INSERT INTO [SomeTable] ([DateColumn]) VALUES (NULL);
    Or:
    Code:
    UPDATE [SomeTable] SET [DateColumn] = NULL;
    "


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ?
    check the definition of the column in the table. make certain that
    1) there is no default value
    2) the required is set to No
    3) check if the column is included in an index (this may cause problem if its a unique index or primary key
    check that the control definition has no default value, validation rule

    if you are not using bound controls and a problem persists then a workaround would be not to update / set the date column(s) if they are not within a specific range

    just a thought you may need to coerce the value of the control to be NULL as opposed to ""
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    Hi,

    Thanks for the reply. Checked the columns, they nothing set in them. I played around with using "Null", Null, #null#, 0, " ", all to no effect. Other databases (MySQL, etc) actually have NOT NULL (and NULL) as a column criteria -- not Access -- I could find nothing that permits us to allow a null in a date field. So how is it possible to do so with a bound form? Another of life's little mysteries!

    Regards

    John S

  5. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the concerned column allows NULLs ("Required" property of the column in the Table Designer: see Healdem's post), this works in Access:
    Code:
    INSERT INTO [SomeTable] ([DateColumn]) VALUES (NULL);
    Or:
    Code:
    UPDATE [SomeTable] SET [DateColumn] = NULL;
    Have a nice day!

  6. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    JET uses isnull function to test if a result is NULL
    I'd rather be riding on the Tiger 800 or the Norton

  7. #6
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    As I have no life, I set up a test form at 7:00 in the morning. Indeed, it works if you force a "NULL" during the INSERT. I found it easiest to pretest for a null or an invalid date (isdate), then set a string variable to either "Null" or "#" & txtAvalue.value & "#" as the case requires, and then simply use the variable in the INSERT statement. Probably is an easier way using an IIF statement directly in the INSERT statement, but it gets confusing with all the ' ' ' ' and " " " "!

    Thanks again. A growth experience.

    John S
    Gatineau, Quebec

  8. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by praxis1949 View Post
    ....but it gets confusing with all the ' ' ' ' and " " " "!
    if you get confused with " & ' (and you are not the only one) when delimiting strings then consider using the CHR() function to return the symbol you want. as far as I know it makes no difference to your actual code in terms of performance but can make it easier to read
    chr(34) is "
    chr(39) is '
    a full list of ASCII symbols in VBA can be found here

    eg
    Code:
    Dim firstname as string
    dim surname as string
    dim city as string
    dim province as string
    dim sql as string
    forename = "John"
    surname = "ssssss"
    city = "Gatineau"
    province = "Quebec"
    
    sql = "insert into mytable (fname,sname,Dob,city,prov) values (" & _
        chr(34) &  forename & chr(34) & "," & _
        chr(34) &  surname & chr(34) & "," & _
        chr(35) &  "1960/04/21" & chr(35) & "," & _
        chr(39) &  city & chr(39) & "," & _
        chr(39) &  province & chr(39) & ")"
    so sql should equate to
    Code:
    insert into mytable (fname,sname,Dob,city,prov) values ("john", "sssss", #1960/04/21#, 'Gatineau', 'Quebec')
    note that you need to be clever about which delimiter to use OR escape it. this applies to, say surnames such as O'Kelly
    Code:
    surname = "O'Kelly"
    ....
        chr(39) &  replace(surname,"'","/'") & chr(39) & "," & _
    ...
    ..this escapes the quote mark in the surname variable if present. if you use the " as delimters you dont' need to worry about quote marks in the data stream. quote marks tare, IIRC the SQL standard way of delimiting text, but Access allows you to use either. but if you text includes say a US style measurement of feet & inches eg "height is 6'4"" you will have problems either way
    Code:
    dim thisheight as string
    myheight = repalce(replace("thisheight","'","/'"),'"','/"')
    I'd rather be riding on the Tiger 800 or the Norton

  9. #8
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    147
    Provided Answers: 1
    This is the answer to the question -- force a NULL!

Tags for this Thread

Posting Permissions

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