Option group problems

[ridercoach]

Since you guys saved my butt on excel. I hoping you can all do the same on access. Here is my problem.

I am pulling from a job table that consists of jobnumbers, what office is doing the job, the engineer on the job and other bits of info.
I have a form set up where I have two option boxes, one consists of the year of the job (1990,1991,...2004 etc..) in option button format, we sort this by the first two letters of the job being the last two digits of the year. Thats easy to write in VBA. Then we pick what kind of order we want the print out in, lets say by eng, job number, etc.... It is all working fine. Now I added a new option which is called office. I want to print by year, by what type of sorted report, and by which office is doing the job. When I build the event in VBA to change the way the event is run from the button, it works, but not the way I want it to. It no longer identifies the year. Its as if it completley skips the year option. It does give me which jobs are at what office, but it gives me them for every year no matter what year I select. See the code below for ref.

If you all can help me out I would greatly appreciate it.

Thank you and happy holidays.


-------------------------------------------------------------------------------------
Private Sub cmdCreateList_Click()
On Error GoTo Err_cmdCreateList_Click

Dim stDocName As String
Dim stlinkcriteria As String
Dim stfilter As String


Select Case FrameYear
Case 1
stlinkcriteria = "([JobNumber]>=94000) and ([JobNumber]<94999)"
Case 2
stlinkcriteria = "([JobNumber]>=95000) and ([JobNumber]<95999)"
Case 3
stlinkcriteria = "([JobNumber]>=96000) and ([JobNumber]<96999)"
Case 4
stlinkcriteria = "([JobNumber]>=97000) and ([JobNumber]<97999)"
Case 5
stlinkcriteria = "([JobNumber]>=98000) and ([JobNumber]<98999)"
Case 6
stlinkcriteria = "([JobNumber]>=99000) and ([JobNumber]<99999)"
Case 7
stlinkcriteria = "([JobNumber]>=0) and ([JobNumber]<1000)"
Case 8
stlinkcriteria = "([jobnumber]>=89000) and ([jobnumber]<89999)"
Case 9
stlinkcriteria = "([jobnumber]>=90000) and ([jobnumber]<90999)"
Case 10
stlinkcriteria = "([jobnumber]>=91000) and ([jobnumber]<91999)"
Case 11
stlinkcriteria = "([jobnumber]>=92000) and ([jobnumber]<92999)"
Case 12
stlinkcriteria = "([jobnumber]>=93000) and ([jobnumber]<93999)"
Case 13
stlinkcriteria = "([jobnumber]>=1000) and ([jobnumber]<2000)"
Case 14
stlinkcriteria = "([jobnumber]>=2000) and ([jobnumber]<3000)"
Case 15
stlinkcriteria = "([jobnumber]>=3000) and ([jobnumber]<4000)"
Case 16
stlinkcriteria = "([jobnumber]>=4000) and ([jobnumber]<5000)"
Case 17
stlinkcriteria = "([jobnumber]>=5000) and ([jobnumber]<6000)"
Case 18
stlinkcriteria = "([jobnumber]>=6000) and ([jobnumber]<7000)"
Case 19
stlinkcriteria = "([jobnumber]>=7000) and ([jobnumber]<8000)"
Case 20
stlinkcriteria = "([jobnumber]>=8000) and ([jobnumber]<9000)"
Case 21
stlinkcriteria = "([jobnumber]>=9000) and ([jobnumber]<10000)"
Case 22
stlinkcriteria = "([jobnumber]>=10000) and ([jobnumber]<11000)"
Case 23
stlinkcriteria = "([jobnumber]>=11000) and ([jobnumber]<12000)"
Case 24
stlinkcriteria = "([jobnumber]>=12000) and ([jobnumber]<13000)"
Case 25
stlinkcriteria = "([jobnumber]>=13000) and ([jobnumber]<14000)"
Case 26
stlinkcriteria = "([jobnumber]>=14000) and ([jobnumber]<15000)"
Case 27
stlinkcriteria = "([jobnumber]>=15000) and ([jobnumber]<16000)"
Case 28
stlinkcriteria = "([jobnumber]>=16000) and ([jobnumber]<17000)"
Case 29
stlinkcriteria = "([jobnumber]>=17000) and ([jobnumber]<18000)"
Case 30
stlinkcriteria = "([jobnumber]>=18000) and ([jobnumber]<19000)"
Case 31
stlinkcriteria = "([jobnumber]>=19000) and ([jobnumber]<20000)"
Case 32
stlinkcriteria = "([jobnumber]>=20000) and ([jobnumber]<21000)"
End Select

Select Case Frame109
Case 1
stlinkcriteria = "([boca]=true) and ([longwood]=false)"
Case 2
stlinkcriteria = "([boca]=no) and ([longwood]=yes)"
Case 3
stlinkcriteria = "([boca]=yes) and ([longwood]=yes)"
End Select

Select Case FrameOrder
Case 1
stDocName = "rptJobs"
Case 2
stDocName = "rptJobs-Alpha"
Case 3
stDocName = "rptJobs-Client"
Case 4
stDocName = "rptJobs-Engineer"
End Select


DoCmd.OpenReport stDocName, acViewPreview, ,stlinkcriteria

Exit_cmdCreateList_Click:
Exit Sub

Err_cmdCreateList_Click:
MsgBox Err.Description
Resume Exit_cmdCreateList_Click

End Sub
------------------------------------------------------------------------------------

 

[Corticus]

It looks like your "overwriting" the value stlinkcriteria gets from the first select...case statement.

You start off ok, your strlinkcriteria gets a value based on 'FrameYear', but then in this select...case:
Select Case Frame109

Case 1 
stlinkcriteria = "([boca]=true) and ([longwood]=false)" 
Case 2 
stlinkcriteria = "([boca]=no) and ([longwood]=yes)" 
Case 3 
stlinkcriteria = "([boca]=yes) and ([longwood]=yes)" 
End Select

You reassign the variable another value.

You might try this revision:

Select Case Frame109 
Case 1 
stlinkcriteria = stlinkcriteria & " and ([boca]=true) and ([longwood]=false)" 
Case 2 
stlinkcriteria = stlinkcriteria & " and ([boca]=no) and ([longwood]=yes)" 
Case 3 
stlinkcriteria = stlinkcriteria & " and ([boca]=yes) and ([longwood]=yes)" 
End Select

 

[ridercoach]

Thank you for the quick response. I tried it and it seemed to work until I realized it was showing both boca and longwood on the print preview instead of one or the other. Wierd...But when you chose the both option which is case 3 it shows the the jobs with both offices being checked in the checked boxes. I dont know if it makes a difference, but on the form I used check boxes for the offices. One check in the longwood column should only print longwood jobs, one check in the boca column should print boca jobs, if both offices where checked then it should print both.

Thank you again.