dcsimg
Results 1 to 5 of 5

Thread: VS 2015 OLEDB search for NULL date in Access database

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    3

    VS 2015 OLEDB search for NULL date in Access database

    As usual, I have trawled the web looking for an answer to my problem, but come up empty. I know someone here will have the answer.

    I have a Windows Form application in VS 2015 that accesses an Access database. Everything works fine until I try to ask it to pull records that have an empty date field.

    My code is:
    Code:
     stoDb = New OleDbDataAdapter("SELECT * From [Stock] WHERE conArchive > #" & ArchiveDate & "# OR stoSDate IS NULL", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=x.accdb")
    The conArchive and ArchiveDate parts work okay. The problem come when I add the OR stoDate IS NULL. I get "No value given for one or more required parameters."

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,349

    Re: VS 2015 OLEDB search for NULL date in Access database

    Welcome to VBForums

    You added that part of the SQL statement correctly, so the error implies that you haven't got a field with that name in the table... which seems likely to be the case, because in the SQL statement you called it stoSDate , but in your post you wrote stoDate


    By the way, the ArchiveDate part is likely to behave incorrectly in some circumstances outside of the control of your program, if you use Parameters that issue (and several others) disappear.

    For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of the Database Development forum).

  3. #3

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    3

    Re: VS 2015 OLEDB search for NULL date in Access database

    Thanks. I'll get to the parameters later.
    stoDate was my typo.
    The field in the database is stoSDate and it is a "date" field.

  4. #4

    Thread Starter
    New Member
    Join Date
    Dec 2018
    Posts
    3

    Re: VS 2015 OLEDB search for NULL date in Access database

    Thanks. I'll get to the parameters later.
    stoDate was my typo.
    The field in the database is stoSDate and it is a "date" field.

  5. #5
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,312

    Re: VS 2015 OLEDB search for NULL date in Access database

    The problem is that to search for null - you need to change the where clause sql....
    Code:
    WHERE conArchive > #" & ArchiveDate & "#"
    to
    Code:
    WHERE conArchive IS NULL
    Otherwise I think Access just errors because null or "" is not a date...

    You could perhaps us Nz(xxx,yyy) function to convert to todays date or a future date, or a past date...
    Otherwise you'd need to change the sql on the fly or use an if to change the sql before passing it to the data adapter.

    If you use parameters, I'm not sure if you'd still end up with the same problem orr whether the engine would be able to parse it properly the way you want.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width