Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing Null Date to MS Access Table (VB6 / Access 97)

    Here's the string, and the value in question is tdbDateDX.Value:

    cn.Execute ("INSERT INTO tblPatient (MRNumber, COG_ID, " _
    & "DOB, Gender, PMD, " _
    & "CRA, Diagnosis, DXDate, " _
    & "FirstName, lastname) " _
    & "VALUES('" & tdbMRN.Value & "', '" & TDBCOG.Text & "', '" & TDBDOB.Value & "' " _
    & ", '" & cboGender.Text & "', '" & TDBPMD.Text & "', '" & TDBCRA.Text & "' " _
    & ", '" & tdbDiag.Text & "', '" & IIf(TDBDateDX.Value = vbNullString, "", TDBDateDX.Value ) & "', '" & TDBFname.Text & "' " _
    & ", '" & TDBLname.Text & "') "), , adCmdText


    As you can see I tried wrapping it in an immediate if statement to try passing a null string or blank string. I've tried both variations of this and neither worked.

    If I edit this table using Access 97 (where the table resides) it has no problem. Does anyone know how to pass a blank date to Access from Visual Basic?

  2. #2
    Star Lounger
    Join Date
    Nov 2001
    Location
    Illinois, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Null Date to MS Access Table (VB6 / Access 97)

    Use the ISNULL function instead of testing for a Null String. IIF(ISNULL(TDBDateDx......

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Null Date to MS Access Table (VB6 / Access 97)

    I tried that like you suggested, same datatype error. It is almost as if Access doens't mind storing Nulls in a date field, but it wont allow any external process doing it.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing Null Date to MS Access Table (VB6 / Access 97)

    Date fields are a special type of variant, so they can contain nulls. However, it looks to me as if you're delimiting the result in your SQL string with single quotes, which effectively converts that value to a string, not a date.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Null Date to MS Access Table (VB6 / Access 97)

    Okay, I think you nailed it. I went through all my datatypes and made sure they were correct.

    I really appreciate all your help. This is the first full project I've written in VB from start to finish. It's been a small adjustment form Access programming.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Null Date to MS Access Table (VB6 / Access 97)

    Well, it works the same as when I put in a valid date encased in quotes. What is interesting now when I pass it a null value, it returns an error messsage "Syntax error (missing operator) in query expression '12:00:44 AM'. When I go into my execute statement from the connection object, when I put my cursor over the field value I'm passing in it is just returnning NULL.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing Null Date to MS Access Table (VB6 / Access 97)

    The fact that you're seeing a time in the error message suggests that you aren't really passing Nulls into the SQL expression, even though you think you are. That means that you're going to have to either use a CDate function to convert the value to a true date or use # delimiters so that the value gets handled properly as a date.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Null Date to MS Access Table (VB6 / Access 97)

    I agree.

    I think I tried CDate before and it errored when it was passed a Null value. I've also tried the #'s on each end of the variable in place of the quotes. I get a syntax error. I'm trying # & TDBDateDX.Text & #

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing Null Date to MS Access Table (VB6 / Access 97)

    Um, ar you putting the # inside the quotes? That's where they belong, but you'll still get an error if the field is null. The Text property is usually the one you use in VB, which is confusing for those of us who normally use Access. Have you tried this?

    "', " & IIf(IsNull(TDBDateDX.Text) OR TDBDateDX.Text = vbNullString, Null, "#" & TDBDateDX.Text & "#") & ", '
    Charlotte

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Null Date to MS Access Table (VB6 / Access 97)

    I had to change the .Text to .Value. It is now returning a generic error stating Error in UPDATE statement.

    Does it matter that I am using an after-market control, it is a True Database Date Control. If this is important, I'm really sorry for not saying so in the first place, it just slipped my mind. I was able to validate that it is setting the value to Null when there is nothing in the field.

    I'm at the point of using an If statement to test my two date fields for Null values and run different queries depending on the condition. It just seems ridiculous that I should have to resort to that.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing Null Date to MS Access Table (VB6 / Access 97)

    Yes, it may matter that you're using a third-party control. Each custom control has its own set of properties and methods and its own behaviors that are not seen in the built in controls.

    On the other hand, the SQL should behave the same way regardless. But where did the UPDATE come from? The SQL you posted was for an append query, not an update in sight. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Charlotte

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Null Date to MS Access Table (VB6 / Access 97)

    Oops, sorry about that. And neat graphic BTW (and very descriptive).

    I am having the same issue with inserting a new record as I am with the update. The fields that I am adding values to or updating are identical, so if I fix the one I'll be able to fix the other. Still, it would have been nice if I had been consistent [img]/forums/images/smilies/sad.gif[/img]

    I have to agree with you that the SQL shouldn't care what type of control it is. The control is definitely passing a NULL in its value property, so I should have the same problem even if I were to use a TextBox.

    I'm posting the UPDATE statement below with the modifications you suggested (that returns an Error in UPDATE statement error)

    cn.Execute ("UPDATE tblPatient SET tblPatient.LastName = '" & TDBLname.Text & "', " _
    & "tblPatient.FirstName = '" & TDBFname.Text & "', tblPatient.COG_ID = '" & TDBCOG.Text & "', " _
    & "tblPatient.DOB = '" & TDBDOB.Value & "', tblPatient.Gender = '" & cboGender.Text & "', " _
    & "tblPatient.PMD = '" & TDBPMD.Text & "', tblPatient.CRA = '" & TDBCRA.Text & "' , " _
    & "tblPatient.DID = '" & cboDiag.Text & "', " _
    & "tblPatient.DXDate = " & IIf(TDBDateDX.ValueIsNull, Null, "#" & TDBDateDX.Value & "#") & " " _
    & "WHERE tblPatient.MRNumber = " & sOldMRN), , adCmdText

    Oh, you will noice that this control has a .ValueIsNull property. This returns a boolean and works the same as the IsNull test, but I tried that as well.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing Null Date to MS Access Table (VB6 / Access 97)

    Aarrgghh!! Without the underlying data structures, it's impossible to really test this kind of thing. One last suggestion (before I go soak my head) is that in creating a SQL string like this, you can't pass it a true Null as a value, you have to pass it "Null", so that the word itself is concatenated to the SQL string. What happens if you change this:

    IIf(TDBDateDX.ValueIsNull, Null, "#" & TDBDateDX.Value & "#")

    To this:

    IIf(TDBDateDX.ValueIsNull, "Null", "#" & TDBDateDX.Value & "#")
    Charlotte

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Null Date to MS Access Table (VB6 / Access 97)

    Charlotte, that worked! You nailed it! Gezzzzzz, I would have never come up with passing a Null as a string.

    Do normal programmers use a recordset object to do adds and updates? The execute method of the connection object seemed the simplist way (and boy, did I call that one!).

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing Null Date to MS Access Table (VB6 / Access 97)

    As far as I know, there's no such thing as a "normal" programmer. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    As for the rest of your question, it depends on the circumstances and on what you mean. You tend to use a recordset and address individual fields when you need to manipulate the data on a field by field basis or when you need to work with a disconnected recordset, etc. You can also use it in ADO to simply run the update. I wasn't sure which one you were talking about. Executing a SQL statement is usually more direct but lacks the "tuneability" of the recordset .AddNew or .Edit approach.

    And BTW, the Command object also has an execute method. I can never remember offhand the pros and cons of which object to use when, but look into the Command object as well, especially if you need to reuse the command.
    Charlotte

Posting Permissions

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