How to represent a blank date in a SQLite Date field?

Databases
#1

bConverted = ParseDate(tfSaleDate.Text, dSaleDate) if not bConverted then dSaleDate = nil end if rsRecall.Field("SaleDate").DateValue = dSaleDate

I have an SQLite database with a Field called “SaleDate” as a Date field. In an Edit Window the user can either enter a sale date or leave it blank. But I get an exception when the SaleDate is nil when I test it for “<> nil”. How do I represent “no or empty date” in a date column?

thanks,

#2

This should do it

bConverted = ParseDate(tfSaleDate.Text, dSaleDate) if not bConverted then dSaleDate = nil end if if dSaleDate is nil then rsRecall.Field("SaleDate").Value = nil else rsRecall.Field("SaleDate").DateValue = dSaleDate end if

#3

And reading it back,

if rs.Field("SaleDate").IsNull then
   dSaleDate = nil
else
   dSaleDate = rs.Field("SaleDate").DateValue
end
#4

I just ran into the same question!
I read this thread, but I never made it to work… I tried different alternatives, such as .NotNil and some other… with no luck!

#5

I don’t know what happen to the photo or how you edit previous posts!
(This forum is sort of silly…! Or, maybe it’s just me…)

Strange… this post can be edited, the correct post! But not the failing post, the one with the error… Oh, well. Life is not suppose to be easy!

#6

out of bounds - you’re accessing arrSERP(2,j) but thats not how you redim’d it

#7

Excuse me for hijacking someone-else’s thread… But really, I would name the thread the same myself!
I’ve included little more code in the image.
I’ve tried some (failing) alternatives… but really, my eyes don’t see the missing part!

#8

I look at your Redim and the first subscript within it is “1”. Now down in your code you have the first subscript of 2 in one one and 3 in the next. That is out of bounds because the Redim only allows for 0 or 1 as the first subscript.

#9

As Norman said [quote]you’re accessing arrSERP(2,j) but thats not how you redim’d it[/quote]

REDIM arrSERP(1, ...)   <- arrSERP has two "rows", 0 and 1

But later you have

arrSERP(2, ...)   <- OutOfBoundsException
#10

All right…!! Thank you!!
I’m thinking all backwards!!
I mix it all up!!
Now I can go on… thank you very much!!

#11

Thanks Norman and Tim, I always know I can get the answer here…

#12