The Calendar, the query the thief and his Lover?

[Stafford]

I've got a form with a Calendar some unbound date boxes and some Command buttons that are to run reports.

Now 2 of the Reports are based on queries that require date parameters and a site parameter (BranchNumber)

The Problem is when I run this through the form I keep getting #error where data should be when the report runs. If I run the report by itself the data returns fine, the same if I run the query on it's own.

The other report simply relies on the date range, so I put some code to basically ignore the Branchnumber. That report returns fine everytime.

Here's the Code

Option Compare Database
Option Explicit

Private Sub Form_Load()

Me!BranchNumber.SetFocus

End Sub

Private Sub Calendar_Click()

On Error GoTo Calendar_Click_Error

If IsNull([StartDate]) Then
[StartDate].Value = [Calendar].Value
Else
[EndDate].Value = [Calendar].Value
End If


Calendar_Click_Exit:
Exit Sub

Calendar_Click_Error:
MsgBox Err.Description
Resume Calendar_Click_Exit

End Sub

Private Sub cmdCompanyReport_Click()
On Error GoTo Err_cmdCompanyReport_Click

Dim stDocName As String

Me![BranchNumber].Value = Me![BranchNumber].OldValue
stDocName = "Monthly Company Report"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdCompanyReport_Click:
Exit Sub

Err_cmdCompanyReport_Click:
MsgBox Err.Description
Resume Exit_cmdCompanyReport_Click

End Sub
Private Sub cmdTicketExchange_Click()
On Error GoTo Err_cmdTicketExchange_Click

Dim stDocName As String

stDocName = "Branch Ticket Exchange Errors"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdTicketExchange_Click:
Exit Sub

Err_cmdTicketExchange_Click:
MsgBox Err.Description
Resume Exit_cmdTicketExchange_Click

End Sub
Private Sub cmdMonthlyBranchDefects_Click()
On Error GoTo Err_cmdMonthlyBranchDefects_Click

Dim stDocName As String

stDocName = "Monthly Branch Defect Report"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdMonthlyBranchDefects_Click:
Exit Sub

Err_cmdMonthlyBranchDefects_Click:
MsgBox Err.Description
Resume Exit_cmdMonthlyBranchDefects_Click

End Sub

I've checked and rechecked, what am I missing?

Please help :|

 

[Fizzio]

Your code seems fine to me. How are you retreiving the date parameter in the report, is it via the query and if so, what is your criteria expression?

 

[Stafford]

Nevermind, I found that my Query had criteria that specified a unique sort!
I have a column called "Sort" which I use to sort the Month in sequence, I had put an "8" in the Criteria to show only August data, and thereby causing the conflict.

Tired eyes!

Thanks for the reply

 

[AppStaff]

If you run this on the form, I'd think you'd need to reference the form in your syntax.. ie isNull(Forms!FormName!StartDate)

 

[Stafford]

If you run this on the form, I'd think you'd need to reference the form in your syntax.. ie isNull(Forms!FormName!StartDate)

[Forms!FormName!StartDate] Are the parameters in my query. I'm not sure I know what you are getting at.

 

[AppStaff]

[Forms!FormName!StartDate] Are the parameters in my query. I'm not sure I know what you are getting at.

"If IsNull([StartDate]) Then
[StartDate].Value = [Calendar].Value
Else
[EndDate].Value = [Calendar].Value
End If "

I was referring to the syntax in this code. Glancing at it, I would think you would need specific object reference for start date and calendar instead of just the control name. Just a quick observation and may not help you at all.