Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

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

0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 5
  • 4
  • 2
  • +1
4 Solutions
 
Gustav BrockCIOCommented:
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
 
peter57rCommented:
I would have thought it sufficient just to test for isdate..

isdate(clientintroin)

Null values should return false .
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 FeddemaCommented:
No need to check for Nulls separately, since a Null is not a date
0
 
Gustav BrockCIOCommented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now