What is wrong with my syntax

[JOM]

I have 2 unbound textboxes(txtbegindate) and (txtenddate) on my form. when i
enter dates in those 2 boxes, and press preview, I will need to open a report
based on those between dates... It was working ok, until I entered 12/31/05
and 01/02/2006 and all the records appeared in my report but there is no data
for those date... so what is wrong with my sntax below?



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

 

[ldiaz]

move Exit Sub to the end.


If IsDate(txtBeginDate) And IsDate(txtEndDate) Then
If IsDate(txtEndDate) < IsDate(txtBeginDate) Then
MsgBox "The ending date must be later than the beginning date."
txtEndDate.SetFocus

End If
Else
MsgBox "Please use a valid date for the beginning date and the
ending date"
End If

Exit Sub

 

[Rick Brandt]

I have 2 unbound textboxes(txtbegindate) and (txtenddate) on my form.
when i enter dates in those 2 boxes, and press preview, I will need
to open a report based on those between dates... It was working ok,
until I entered 12/31/05 and 01/02/2006 and all the records appeared
in my report but there is no data for those date... so what is wrong
with my sntax below?



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

You don't want to compare the result of IsDate() in the inner If-Then Block
Just the value of the dates themselves. I think you meant to use CDate() there
rather than IsDate().

 

[John Vinson]

I have 2 unbound textboxes(txtbegindate) and (txtenddate) on my form. when i
enter dates in those 2 boxes, and press preview, I will need to open a report
based on those between dates... It was working ok, until I entered 12/31/05
and 01/02/2006 and all the records appeared in my report but there is no data
for those date... so what is wrong with my sntax below?

Nothing; but that's not why you're getting the error. Could you post
the SQL view of the Query upon which the Report is based? That's
likely the problem.

John W. Vinson[MVP]

 

[JOM]

ok here is my sql statements from where the report is coming from!

SELECT tblBorrower.BorID, tblBorrower.BorLName,
Format([RqstDateRcvd],"mm/dd/yyyy",0,0) AS Expr1, tblBorrower.RcvdDateIRS,
tblTaxDoc.TaxDocID, tblTaxDoc.TaxDocName, tblTaxDoc.TaxDocType,
tblTaxDoc.TaxDocYear, tblTaxDoc.TaxDOcStatus, tblBorrower.RqstDateRcvd
FROM tblBorrower INNER JOIN tblTaxDoc ON (tblBorrower.BorID =
tblTaxDoc.BorID) AND (tblBorrower.BorID = tblTaxDoc.BorID)
WHERE (((Format([RqstDateRcvd],"mm/dd/yyyy",0,0)) Between
[forms]![frmSrchStatus]![txtbegindate] And
[forms]![frmSrchStatus]![txtenddate]));

 

[Douglas J Steele]

Try removing the Format statement from your WHERE clause: Format actually
converts whatever it's operating on to a string. If you're trying to remove
the time portion of the date, use the DateValue function or (better) check
Between [forms]![frmSrchStatus]![txtbegindate] And
[forms]![frmSrchStatus]![txtenddate] + 1

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

ok here is my sql statements from where the report is coming from!

SELECT tblBorrower.BorID, tblBorrower.BorLName,
Format([RqstDateRcvd],"mm/dd/yyyy",0,0) AS Expr1, tblBorrower.RcvdDateIRS,
tblTaxDoc.TaxDocID, tblTaxDoc.TaxDocName, tblTaxDoc.TaxDocType,
tblTaxDoc.TaxDocYear, tblTaxDoc.TaxDOcStatus, tblBorrower.RqstDateRcvd
FROM tblBorrower INNER JOIN tblTaxDoc ON (tblBorrower.BorID =
tblTaxDoc.BorID) AND (tblBorrower.BorID = tblTaxDoc.BorID)
WHERE (((Format([RqstDateRcvd],"mm/dd/yyyy",0,0)) Between
[forms]![frmSrchStatus]![txtbegindate] And
[forms]![frmSrchStatus]![txtenddate]));

Nothing; but that's not why you're getting the error. Could you post
the SQL view of the Query upon which the Report is based? That's
likely the problem.

John W. Vinson[MVP]

 

[JOM]

That worked, Thanks!

Try removing the Format statement from your WHERE clause: Format actually
converts whatever it's operating on to a string. If you're trying to remove
the time portion of the date, use the DateValue function or (better) check
Between [forms]![frmSrchStatus]![txtbegindate] And
[forms]![frmSrchStatus]![txtenddate] + 1

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

ok here is my sql statements from where the report is coming from!

SELECT tblBorrower.BorID, tblBorrower.BorLName,
Format([RqstDateRcvd],"mm/dd/yyyy",0,0) AS Expr1, tblBorrower.RcvdDateIRS,
tblTaxDoc.TaxDocID, tblTaxDoc.TaxDocName, tblTaxDoc.TaxDocType,
tblTaxDoc.TaxDocYear, tblTaxDoc.TaxDOcStatus, tblBorrower.RqstDateRcvd
FROM tblBorrower INNER JOIN tblTaxDoc ON (tblBorrower.BorID =
tblTaxDoc.BorID) AND (tblBorrower.BorID = tblTaxDoc.BorID)
WHERE (((Format([RqstDateRcvd],"mm/dd/yyyy",0,0)) Between
[forms]![frmSrchStatus]![txtbegindate] And
[forms]![frmSrchStatus]![txtenddate]));

On Tue, 17 Jan 2006 15:26:02 -0800, JOM

I have 2 unbound textboxes(txtbegindate) and (txtenddate) on my form. when i
enter dates in those 2 boxes, and press preview, I will need to open a report
based on those between dates... It was working ok, until I entered 12/31/05
and 01/02/2006 and all the records appeared in my report but there is no data
for those date... so what is wrong with my sntax below?

Nothing; but that's not why you're getting the error. Could you post
the SQL view of the Query upon which the Report is based? That's
likely the problem.

John W. Vinson[MVP]