hi all
I have a sub form well I think that's the tech term right let get, down to business
how it works
I have 2 txt box's called txtstartdate and txtenddate I enter date range between these box's and hit the command button it show all records in that date range. the field the txt box's are filtering is "date Raised" then open's report call "input report" I hope everyone with me lol. if I leave txtstartdate and txtenddate empty "no dates" it will open my report showing all records yet again that great
I have now added checkbox called job cancelled 2 and this filter's field "job cancelled"
right here the code then I will explain more
Code:
Private Sub cmdPreview_Click()
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.
'DO set the values in the next 3 lines.
strReport = "Input Report" 'Put your report name in these quotes.
strDateField = "[Date raised]" 'Put your field name in the square brackets in these quotes.
lngView = acViewReport 'Use acViewNormal to print instead of preview.
'Build the filter string.
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If
If Me.[Job Cancelled 2] = True Then
strWhere = strWhere & "AND" & "[job cancelled] = -1"
End If
'Close the report if already open: otherwise it won't filter properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If
'Open the report.
'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub
r
right the code in red is my new code I have entered right now here's my problem
if I enter a date range in my txt box's and make job cancelled 2 " true"
it opening report showing job cancelled in that date range
but my problem is if I enter no date's in my txt box's and make job cancelled 2 true
I get syntax error
Here's a pic
so i would like to be able to not have to enter a date range unless the user wants to and if job cancelled 2 is true show all records job cancelled
hope you all understand my poor English lol
I have been bang my head against the wall for last few days on this now and it begin to hurt
as my vba skill are very little
please I'm begging for help I want to get this sorted first one to solve I owe them a beer!!! BIG TIME!!
cheers
Shane