Solved

isdate function

Posted on 2011-02-23
12
545 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
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 49

Accepted Solution

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

/gustav
0
 
LVL 49

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
 

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 49

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 49

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 49

Expert Comment

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

/gustav
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now