Running a report to give results that lie between two dates

[leahb747]

Hi all,

I'm currently trying to fix a database that hasn't been working properly and have come unstuck trying to run a report between 2 dates.

Basically, each report shows a date (DayDate) and belongs to a certain Contract (Contract). I have developed a form with a combo box (cboContract) that offers all possible contract numbers and two boxes where the user inputs StartDate and EndDate.

What I'm trying to do is work out what code I need to ensure that when the user presses the button Run Report (Command19) the report (rptDayworkCheck) will run between the StartDate and End Date. The code I have so far (from miscellaneous sources is as follows)

Code:
Private Sub Command19_Click()
On Error GoTo Err_Command19_Click
Dim stDocName, Contract As String
Dim StartDate As Date
Dim EndDate As Date
Dim stLinkCriteria As String

StartDate = Forms!frmContracts1!StartDate
EndDate = Forms!frmContracts1!EndDate
Contract = Forms!frmContracts1!cboContract

stDocName = "rptDayworkCheck"
stLinkCriteria = "[DayDate] Between # " & Format(StartDate, "dd/mm/yyyy") & "# And #" & Format(EndDate, "dd/mm/yyyy") & "#"
DoCmd.OpenReport stDocName, acPreview, stLinkCriteria


Exit_Command19_Click:
Exit Sub
Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click

End Sub

Any help would be greatly (and desperately) appreciated!

 

[pbaldy]

I think you have the wherecondition in the wrong position. Try

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

 

[pbaldy]

Oh, and in VBA you should format the dates in US format; mm/dd/yyyy.

 

[leahb747]

Works like a charm. I can't believe I didn't try it earlier!

 

[pbaldy]

Happy to help!

 

[leahb747]

I've just had a look at it, and whilst the dates work, when I go to change the combo box value for the Contract name. I still get the results for all the contracts. I didn't notice it at first. I had a feeling the code for Contract was wrong... any ideas?

 

[leahb747]

Code currently looks like this

Private Sub Command19_Click()
On Error GoTo Err_Command19_Click
Dim stDocName As String
Dim stContract As String
Dim StartDate As Date
Dim EndDate As Date
Dim ResourceID As String
Dim stLinkCriteria As String

StartDate = Forms!frmContracts1!StartDate
EndDate = Forms!frmContracts1!EndDate

stDocName = "rptDayworkCheck"
stLinkCriteria = "[DayDate] Between #" & Format(StartDate, "mm/dd/yyyy") & "# And #" & Format(EndDate, "mm/dd/yyyy") & "#"
stContract = "Forms!frmContracts1!cboContract"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria


Exit_Command19_Click:
Exit Sub
Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click

 

[pbaldy]

Your wherecondition (stLinkCriteria) does not include anything for contract, just the dates. You'd have to add it to that.

 

[leahb747]

Your wherecondition (stLinkCriteria) does not include anything for contract, just the dates. You'd have to add it to that.

I've tried a couple of times to do that, how would I add it? Just with another & Contract = etc etc, or with a comma?

 

[pbaldy]

With "AND":

stLinkCriteria = "[DayDate] Between #" & Format(StartDate, "mm/dd/yyyy") & "# And #" & Format(EndDate, "mm/dd/yyyy") & "# And Contract = " & stContract

If Contract is a text field, you'd need single quotes around the value like you did with the # for the dates.

 

[leahb747]

Legend!!!!

 

[pbaldy]

Glad it worked for you!