473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Open the report conditionally

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.StartDate) And
DateValue(Me.EndDate) & "')"
strSelectEmp = "[EmpName] = '" & Me.cboEmployees & "' and
(datevalue([overtimedate]) >= '" & DateValue(Me.StartDate) & "' and
datevalue([overtimedate]) <= '" & DateValue(Me.EndDate) & "')"

DoCmd.OpenReport "rptOverTimeApproval", acViewPreview, ,
strSelectEmp
End Sub

Can anybody help me to solve the problem.

Thanks in advance

Naushad
Aug 20 '08 #1
3 2052
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.cboEmployees) Then
strWhere = strWhere & "(EmpName = """ & Me.cboEmployees & """) AND "
End If

If IsDate(Me.StartDate) Then
strWhere =strWhere & "(OverTimeDate >= " & _
Format(Me.StartDate, strcJetDate) & ") AND "
End If

If IsDate(Me.EndDate) Then
strWhere =strWhere & "(OverTimeDate < " & _
Format(Me.EndDate + 1, strcJetDate) & ") AND "
End If

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

Debug.Print strWhere
DoCmd.OpenReport "rptOverTimeApproval", 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**********************************@w7g2000h sa.googlegroups.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.StartDate) And
DateValue(Me.EndDate) & "')"
strSelectEmp = "[EmpName] = '" & Me.cboEmployees & "' and
(datevalue([overtimedate]) >= '" & DateValue(Me.StartDate) & "' and
datevalue([overtimedate]) <= '" & DateValue(Me.EndDate) & "')"

DoCmd.OpenReport "rptOverTimeApproval", acViewPreview, ,
strSelectEmp
End Sub

Can anybody help me to solve the problem.

Thanks in advance

Naushad
Aug 20 '08 #2
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...@SeeSig.Invalidwrote:
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.cboEmployees) Then
* * * * strWhere = strWhere & "(EmpName = """ & Me.cboEmployees & """) AND "
* * End If

* * If IsDate(Me.StartDate) Then
* * * * strWhere =strWhere & "(OverTimeDate >= " & _
* * * * * * Format(Me.StartDate, strcJetDate) & ") AND "
* * End If

* * If IsDate(Me.EndDate) Then
* * * * strWhere =strWhere & "(OverTimeDate < " & _
* * * * * * Format(Me.EndDate + 1, strcJetDate) & ") AND "
* * End If

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

* * Debug.Print strWhere
* * DoCmd.OpenReport "rptOverTimeApproval", 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**********************************@w7g2000h sa.googlegroups.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.StartDate) And
DateValue(Me.EndDate) & "')"
* *strSelectEmp = "[EmpName] = '" & Me.cboEmployees & "' and
(datevalue([overtimedate]) >= '" & DateValue(Me.StartDate) & "' and
datevalue([overtimedate]) <= '" & DateValue(Me.EndDate) & "')"
* DoCmd.OpenReport "rptOverTimeApproval", acViewPreview, ,
strSelectEmp
End Sub
Can anybody help me to solve the problem.
Thanks in advance
Naushad- Hide quoted text -

- Show quoted text -
Aug 25 '08 #3
"Naushad" <nh*****@kockw.comwrote in message
news:cd**********************************@x35g2000 hsb.googlegroups.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...@SeeSig.Invalidwrote:
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.cboEmployees) Then
strWhere = strWhere & "(EmpName = """ & Me.cboEmployees & """) AND "
End If

If IsDate(Me.StartDate) Then
strWhere =strWhere & "(OverTimeDate >= " & _
Format(Me.StartDate, strcJetDate) & ") AND "
End If

If IsDate(Me.EndDate) Then
strWhere =strWhere & "(OverTimeDate < " & _
Format(Me.EndDate + 1, strcJetDate) & ") AND "
End If

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

Debug.Print strWhere
DoCmd.OpenReport "rptOverTimeApproval", 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**********************************@w7g2000h sa.googlegroups.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.StartDate) And
DateValue(Me.EndDate) & "')"
strSelectEmp = "[EmpName] = '" & Me.cboEmployees & "' and
(datevalue([overtimedate]) >= '" & DateValue(Me.StartDate) & "' and
datevalue([overtimedate]) <= '" & DateValue(Me.EndDate) & "')"
DoCmd.OpenReport "rptOverTimeApproval", acViewPreview, ,
strSelectEmp
End Sub
Can anybody help me to solve the problem.
Thanks in advance
Naushad- Hide quoted text -

- Show quoted text -
Aug 25 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Miguel | last post by:
I have reviewed the many postings on this topic and understand the principle, but am not sure where in my case to apply trapping an error. I have created a form to supply parameters to a report. I...
12
by: Orchid | last post by:
Hello all, I have different version of reports which used for different months. For example, I am using report version 1 up to September, but we have some design changes on the report for October,...
0
by: rinmanb70 | last post by:
I open a report with a button on a form and I'd like to have the report open to the last page. I tried using sendkeys and it works when I manually open the report from Reports on the msaccess...
4
by: magmike | last post by:
I've created a report for the purpose of printing a one page summary of a record. Of course, when I created the report, it gives me a page on every record. Can I create a button to open that report...
5
by: Piedone | last post by:
Hi! I have experienced a a really odd problem when writing an Access application. I would programatically open a report (because i need to pass a value to it) with DoCmd.OpenReport. I would like...
7
by: martin DH | last post by:
Hello, I have a report that I open that pull its data from a form that builds a where string. Opening the report first opens the form, where I enter criteria, and then pulls matching records from a...
4
by: Phil Stanton | last post by:
I am opening a report (in Preview) from a menu system I use the following code if there is no data in a report Private Sub Report_NoData(Cancel As Integer) MsgBox "There are no errors in...
4
by: pacarv | last post by:
I've created a database in Access 2003 with multiple reports that are accessable from the switchboard. The switchboard also has a print button. The user selects the report from the menu which then...
5
by: tetsuo2030 | last post by:
Hey All, I’ve reached another aesthetic stumbling block: I have a report that lists all the widgets on Optimus Prime with the fields "Widget Number" and "ID Number" (and some other fields). ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.