Open the report conditionally

[Naushad]

Hi All,

I am trying to open the report conditionally from the dialogue box
form. In the form there are three field.

cboEmployees
StartDate
EndDate

I have used these field in criteria to open the report. If I use the
field name and between command, the report is not opening but when I
use the greater than and less than equal to, the report is opening but
there is no records. The used code are as follows:

Private Sub cmdPrint_Click( )
Dim strSelectEmp As String

'strSelectEmp = "[EmpName] = '" & Me.cboEmployees & "' AND
(datevalue([OverTimeDate]) Between '" & DateValue(Me.St artDate) And
DateValue(Me.En dDate) & "')"
strSelectEmp = "[EmpName] = '" & Me.cboEmployees & "' and
(datevalue([overtimedate]) >= '" & DateValue(Me.St artDate) & "' and
datevalue([overtimedate]) <= '" & DateValue(Me.En dDate) & "')"

DoCmd.OpenRepor t "rptOverTimeApp roval", acViewPreview, ,
strSelectEmp
End Sub

Can anybody help me to solve the problem.

Thanks in advance

Naushad

[Allen Browne]

This example is designed to make it really easy to add more filter boxes if
you ever need to. It solves several problems:

1. It applies the filter only if there is something in the controls (not if
they are left null.)

2. It checks the dates are valid.

3. It formats the dates the way JET expects, and adds the # delimiter. (Do
NOT change the Const line to match your regional settings: leave it as is.)

4. It illustrates how to print the contents of the filter string into the
Immediate Window. After running it, press Ctrl+G to see what the filter
looked like.

5. If your OverTimeDate field has a time component as well as the date, it
still returns the last date (by asking for 'less than the next day.')

6. If the person's name contains an apostropy, it still works (by using
double-quotes.)

