use of isdate

[Guest]

I would like to Check to see that ending month is later than beginning month.
How do I do that. because what I have is Checking to see if the ending
date is later than beginning date as follows: (where begindate and enddate
are unbound textboxes on a form now my form has 2 unbound textboxes with
beginMonth and EndMonth. Months are formated as "mmmm yyyy" )

If IsDate(BeginDate) And IsDate(EndDate) Then
If EndDate < BeginDate Then
MsgBox "The ending date must be later than the beginning date."
Exit Sub
End If
Else
MsgBox "Please use a valid date for the beginning date and the
ending date values."
End If

 

[Allen Browne]

Try using CDate() to convert the entries to actual date values:

If IsDate(BeginDate) And IsDate(EndDate) Then
If CDate(EndDate) < CDate(BeginDate) Then
MsgBox "The ending date must be later than the beginning date."
Exit Sub
End If
Else
MsgBox "Please use a valid date"
End If

 

[Rick Brandt]

I would like to Check to see that ending month is later than
beginning month. How do I do that. because what I have is Checking
to see if the ending
date is later than beginning date as follows: (where begindate and
enddate are unbound textboxes on a form now my form has 2 unbound
textboxes with beginMonth and EndMonth. Months are formated as "mmmm
yyyy" )

If IsDate(BeginDate) And IsDate(EndDate) Then
If EndDate < BeginDate Then
MsgBox "The ending date must be later than the beginning
date." Exit Sub
End If
Else
MsgBox "Please use a valid date for the beginning date and the
ending date values."
End If

While a text string like "March 2005" will return True when used as the
argument for IsDate() it will not be evaluated as a date in your If EndDate
< BeginDate line. That will evaluate them as text strings and the "less
than" will be alphbetical, not chronological.

Try wrapping each in CDate()...

If IsDate(BeginDate) And IsDate(EndDate) Then
If CDate(EndDate) < CDate(BeginDate) Then
MsgBox "The ending date must be later than the beginning date."
Exit Sub
End If
Else
MsgBox "Please use a valid date for the beginning date and the ending
date values."
End If

 

[Guest]

Thanks for your response, When I tried to open the report based on those
months (from July to September) it opened the report for may june july
september and october...

This is how my report is based..
my table has the following: Rcvddate e.g., 10/10/2005

so my report is grouped as follows:
RcvdMonths:Format$([RcvdDate],"mmmm yyyy",0,0) e.g., October 2005

so what could have I done wrong

 

[Guest]

Thanks for your response, When I tried to open the report based on those
months (from July to September) it opened the report for may june july
september and october...

This is how my report is based..
my table has the following: Rcvddate e.g., 10/10/2005

so my report is grouped as follows:
RcvdMonths:Format$([RcvdDate],"mmmm yyyy",0,0) e.g., October 2005

so what could have I done wrong

 

[Rick Brandt]

Thanks for your response, When I tried to open the report based on
those months (from July to September) it opened the report for may
june july september and october...

This is how my report is based..
my table has the following: Rcvddate e.g., 10/10/2005

so my report is grouped as follows:
RcvdMonths:Format$([RcvdDate],"mmmm yyyy",0,0) e.g., October 2005

so what could have I done wrong

How are you passing the criteria on your form to the report?

 

[Guest]

through a query, the query's sql is as follows

SELECT Tasks, Format$([RcvdDate],"mmmm yyyy",0,0) AS RcvdMonths
FROM tblEmployee
GROUP BY Tasks, Format$([RcvdDate],"mmmm yyyy",0,0)
HAVING (((Tasks) Is Not Null) AND ((Format$([RcvdDate],"mmmm yyyy",0,0))
Between [Forms]![rptmonthlyYield]![BeginMonth] And
[Forms]![rptmonthlyYield]![EndMonth]))
ORDER BY Tasks;

Thanks for your response, When I tried to open the report based on
those months (from July to September) it opened the report for may
june july september and october...

This is how my report is based..
my table has the following: Rcvddate e.g., 10/10/2005

so my report is grouped as follows:
RcvdMonths:Format$([RcvdDate],"mmmm yyyy",0,0) e.g., October 2005

so what could have I done wrong

How are you passing the criteria on your form to the report?

 

[Rick Brandt]

through a query, the query's sql is as follows

SELECT Tasks, Format$([RcvdDate],"mmmm yyyy",0,0) AS RcvdMonths
FROM tblEmployee
GROUP BY Tasks, Format$([RcvdDate],"mmmm yyyy",0,0)
HAVING (((Tasks) Is Not Null) AND ((Format$([RcvdDate],"mmmm
yyyy",0,0)) Between [Forms]![rptmonthlyYield]![BeginMonth] And
[Forms]![rptmonthlyYield]![EndMonth]))
ORDER BY Tasks;

Okay, there you are wrapping your dates in the format function and the output of
that is no longer a date, it is a string. If you want chronological comparisons
you need to keep the date DataType.

 

[Allen Browne]

Yes, I thought you might be doing somehing like that.

The simplest workaround would be to let the user specify full dates, e.g.
July 1 2005 to October 31 2005. You could then use the code in this link:
http://allenbrowne.com/casu-08.html

If you don't want to do that, and you want to include October, you will need
to ask for less than November so all the October dates are included. The
most efficient approach would be to convert those text boxes into true
dates, so that Access can use any index on your date field and perform a
numeric date comparison. You will then need to format the date into the
correctly delimited format in the WhereCondition string:

Dim strWhere As String
Dim bCancel As Boolean
Const conJetDate = "\#mm\/dd\/yyyy\#"

If IsDate(BeginDate) And IsDate(EndDate) Then
If CDate(EndDate) < CDate(BeginDate) Then
MsgBox "The ending date must be later than the beginning date."
bCancel = True
End If
Else
MsgBox "Please use a valid date"
bCancel = True
End If
If Not bCancel Then
strWhere = "([MyDateField] >= " & _
Format(CDate(Me.BeginDate, conJetDate) & _
") AND ([MyDateField] < " & _
Format(CDate(Me.BeginDate, conJetDate) & ")"
'Debug.Print strWhere
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your response, When I tried to open the report based on those
months (from July to September) it opened the report for may june july
september and october...

This is how my report is based..
my table has the following: Rcvddate e.g., 10/10/2005

so my report is grouped as follows:
RcvdMonths:Format$([RcvdDate],"mmmm yyyy",0,0) e.g., October 2005

so what could have I done wrong

Try using CDate() to convert the entries to actual date values:

If IsDate(BeginDate) And IsDate(EndDate) Then
If CDate(EndDate) < CDate(BeginDate) Then
MsgBox "The ending date must be later than the beginning
date."
Exit Sub
End If
Else
MsgBox "Please use a valid date"
End If

 

[Guest]

Thanks for the advice....

through a query, the query's sql is as follows

SELECT Tasks, Format$([RcvdDate],"mmmm yyyy",0,0) AS RcvdMonths
FROM tblEmployee
GROUP BY Tasks, Format$([RcvdDate],"mmmm yyyy",0,0)
HAVING (((Tasks) Is Not Null) AND ((Format$([RcvdDate],"mmmm
yyyy",0,0)) Between [Forms]![rptmonthlyYield]![BeginMonth] And
[Forms]![rptmonthlyYield]![EndMonth]))
ORDER BY Tasks;

Okay, there you are wrapping your dates in the format function and the output of
that is no longer a date, it is a string. If you want chronological comparisons
you need to keep the date DataType.

 

[Guest]

Thanks for the advice!

Yes, I thought you might be doing somehing like that.

The simplest workaround would be to let the user specify full dates, e.g.
July 1 2005 to October 31 2005. You could then use the code in this link:
http://allenbrowne.com/casu-08.html

If you don't want to do that, and you want to include October, you will need
to ask for less than November so all the October dates are included. The
most efficient approach would be to convert those text boxes into true
dates, so that Access can use any index on your date field and perform a
numeric date comparison. You will then need to format the date into the
correctly delimited format in the WhereCondition string:

Dim strWhere As String
Dim bCancel As Boolean
Const conJetDate = "\#mm\/dd\/yyyy\#"

If IsDate(BeginDate) And IsDate(EndDate) Then
If CDate(EndDate) < CDate(BeginDate) Then
MsgBox "The ending date must be later than the beginning date."
bCancel = True
End If
Else
MsgBox "Please use a valid date"
bCancel = True
End If
If Not bCancel Then
strWhere = "([MyDateField] >= " & _
Format(CDate(Me.BeginDate, conJetDate) & _
") AND ([MyDateField] < " & _
Format(CDate(Me.BeginDate, conJetDate) & ")"
'Debug.Print strWhere
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your response, When I tried to open the report based on those
months (from July to September) it opened the report for may june july
september and october...

This is how my report is based..
my table has the following: Rcvddate e.g., 10/10/2005

so my report is grouped as follows:
RcvdMonths:Format$([RcvdDate],"mmmm yyyy",0,0) e.g., October 2005

so what could have I done wrong

Try using CDate() to convert the entries to actual date values:

If IsDate(BeginDate) And IsDate(EndDate) Then
If CDate(EndDate) < CDate(BeginDate) Then
MsgBox "The ending date must be later than the beginning
date."
Exit Sub
End If
Else
MsgBox "Please use a valid date"
End If

I would like to Check to see that ending month is later than beginning
month.
How do I do that. because what I have is Checking to see if the
ending
date is later than beginning date as follows: (where begindate and
enddate
are unbound textboxes on a form now my form has 2 unbound textboxes
with
beginMonth and EndMonth. Months are formated as "mmmm yyyy" )

If IsDate(BeginDate) And IsDate(EndDate) Then
If EndDate < BeginDate Then
MsgBox "The ending date must be later than the beginning
date."
Exit Sub
End If
Else
MsgBox "Please use a valid date for the beginning date and the
ending date values."
End If