isdate function

I am working in access 10 vba.

I have a function that needs to evaluate if the value passed isnull OR is not a date and set a string accordingly

a case is then used that will apply tests at various stages.

The isdate() does not work
I have 4 variables that are strings representing dates

I cannot do the test i want to do if the vaulue passed isnull or not a valid date

I added the nz here

'çheck if date
dateSmmtIntroCheck = IsDate(CDate(Nz(ClientIntroIn, 0)))

then passed a null to see what would happen and dateSmmtIntroCheck returns true

?datethrough(null,01/08/2000,null,01/03/2000)

Public Function DateThrough(ClientIntroIn As Variant, ClientTermIn As Variant, SMMTIntroIn As Variant, SmmtTermIn As Variant) As Boolean

Dim OutputLogic As Boolean
Dim SmmtIntroGiven As Boolean
Dim SmmtTermGiven As Boolean
Dim ClientIntroGiven As Boolean
Dim ClientTermGiven As Boolean
Dim dateCheck As Boolean
Dim BinaryString As String
Dim dateSmmtIntroCheck As Boolean
Dim dateSmmtTermCheck As Boolean
Dim dateClientIntroCheck As Boolean
Dim dateClientTermCheck As Boolean

'çheck if date
dateSmmtIntroCheck = IsDate(CDate(Nz(ClientIntroIn, 0)))
dateSmmtTermCheck = IsDate(CDate(ClientTermIn))
dateClientIntroCheck = IsDate(CDate(SMMTIntroIn))
dateClientTermCheck = IsDate(CDate(SmmtTermIn))


'çheck if null values or non date values passed
If (IsNull(ClientIntroIn) = True) Or (dateClientIntroCheck = False) Then

    ClientIntroGiven = False
    BinaryString = "0"
Else
    ClientIntroGiven = True
    BinaryString = "1"
End If

If (IsNull(ClientTermIn) = True) Or (dateClientTermCheck = False) Then

    ClientTermGiven = False
    BinaryString = BinaryString & "0"
Else
    ClientTermGiven = True
    BinaryString = BinaryString & "1"
End If

If (IsNull(SMMTIntroIn) = True) Or (dateSmmtIntroCheck = False) Then
    SmmtIntroGiven = False
    BinaryString = BinaryString & "0"
Else
    SmmtIntroGiven = True
    BinaryString = BinaryString & "1"
End If


If (IsNull(SmmtTermIn) = True) Or (dateSmmtTermCheck = False) Then
    SmmtTermGiven = False
    BinaryString = BinaryString & "0"
Else
    SmmtTermGiven = True
    BinaryString = BinaryString & "1"
End If

'take relevant action
Select Case BinaryString

Case "0000": OutputLogic = True
Case "0001": OutputLogic = True
Case "0010": OutputLogic = True
Case "0011": OutputLogic = True
Case "0100": OutputLogic = True
Case "0101"

OutputLogic = DateInvariance(ClientTermIn, SmmtTermIn, ReturnTermcount())
Case "0110"
Case "0111"
Case "1000": OutputLogic = True
Case "1001"
Case "1010"
Case "1011"
Case "1100": OutputLogic = True
Case "1101"
Case "1110"
Case "1111"

End Select



DateThrough = OutputLogic

End Function

Open in new window

PeterBaileyUkAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
That is correct. 0 certainly is a valid date (value).

/gustav
0
 
Gustav BrockCIOCommented:
You have turned it upside down. If a value can be converted by CDate, of course IsDate will return true.

'çheck if date
dateSmmtIntroCheck = IsDate(ClientIntroIn)

/gustav
0
 
peter57rConnect With a Mentor Commented:
I would have thought it sufficient just to test for isdate..

isdate(clientintroin)

Null values should return false .
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
PeterBaileyUkAuthor Commented:
so my code is essentially correct then?

I can see that the cdate is a good argument otherwise cdate would have squeeled.

ok I have removed cdate.

how is 0 a valid date? I had hoped a valid date would be anything from 1/1/yyyy to 31/12/yyyy

invalid being 30/4/2011 for example

maybe i worry too much!
0
 
PeterBaileyUkAuthor Commented:
it appears now thw cdate is gone that the isdate just gives false certainly passing this it does:

?datethrough(null,01/08/2000,null,01/03/2000)
0
 
Gustav BrockCIOCommented:
No, your code must be corrected as shown and could be simplified a lot.

0 is a valid date value:

? CDate(0)
1899-12-30

So is "30/4/2011". CDate will read that as 2011-04-30.

Have in mind that CDate tries to be clever. If an expression can be understood as a date, it is accepted. It will not validate a range, a specific format or anything else.

Don't pass math like 1/8/2000 (numeric) to the function if you mean a string.

?datethrough(null,"01/08/2000",null,"01/03/2000")

/gustav
0
 
PeterBaileyUkAuthor Commented:
this ?datethrough(null,"01/08/2000",null,"30/04/2000")

now returns the correct things

the code is now as attached is there room for improvement or something i have missed thats blazingly obvious to you?


Public Function DateThrough(ClientIntroIn As Variant, ClientTermIn As Variant, SMMTIntroIn As Variant, SmmtTermIn As Variant) As Boolean

