none
Does Access have a date place holder? RRS feed

  • Question

  • Using Access 2003 sp3

    Under certain conditions a table I use will have a date in a date field, at times the date field will be null.

    I want to load the date into a date variable, but when the field is null, is there a value I can assign the date to indicate that?

    I get an 'invalid use of null' error when I try to assign the date value and the date is null. In that case, is there a value I can assign to the date to indicate it is empty? In FoxPro there was a blank date ('  /  /  ') one could use, is there something that will work in Access?

    Thanks in advance for any help.  --Fred

    Friday, April 14, 2017 4:44 PM

Answers

  • Hi Fred,

    If you truly want to use Null in your code, then you'll need to declare the variable as Variant rather than Date. Otherwise, you can use an arbitrary date to indicate an empty record. For example, you can use something like #1/1/100#, or today's date, or a date too far in the future like #12/31/9999#.

    Just my 2 cents...

    • Marked as answer by Fredrated451 Friday, April 14, 2017 5:06 PM
    Friday, April 14, 2017 4:49 PM

All replies

  • Hi Fred,

    If you truly want to use Null in your code, then you'll need to declare the variable as Variant rather than Date. Otherwise, you can use an arbitrary date to indicate an empty record. For example, you can use something like #1/1/100#, or today's date, or a date too far in the future like #12/31/9999#.

    Just my 2 cents...

    • Marked as answer by Fredrated451 Friday, April 14, 2017 5:06 PM
    Friday, April 14, 2017 4:49 PM
  • Thanks theDBguy, when I switched the variable type to variant it does what I want.

    Much obliged.

    Friday, April 14, 2017 5:06 PM
  • Hi,

    You're welcome! Glad to hear you got it sorted out. Good luck with your project.

    Friday, April 14, 2017 5:09 PM