Private Sub cmdPrint_Click( )
Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.cboEm ployees) Then
strWhere = strWhere & "(EmpName = """ & Me.cboEmployees & """) AND "
End If

If IsDate(Me.Start Date) Then
strWhere =strWhere & "(OverTimeD ate >= " & _
Format(Me.Start Date, strcJetDate) & ") AND "
End If

If IsDate(Me.EndDa te) Then
strWhere =strWhere & "(OverTimeD ate < " & _
Format(Me.EndDa te + 1, strcJetDate) & ") AND "
End If

'Chop off the trailing " AND "
lngLen=Len(strW here) - 5
If lngLen 0 Then
strWhere = Left$(strWhere, lngLen)
End If

Debug.Print strWhere
DoCmd.OpenRepor t "rptOverTimeApp roval", acViewPreview, , strWhere
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Naushad" <nh*****@kockw. comwrote in message
news:13******** *************** ***********@w7g 2000hsa.googleg roups.com...

>
I am trying to open the report conditionally from the dialogue box
form. In the form there are three field.

cboEmployees
StartDate
EndDate

I have used these field in criteria to open the report. If I use the
field name and between command, the report is not opening but when I
use the greater than and less than equal to, the report is opening but
there is no records. The used code are as follows:

Private Sub cmdPrint_Click( )
Dim strSelectEmp As String

'strSelectEmp = "[EmpName] = '" & Me.cboEmployees & "' AND
(datevalue([OverTimeDate]) Between '" & DateValue(Me.St artDate) And
DateValue(Me.En dDate) & "')"
strSelectEmp = "[EmpName] = '" & Me.cboEmployees & "' and
(datevalue([overtimedate]) >= '" & DateValue(Me.St artDate) & "' and
datevalue([overtimedate]) <= '" & DateValue(Me.En dDate) & "')"

DoCmd.OpenRepor t "rptOverTimeApp roval", acViewPreview, ,
strSelectEmp
End Sub

Can anybody help me to solve the problem.

Thanks in advance

Naushad

[Naushad]

Thanks for your support. I have used your code to open the report. I
am phasing the same problem that means the report is opening but
there is no records. I don't know why there is no records in the
report. I have used the datevalue function also.

Please solve this problem.

Thanks and regards

On Aug 20, 2:54*pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

This example is designed to make it really easy to add more filter boxes if
you ever need to. It solves several problems:

1. It applies the filter only if there is something in the controls (not if
they are left null.)

2. It checks the dates are valid.

3. It formats the dates the way JET expects, and adds the # delimiter. (Do
NOT change the Const line to match your regional settings: leave it as is..)

4. It illustrates how to print the contents of the filter string into the
Immediate Window. After running it, press Ctrl+G to see what the filter
looked like.

5. If your OverTimeDate field has a time component as well as the date, it
still returns the last date (by asking for 'less than the next day.')

6. If the person's name contains an apostropy, it still works (by using
double-quotes.)

Private Sub cmdPrint_Click( )
* * Dim strWhere As String
* * Dim lngLen As Long
* * Const strcJetDate = "\#mm\/dd\/yyyy\#"

* * If Not IsNull(Me.cboEm ployees) Then
* * * * strWhere = strWhere & "(EmpName = """ & Me.cboEmployees & """) AND "
* * End If

* * If IsDate(Me.Start Date) Then
* * * * strWhere =strWhere & "(OverTimeD ate >= " & _
* * * * * * Format(Me.Start Date, strcJetDate) & ") AND "
* * End If

* * If IsDate(Me.EndDa te) Then
* * * * strWhere =strWhere & "(OverTimeD ate < " & _
* * * * * * Format(Me.EndDa te + 1, strcJetDate) & ") AND "
* * End If

* * 'Chop off the trailing " AND "
* * lngLen=Len(strW here) - 5
* * If lngLen 0 Then
* * * * strWhere = Left$(strWhere, lngLen)
* * End *If

* * Debug.Print strWhere
* * DoCmd.OpenRepor t "rptOverTimeApp roval", acViewPreview, , strWhere
End Sub

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Naushad" <nhai...@kockw. comwrote in message

news:13******** *************** ***********@w7g 2000hsa.googleg roups.com...

I am trying to open the report conditionally from the dialogue box
form. In the form there are three field.

cboEmployees
StartDate
EndDate

I have used these field in criteria to open the report. If I use the
field name and between command, the report is not opening but when I
use the greater than and less than equal to, the report is opening but
there is no records. The used *code are as follows:

Private Sub cmdPrint_Click( )
* *Dim strSelectEmp As String

* *'strSelectEmp = "[EmpName] = '" & Me.cboEmployees & "' *AND
(datevalue([OverTimeDate]) Between '" & DateValue(Me.St artDate) And
DateValue(Me.En dDate) & "')"
* *strSelectEmp = "[EmpName] = '" & Me.cboEmployees & "' and
(datevalue([overtimedate]) >= '" & DateValue(Me.St artDate) & "' and
datevalue([overtimedate]) <= '" & DateValue(Me.En dDate) & "')"

* DoCmd.OpenRepor t "rptOverTimeApp roval", acViewPreview, ,
strSelectEmp
End Sub

Can anybody help me to solve the problem.

Thanks in advance

Naushad- Hide quoted text -

- Show quoted text -

[Allen Browne]

"Naushad" <nh*****@kockw. comwrote in message
news:cd******** *************** ***********@x35 g2000hsb.google groups.com...

>
Please solve this problem.

We are not here to solve your problems. The best we can do is give you some
clues where to look, so you can solve them.

Thanks for your support. I have used your code to open the report.
I am phasing the same problem that means the report is opening
but there is no records. I don't know why there is no records in the
report. I have used the datevalue function also.

After you open the report and it has no records, open the Immediate Window
(Ctrl+G) and see what you got printed there. Try that as the criteria in a
query similar to the report's record source, and see why you get no results.

On Aug 20, 2:54 pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:

This example is designed to make it really easy to add more filter boxes
if
you ever need to. It solves several problems:

1. It applies the filter only if there is something in the controls (not
if
they are left null.)

2. It checks the dates are valid.

3. It formats the dates the way JET expects, and adds the # delimiter. (Do
NOT change the Const line to match your regional settings: leave it as
is.)

4. It illustrates how to print the contents of the filter string into the
Immediate Window. After running it, press Ctrl+G to see what the filter
looked like.

5. If your OverTimeDate field has a time component as well as the date, it
still returns the last date (by asking for 'less than the next day.')

6. If the person's name contains an apostropy, it still works (by using
double-quotes.)

Private Sub cmdPrint_Click( )
Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.cboEm ployees) Then
strWhere = strWhere & "(EmpName = """ & Me.cboEmployees & """) AND "
End If

If IsDate(Me.Start Date) Then
strWhere =strWhere & "(OverTimeD ate >= " & _
Format(Me.Start Date, strcJetDate) & ") AND "
End If

If IsDate(Me.EndDa te) Then
strWhere =strWhere & "(OverTimeD ate < " & _
Format(Me.EndDa te + 1, strcJetDate) & ") AND "
End If

'Chop off the trailing " AND "
lngLen=Len(strW here) - 5
If lngLen 0 Then
strWhere = Left$(strWhere, lngLen)
End If

Debug.Print strWhere
DoCmd.OpenRepor t "rptOverTimeApp roval", acViewPreview, , strWhere
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Naushad" <nhai...@kockw. comwrote in message

news:13******** *************** ***********@w7g 2000hsa.googleg roups.com...

I am trying to open the report conditionally from the dialogue box
form. In the form there are three field.

cboEmployees
StartDate
EndDate

I have used these field in criteria to open the report. If I use the
field name and between command, the report is not opening but when I
use the greater than and less than equal to, the report is opening but
there is no records. The used code are as follows:

Private Sub cmdPrint_Click( )
Dim strSelectEmp As String

'strSelectEmp = "[EmpName] = '" & Me.cboEmployees & "' AND
(datevalue([OverTimeDate]) Between '" & DateValue(Me.St artDate) And
DateValue(Me.En dDate) & "')"
strSelectEmp = "[EmpName] = '" & Me.cboEmployees & "' and
(datevalue([overtimedate]) >= '" & DateValue(Me.St artDate) & "' and
datevalue([overtimedate]) <= '" & DateValue(Me.En dDate) & "')"

DoCmd.OpenRepor t "rptOverTimeApp roval", acViewPreview, ,
strSelectEmp
End Sub

Can anybody help me to solve the problem.

Thanks in advance

Naushad- Hide quoted text -

- Show quoted text -