Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2012

    Red face Unanswered: error in null or blank date field in select stmt

    Help! Im a newbie in vba programmingand encountering errors thruout my access application on this particular issue.

    I have an insert statement where Im taking data from a textbox with a date value on an unbound form and on my save pushbutton inserting the value into a table. Sometimes for this particular textbox (txtDOB.value) there is a date, and sometimes there is not. The field name in the table is named DOB. The table is Decedent.

    My code is as follows (it is in the middle of the insert statement, so I'm just highlighting the particular line of code that is germaine:

    strsql2 = strsql2 & IIF(ISNULL(txtDOB.value), " ' ' ", "#" & (txtdob.value & "#") & ", "
    it works fine if the textbox isnt blank.

    So, I tried this inside of my Insert statement.....

    If Isnull(txtdob.value) or Len(trim(txtdob.value)) <1 then
    strsql2 = strsql2 & "DOB = null" & ", "
    strsql2 = strsql2 & "DOB = #" & txtdob.value & "#, "
    End if

    That still gives me an error if the field/text box is blank. Again, it inserts correctly if the text box contains a date value. Any help in fixing the first line of of code or the above if..end if inside of my Insert statement would be greatly appreciated.

    Thanks cmorri
    Last edited by cmorri; 12-21-12 at 11:04.

  2. #2
    Join Date
    Mar 2009
    Gatineau, Quebec Canada
    Provided Answers: 1

    Date Insert Problem

    I am using MS Access 10 with an insert statement, and simply test the date test box thus:

    if isNull(txtDOB) then
    txtDOB = #1/1/1899#
    end if

    Never fails. Putting in letters results in a system error message. Adding a space? Treated as a "null" by the system.

    Perhaps you are having a problem using an IIF statement in a procedure.

    J. Smith
    Aylmer, Quebec

Posting Permissions

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