Date Validation Procedure 1

[mamartin]

I need a date validation procedure. If someone can direct me to an example, that would be most nice. I am using VB 6.0 with a SQL Server 7.0 DB.

I have 2 dates on my user form: Open Date and Close Date. Within the DB table, they are defined as datetime. I am using a masked edit box to accept input in the format of dd-mmm-yyyy (can't use the date picker for various reasons). The Open Date must always be entered and the Close Date will only be entered when the ticket is closed out. The editing that should occur prior to writing the rec to the DB is:

Is Open Date a valid Date?
Is Close Date a valid Date or empty?
If Close Date is entered, is it greater than or equal to the Open Date?

When I write to the SQL DB table, if Close Date is not entered, I would like to see NULL in the table column.

Hope someone can be of help.

Michael A. Martin

 

[nicsin]

Try this

dim proceed as boolean
proceed = true

if isDate(openDate) then
    if isDate(closeDate) or (closeDate = "") then
        if closeDate <> "" then
            if cDate(closeDate) < cDate(openDate) then proceed = false
        end if
    else
        proceed = false
    end if
else
    proceed = false
end if

if proceed then
    'write to the database
else
    'Dates are not valid
end if

 

[mamartin]

nicsin,

thanks, that is working almost 100%. Just a couple more silly questions. Since I am using a masked edit box, the closeDate shows up as "__-___-____" during the validation proc. Is there a way to strip this out so I just see "" (for if comparison purposes) or should I check closeDate <> "__-___-____"?

How will this work for writing NULL to the DB table when the closeDate field is not entered?

Thanks again,

Michael A. Martin

 

[nicsin]

I assume you are refering to when the closeDate is empty huh? then you should check for the default value of the control.

as for the NULL in the database, I wouldn't recomend it but if you insist I can help you if you post your query here.

 

[mamartin]

Not sure what you mean by the "default value of the control", could you elaborate for me?

And, why do you not recommend writing NULL to the DB table? What would you recommend be written when the closeDate is not entered?

Thanks again,

Michael

 

[mmilan]

I think nicsin is referring to the mask...

 

[mamartin]

Yes, I guess then I should compare against "__-___-____" which is the type of mask I am using for the 2 date fields. Putting this check into the validation proc seems to work.

Next step, when I write the 2 date fields to the DB table, how should I format them. Especially if the closeDate is empty? I'm not married to using NULL for an empty string, just seems easier for me later on when I need to query on this field. I am using a simple INSERT statement to accomplish the DB table write.

Thanks all,

Michael

 

[nicsin]

Yes I am, sorry for he misunderstanding.

I don't usually post NULL values in the database. For dates I use 0 (zero). so instead of saying

insert into myTable values ("9/3/2004", null) you can say insert into myTable values ("9/3/2004", 0)

if you want to find the ones with no closeDate use

select * from myTable where closeDate = 0

 

[mamartin]

I set up a constant string (conNullDate) with a value of "01/01/1900" (seems that's what SQL writes anyway when the value is 0). In my Insert SQL statement, I do an IIF condition check on the mskCloseDate = "__-___-____". If true, then write conNullDate, if false, write mskCloseDate. Seems a bit cheesy, but it seems to work.

If you have any other suggestions, please share. Otherwise, thanks again for the assistance.

Michael

 

[nicsin]

It's the same thing but as the databases store date datatypes as integers, it is more logical to use 0 (since you know the value).

Good luck!

 

[LostInCode]

Since when does a database store date values as integers?
A date is store as a 8 byte foating point.

So, using 0 as a date is still a date.
-1 is also a date.

Store a NULL if there is no date to store.

So, there is a great difference in using NULL, and 0 for a date: The one is not any value, and the other is a real date.

That is the easiest and surest method to determine if a Date has been set, or if the user/code doesn't want to store a date.
Take this example:

dtImmob1_PurchaseDate = CDate("1899-12-30")
dtImmob1_SellDate = 0

'Has the immobilien been sold yet, and if so, then when?
Debug.? Format$(dtImmob1_SellDate,"Short Date")

You will find that the sell date was prior to the purchase date, when you may even not have expected it to have been sold yet!

Now, that's cheesy!

In your application, if you store missing dates as NULL in the database, you can then check if there is a date by using the IsNull(), or IsDate() functions on the recordset field.

Dim vDate As Variant

If IsDate(Text1.Text) then
vDate = CDate(Text1.Text)
Else
vDate = Null
End If

Or:

If IsDate(Text1.Text) then
rs.Fields("SomeDateField").Value = Format$(Text1.Text,"yyyy-mm-dd"))
Else
rs.Fields("SomeDateField").Value = NULL
End If

 

[nicsin]

LostInCode,

I appreciate your input but I will stick to my original post. Dates around 1900's are not of any interest whatsoever so we can safely assume that if a date is 0 or "01/01/1900" then it hasn't been set by the user. I have found from my little experience that having NULL values in a database can lead to problems. I've never used a variant before either...

regards,
nicsin

 

[strongm]

>I have found...[]...NULL values in a database can lead to problems

There are good arguments in their favour...

 

[LostInCode]

And I have never seen any real valid arguments against them.

If any, then due to a mis-use.

So, what does the rest of the world use?
Or does everyone else needing dates prior to 1900 just use and older date - like 16 billion BC to identify no date, or in the case of VB - 099 AD?

 

[nicsin]

strongm,

I'd really like to hear them since I've never listened to anyone supporting the use of nulls before.

LostInCode,

I think you are being unrealistic. Have you ever been in need to post dates 100 years old? Apart from that, I always prefer to give a default value to the variables in my code so I do the same when I post in a database. Maybe it is a matter of perspective and personal opinion but I consider a NULL as not a value, which holds no information at all. So yes I'rather set a field to 16 billion BC than using NULL but again...that's my opinion.

 

[LostInCode]

>Have you ever been in need to post dates 100 years old

Yes, quite often.

Take for instance the example I used. Quite realistic.