Solved

isdate function

Posted on 2011-02-23
12
552 Views
Last Modified: 2012-08-13
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
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 34961591
That is correct. 0 certainly is a valid date (value).

/gustav
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 34961636
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
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 125 total points
ID: 34961671
I would have thought it sufficient just to test for isdate..

isdate(clientintroin)

Null values should return false .
0
Independent Software Vendors: 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!

 

Author Comment

by:PeterBaileyUk
ID: 34961684
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
 

Author Comment

by:PeterBaileyUk
ID: 34961756
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
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 34961794
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
 

Author Comment

by:PeterBaileyUk
ID: 34963133
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 34963194
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
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 125 total points
ID: 34963195
No need to check for Nulls separately, since a Null is not a date
0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 34963305
That's not needed. The datexxx variables are Booleans.

/gustav
0
 

Author Comment

by:PeterBaileyUk
ID: 34963774
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
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 34965201
Much better if you ask me.

/gustav
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question