Date data type NULL

[Valentine]

I have created a date variable in VBA ( DIM DTG as Date ) I want to fill that date by checking to see if the DTG is empty first to fill it with the first date my code comes accross. Thing is that the DTG variable is already filled with #12:00:00 AM# which isnt empty. Can i initialize the variable to make it empty before my if statement? I tried to do DTG = "" didnt work. I also tried DTG = Null that didnt work either.

 

[GPGeorge]

Why does it need to be "empty"? If you are going to set it to the first date your code comes across anyway, what difference does it make? Perhaps there's more to the requirement than stated so far?

 

[theDBguy]

Is #12:00:00 AM# a valid date in your situation? If not, just use it for your test.

In other words, treat #12:00:00 AM# as empty or null.

 

[Pat Hartman]

You need to use IsDate() or IsNull(). You CANNOT use = Null to check for nulls - EVER. And "" is a ZLS (Zero Length STRING" so since a date is a numeric data type it can NEVER be a string.

 

[Valentine]

OK thank you everyone I will use whats in the field as the blank check. Another question about the date is since the value inside is a time if I am trying to add in MM/DD/YYYY do I need to add that up when i created the DTG variable or will it add into the date variable how i want it?

 

[theDBguy]

OK thank you everyone I will use whats in the field as the blank check. Another question about the date is since the value inside is a time if I am trying to add in MM/DD/YYYY do I need to add that up when i created the DTG variable or will it add into the date variable how i want it?

What do you mean "add?" Just replace it. Right?

 

[Pat Hartman]

Are you sure that the field does not have a default value for date? Please show us the code you use to define the variable and all the code prior to when you check it for null.

 

[arnelgp]

you can Define your variable as Variant type:

dim DTG As Variant


then you can test if it has any value:

If IsEmpty(DTG) then
'no value yet
Else
'another code
End If

 

[Valentine]

Dim DTG as String
DTG = ""
If rsCNF![Need By Date] <> "" Then
    If DTG = "" Then
        DTG = rsCNF![Need By Date]
    ElseIf DTG > rsCNF![Need By Date] Then
        DTG = rsCNF![Need By Date]
    End If
ElseIf rsCNF![Need By Date] = "" Then
    If DTG = "" Then
        DTG = rsCNF![Validation Date]
    ElseIf DTG > rsCNF![Validation Date] Then
        DTG = rsCNF![Validation Date]
    End If
End If

I changed it to string so that i can use "" to make DTG empty. I also tried keeping it as Dim DTG as Date and using 12:00:00 AM as the check for empty. Nothing has worked it is not replacing the data.

 

[Valentine]

I figured it out. I had an extra ElseIf statement that was confusing. I removed the main If statement ElseIf and made it a simple Else now the date field is being populated with the proper date.

 

[plog]

Logically you've got a lot of superfluous code:

Dim DTG as String
DTG = ""
If rsCNF![Need By Date] <> "" Then
    If DTG = "" Then
        DTG = rsCNF![Need By Date]
    ElseIf DTG > rsCNF![Need By Date] Then
        DTG = rsCNF![Need By Date]
    End If
ElseIf rsCNF![Need By Date] = "" Then
    If DTG = "" Then
        DTG = rsCNF![Validation Date]
    ElseIf DTG > rsCNF![Validation Date] Then
        DTG = rsCNF![Validation Date]
    End If
End If

Is equivalent to this:

Dim DTG as String
DTG = ""
If rsCNF![Need By Date] <> "" Then 
  DTG = rsCNF![Need By Date]
ElseIf rsCNF![Need By Date] = "" Then
  DTG = rsCNF![Validation Date]
End If

Those nested elseif statements will never get hit. And its possible that DTG never gets assigned a value. I would thoroughly test out your code to make sure it realy does what you want.

 

[Valentine]

this If statement is nested inside a while loop that would go through multiple CNF[Need By Date] and select the oldest and put that one in the DTG variable.

I am now having trouble with the next step of the code.

If DTG > Date Then
    tmpOnTime = (tmpOnTime +1)
ElseIf DTG < Date & DTG <= (Date + 30) Then
    tmp30 = (tmp30 + 1)
ElseIf DTG <= (Date + 31) & DTG <= (Date + 90) Then
    tmp31 = (tmp31 + 1)
ElseIf DTG <= (Date + 91) & DTG <= (Date + 365) Then
    tmp91 = (tmp91 + 1)
ElseIf DTG <= (Date + 366) Then
    tmpYear = (tmpYear + 1)
End If

 

[plog]

AND is the logical operator for multiple conditions, not &
You do not even need that because al your first conditions are proper subsets of the 2nd condition:

ElseIf DTG <= (Date + 31) & DTG <= (Date + 90) Then

Should be

ElseIf DTG <= (Date + 90) Then

If DTG is less than Date +31 its less than Date +90 Then

 

[Valentine]

shoot did I put my less than and greater than wrong? I am trying to do between dates

 

[Pat Hartman]

Let's go back. At one point Plog had DTG defined as a string. Please tell me you do not have the date defined as a string.

 

[The_Doc_Man]

Let's go back for a moment to explain something. Variables of types other than Variant will not contain a NULL. They cannot do so. NULL is normally the result of a bound field for which something prevents proper selection of a record. (Not the ONLY way to get a NULL, but the most common.) All declared (DIM, PRIVATE, PUBLIC, etc.) variables have a default which, for numeric types, is 0. For strings, the "empty" string also called the zero-length-string or "" (double quotes with nothing between the quotes).

The Date data type is stored as a numeric type (DOUBLE) because Date is what is called a TypeCast of DOUBLE. "TypeCast" is another way of saying "alternate interpretation." Access stores dates in a DOUBLE that expresses the elapsed time (in days and fractions of a day) since the Access reference time of 12-30-1899 at midnight. Which means that a 0 value in a date "maps" to midnight of 12-30-1899. Therefore, if you impose a Time format on a Date field that contains 0, you would get... midnight. If your regional settings do not have Military time format selected, you won't see 00:00:00 for midnight; you will see 12:00:00, which explains what you saw.

You can impose both date and time formats on a Date data-type variable when outputting it, but it will always be stored numerically. For instance, this is a legal format of a date/time: FORMAT( DTG, "dd-mmm-yyyy hh:nn:ss" ) - if you set DTG to the time I was typing this response, you would see a value of 11-Feb-2022 13:28:50 (roughly speaking).

While we are at it... PLog pointed this out but I will emphasize it.

ElseIf DTG < Date & DTG <= (Date + 30) Then

is wrong because & is a concatenation operator. You cannot concatenate numeric values that way - numeric concatenation is not defined for VBA. You can concatenate formatted strings derived from a numeric variable, but not the numeric variables themselves. In this instance, you need AND written out because in ENGLISH, & is "and" but in programming it is not always "and" - it is a string conjunction.

 

[Pat Hartman]

I'm pretty sure that if you concatenate Date and DTG, Access will convert the two dates to strings and concatenate the strings.

 

[The_Doc_Man]

Actually, never tried that, Pat, and don't doubt you. But even if it does it silently, the resulting string, being the concatenation of TWO dates, will probably not be particularly useful.

 

[Pat Hartman]

I agree, the result would not be useful

 

[Valentine]

Thank you everyone for the responses. I went with using the 12:00:00 AM as the base line the "Zero" if you will so in my if statement i just put that value in for if blank. The "And" and "&" debate ended up only working with AND in between, Access wouldn't let me use & it gave me an operator error.