Solved

isdate function

Posted on 2011-02-23
12
547 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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
 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

776 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