Blanking a date field

[mdweezer]

I have a simple variable defined as date:
Public GenerationDateValue As Date

I assign data to it using:
GenerationDateValue = Nz(Generation_Date.Value)

However in the following code whe executed it puts in: 12:00:00 AM for its value in it making it seem as if there is data in there even when I leave the field blank and mean for it to be ignored by the following code:

If IsNull(GenerationDateValue) = False And IsNull(ToDateValue) = True Then
        FirstChecked = True
        qry = qry & "AND (((GenerationDate) LIKE '*" & GenerationDateValue & "*')) "
    ElseIf IsNull(GenerationDateValue) = False And IsNull(ToDateValue) = False Then
        GenerationDateValue = GenerationDateValue - 1
        ToDateValue = ToDateValue + 1
        FirstChecked = True
        qry = qry & "AND (((GenerationDate) > #" & GenerationDateValue & "#)) "
        qry = qry & "AND (((GenerationDate) < #" & ToDateValue & "#)) "
    End If

If the field is blank, shouldn't the NZ function just make it null and not fill in 12:00:00 AM for the value? Thanks

 

[idbr]

mdweezer,

The nz function converts null values to 0 - hence your problem. 0 is the time serial for 12:00.00 if I remember rightly...

Iain

 

[SkipVought]

Check the properties of your date field.

Can it be NULL or MUST you have a value???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[mdweezer]

I am passing a value to it from a field, if the field has a date in it, then it carries over the value, if the field is blank I want the variable NULL, as we figured out NZ can't do what I need it to do, so what are my other options for passing data to it and if there is no data keeping it null?