Dim OutputLogic As Boolean
Dim SmmtIntroGiven As Boolean
Dim SmmtTermGiven As Boolean
Dim ClientIntroGiven As Boolean
Dim ClientTermGiven As Boolean
Dim dateCheck As Boolean
Dim BinaryString As String
Dim dateSmmtIntroCheck As Boolean
Dim dateSmmtTermCheck As Boolean
Dim dateClientIntroCheck As Boolean
Dim dateClientTermCheck As Boolean

'çheck if date
dateSmmtIntroCheck = IsDate(ClientIntroIn)
dateSmmtTermCheck = IsDate(ClientTermIn)
dateClientIntroCheck = IsDate(SMMTIntroIn)
dateClientTermCheck = IsDate(SmmtTermIn)


'çheck if null values or non date values passed
If (IsNull(ClientIntroIn) = True) Or (dateClientIntroCheck = False) Then

    ClientIntroGiven = False
    BinaryString = "0"
Else
    ClientIntroGiven = True
    BinaryString = "1"
End If

If (IsNull(ClientTermIn) = True) Or (dateClientTermCheck = False) Then

    ClientTermGiven = False
    BinaryString = BinaryString & "0"
Else
    ClientTermGiven = True
    BinaryString = BinaryString & "1"
End If

If (IsNull(SMMTIntroIn) = True) Or (dateSmmtIntroCheck = False) Then
    SmmtIntroGiven = False
    BinaryString = BinaryString & "0"
Else
    SmmtIntroGiven = True
    BinaryString = BinaryString & "1"
End If


If (IsNull(SmmtTermIn) = True) Or (dateSmmtTermCheck = False) Then
    SmmtTermGiven = False
    BinaryString = BinaryString & "0"
Else
    SmmtTermGiven = True
    BinaryString = BinaryString & "1"
End If

'take relevant action
Select Case BinaryString

Case "0000": OutputLogic = True
Case "0001": OutputLogic = True
Case "0010": OutputLogic = True
Case "0011": OutputLogic = True
Case "0100": OutputLogic = True
Case "0101"

OutputLogic = DateInvariance(ClientTermIn, SmmtTermIn, ReturnTermcount())
Case "0110"
Case "0111"
Case "1000": OutputLogic = True
Case "1001"
Case "1010"
Case "1011"
Case "1100": OutputLogic = True
Case "1101"
Case "1110"
Case "1111"

End Select



DateThrough = OutputLogic

End Function

Open in new window

0
 
Helen FeddemaCommented:
Use IsDate() on the raw value, and if it passes, then use CDate to set a date variable.  Here is some sample code:
If IsDate(Me![txtToDate].Value) = True Then
   dteToDate = CDate(Me![txtToDate].Value)
End If

Open in new window

0
 
Helen FeddemaConnect With a Mentor Commented:
No need to check for Nulls separately, since a Null is not a date
0
 
Gustav BrockConnect With a Mentor CIOCommented:
That's not needed. The datexxx variables are Booleans.

/gustav
0
 
PeterBaileyUkAuthor Commented:
ok I amended as advised so i think its ok now
Public Function DateThrough(ClientIntroIn As Variant, ClientTermIn As Variant, SMMTIntroIn As Variant, SmmtTermIn As Variant) As Boolean

Dim OutputLogic As Boolean

Dim BinaryString As String
Dim ClientIntrodteToDate As Date
Dim ClientTermdteToDate As Date
Dim SmmtIntrodteToDate As Date
Dim SmmtTermdteToDate As Date



'çheck if null values or non date values passed
If IsDate(ClientIntroIn) = True Then
   ClientIntrodteToDate = CDate(ClientIntroIn)
   BinaryString = "1"
Else
   BinaryString = "0"
End If

If IsDate(ClientTermIn) = True Then
   ClientTermdteToDate = CDate(ClientTermIn)
   BinaryString = BinaryString & "1"
Else
   BinaryString = BinaryString & "0"
End If


If IsDate(SMMTIntroIn) = True Then
   SmmtIntrodteToDate = CDate(SMMTIntroIn)
   BinaryString = BinaryString & "1"
Else
   BinaryString = BinaryString & "0"
End If

If IsDate(SmmtTermIn) = True Then
   SmmtTermdteToDate = CDate(SmmtTermIn)
   BinaryString = BinaryString & "1"
Else
   BinaryString = BinaryString & "0"
End If


'take relevant action
Select Case BinaryString

Case "0000": OutputLogic = True
Case "0001": OutputLogic = True
Case "0010": OutputLogic = True
Case "0011": OutputLogic = True
Case "0100": OutputLogic = True
Case "0101": OutputLogic = DateInvariance(ClientTermdteToDate, SmmtTermdteToDate, ReturnTermcount())
Case "0110"
Case "0111"
Case "1000": OutputLogic = True
Case "1001"
Case "1010"
Case "1011"
Case "1100": OutputLogic = True
Case "1101"
Case "1110"
Case "1111"

End Select



DateThrough = OutputLogic

End Function

Open in new window

0
 
Gustav BrockCIOCommented:
Much better if you ask me.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.