Referencing Report Field

[Krispy89]

Hi First time user to any forum was wondering if there was any solution to my problem or if im tackling this in the wrong approach. I have a form with three fields cboServiceType with a value list, and two text boxes txtAfter and txtBefore. I want to on command click i want to open a Report Where the JobDate field in my Report is between the dates in my text boxes and = to my cboService type. my code i have so far is below i know it is wrong i was wondering if someone could help fix my error?

Cheers Krispy89

Code:

Private Sub cmdSelectType_Click()
Dim before As Date
Dim after As Date
before = Me.txtBefore
after = Me.txtAfter

Select Case True
Case (Me.cboServiceType = "S/V")
DoCmd.OpenReport "rptJobsBooked", acViewPreview, , "ServiceType = 'S/V' And before < JobDate < after"
Case (Me.cboServiceType = "S/C")
DoCmd.OpenReport "rptJobsBooked", acViewPreview, , "ServiceType = 'S/C' And before < JobDate < after"
Case (Me.cboServiceType = "I")
DoCmd.OpenReport "rptJobsBooked", acViewPreview, , "ServiceType = 'I' And before < JobDate < after"
Case (Me.cboServiceType = "Other")
DoCmd.OpenReport "rptJobsBooked", acViewPreview, , "ServiceType = 'Other' And before < JobDate < after"
End Select

End Sub

 

[JANR]

What kind of error?

But your Date qriteria looks odd, perhaps using Between instead.

DoCmd.OpenReport "rptJobsBooked", acViewPreview, , "ServiceType = 'S/V' And [COLOR=red]JobDate Between before And after[/COLOR]"

JR

 

[Krispy89]

it asks for the paramater value for before and after through the default input box

 

[JANR]

Mayby DCrake's metode here is a better solution: http://www.access-programmers.co.uk/forums/showthread.php?t=167718

JR

 

[Krispy89]

Thanks Will Try this solution and post back later cheer JR

 

[Krispy89]

Just Want to say thanks again it worked problem solved

 

[JANR]

Glad to help

JR