Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combine (2) Case Selects (Access 97)

    What I am attempting to do is allow the user to choose they type of items from Me.Criteria (Case 1 = "Active", Case 2 = "Deleted", Case 3 = "All") as well as how they want to sort the data from Me.GrpSortBy. As it is written right now, the Me.GrpSortBy works just fine. However, no matter which Criteria is choosen, it always shows all data. Therefore, I need to know how to combine both cases for the report.

    Private Sub command3_Click()
    Dim PlantSort As String
    Dim strWhere As String
    Dim strWhere1 As String

    DoCmd.RunMacro "PlantCombo"
    gstrTitle = "Composition / Status"
    Select Case Me.Criteria
    Case 1
    strWhere1 = "[Deleted] = False"
    Case 2
    strWhere1 = "[Deleted] = True"
    Case 3
    strWhere1 = "[Deleted] <=0" ' nothing needed to get all records"
    End Select

    Select Case Me.grpSortBy
    Case 1
    strWhere = "[SortBy] = Y1Sum"
    PlantSort = "[Plant] = 'Altima Trim & Chassis'"
    DoCmd.OpenReport "General Info", acViewPreview, , PlantSort
    Case 2
    strWhere = "[SortBy] = Project Classification"
    PlantSort = "[Plant] = 'Altima Trim & Chassis'"
    DoCmd.OpenReport "General Info", acViewPreview, , PlantSort
    Case 3
    PlantSort = "[Plant]='Altima Trim & Chassis'"
    DoCmd.OpenReport "General Info By Code", acViewPreview, , PlantSort
    End Select
    End Sub

    The reason I wrote the Me.GrpSortBy like I did (including the running report, ect. at that time) was because if Case 1 or Case 2 is choosen, (1) report is ran and it is just sorted differently. However it Case 3 is choosen a different report runs.

    Any ideas on how to get this to work?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Combine (2) Case Selects (Access 97)

    I would suggest that you declare another variable StrReportName to hold the name of the report, and set its value in the second Select statement, then move the docmd.openReport line outside the second select statement.

    Also declare another variable to hold the final where condition.

    Before you open the report however, build strWhereFinal out of the the selections made in the two select statements.

    Something like this: strWhereFinal = strwhere & " and " & strwhere1 & " and " strPlantsort

    DoCmd.OpenReport strReportname, acViewPreview, , strWhereFinal
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine (2) Case Selects (Access 97)

    Here is what I have done.
    If Me.grpSortBy Case 3 is choosen with any Case from Me.Criteria, the report runs perfectly. However if Case 1 or 2 from Me.grpSortBy is choosen, the syntax error (missing operator) in query expression '([Deleted] = False and [SortBy] = Project Classification and [Plant] = 'Altima Trim & Chassis')'


    Private Sub command3_Click()
    Dim intCount As Integer
    Dim strWhere As String
    Dim strWhereS As String
    Dim strWhereT As String
    Dim PlantSort As String




    Dim isgm As String


    intCount = 1
    engall.Visible = False
    engalll.Visible = False
    bfs.Visible = False
    bfsl.Visible = False
    pfs.Visible = False
    pfsl.Visible = False
    tc.Visible = False
    tcl.Visible = False
    powertrain.Visible = False
    powertrainl.Visible = False
    EngineeringL.Visible = False




    gstrTitle = "Composition / Status"
    gstrTitleSt = Choose(Criteria, "Active Items", "Deleted Items", "All Items")
    DoCmd.RunMacro "PlantCombo"


    Do While intCount <= 2




    'DoCmd.Close
    Select Case Me.Criteria
    Case 1
    strWhere = "[Deleted] = False"
    Case 2
    strWhere = "[Deleted] = True"
    Case 3
    strWhere = "[Deleted] <=0" ' nothing needed to get all records"
    End Select

    Select Case Me.grpSortBy
    Case 1
    strWhereS = "[SortBy] = Y1Sum"

    Case 2
    strWhereS = "[SortBy] = Project Classification"

    Case 3

    End Select

    strWhereT = strWhere & " and " & strWhereS


    If Me.grpSortBy = 1 Or Me.grpSortBy = 2 Then
    strWhereT = strWhere & " and " & strWhereS & " and [Plant] = 'Altima Trim & Chassis'"
    DoCmd.OpenReport "General Info", acViewPreview, , strWhereT
    Else
    strWhere = strWhere & " and [Plant] = 'Altima Trim & Chassis'"
    DoCmd.OpenReport "General Info By Code", acViewPreview, , strWhere
    End If

    [Criteria] = 1

    Exit Sub

    Loop
    End Sub

    I have looked on MS Knowledge Base, my Access 97 Bible and other "help" sites but have found nothing on combining Select Cases'.
    Please, any other ideas or possibly an example would be greatly appriciated.

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine (2) Case Selects (Access 97)

    I'm a little confused, but I believe that your problem is that you need single quotes around the string values for the sortby...

    strWhereS = "[SortBy] = 'Y1Sum'"
    strWhereS = "[SortBy] = 'Project Classification'"

    HTH

  5. #5
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine (2) Case Selects (Access 97)

    Trudi,
    I believe you have got me on the right track. The Report did pull up, but had "errors" in every field. Now, I am truely lost, because the simple ' ' at the [SortBy] caused the report to accept the syntax but read it incorrectly......

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine (2) Case Selects (Access 97)

    Okay... Let's try get this working...

    To relieve my confusion, I need you to confirm a bit of information before I start writing this code... Me.Criteria and Me.grpSortBy... Criteria and grpSortBy are the names of controls on the form?? or?? ... Could you possibly give me a few sample records from the data so that I can see what the field names are and the kind of data?

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine (2) Case Selects (Access 97)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I keep looking at the code you have there and it's really making no sense to me... Why are you doing a Do While Loop through it?... and you're not incrementing intCount anywhere that I can see... Aren't you going into an endless loop there?? ...

    Could you post a small sample of the database?... I think maybe we should start from scratch... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> ...This should not be so difficult...
    What do you want to do... in every day terms... ? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine (2) Case Selects (Access 97)

    It would probably be best if I break it down via the following:
    (2) Option Groups
    Group1: Me.Criteria
    (3) Options reading from one field [Deleted]. Option 1 = Deleted = False.... Option 2 = Deleted = True..... Option 3 = Deleted = True or False (to read all records)
    Group2: Me.SortBy (This is how to tell the report how to Sort the data)
    First, in the query the following expression was created:
    SortBy: Choose([Forms]![InternalReports]![grpSortBy],[Project Classification])
    ***Side Note**** I have tried changing the query expression to:
    Choose([Forms]![InternalReports]![grpSortBy],[Project Classification] or [Y1Sum]) then tried:
    Choose([Forms]![InternalReports]![grpSortBy],'Project Classification') then tried:
    Choose([Forms]![InternalReports]![grpSortBy],'Project Classification' or 'Y1Sum')
    ****End Note
    Then, in the report, [SortBy] is used in the sorting and grouping
    (3) Options on form for Me.grpSortBy. Option 1 = [SortBy] = Project Classification ..... Option 2 = [SortBy] = Y1Sum
    *****Note**** Both Project Classification and Y1Sum are fields in the query. Also, Option 1 or Option 2 is to run report "General Info" **** End Note*****
    Option 3 = No Sorting, but instead it runs a different report named "General Info By Code"

    Also, PlantSort = "[Plant] = 'Altima Trim & Chassis'" . In this line I am wanting to tell the report to only pull the records where the field [Plant] = Altima Trim & Chassis.

    Well there is the entire section concerning the Select Cases...... As for giving some actual data,
    Fields: [Deleted] is a (Yes/No) check box
    The rest I believe are explained above.

  9. #9
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine (2) Case Selects (Access 97)

    I created a similar form for the same database and had a select case that involved Me.Criteria. So, I simply copied the code to this button. Therefore, there maybe some "needless" information, and I am more than open to suggestions......
    As for starting over, I hope you mean only this form............ This database is almost complete and has taken months to develop, therefore, starting the entire thing over is not a possibility.
    Post a small sample? I can not, "Confidential Data", and it would take some time to delete the tables and create new tables with dummie data.
    I would like to save that as a last resort.

  10. #10
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine (2) Case Selects (Access 97)

    Well that solves the first mystery... You're getting errors because I told you to put single quotes around "Project Classification" and "Y1Sum"... If these are field names then you need to put square brackets around them... The report's recordsource is looking for those two Values in a field called SortBy...

    Let me look through this and see if I can help... Can I have the names of the recordsources for both reports?

  11. #11
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine (2) Case Selects (Access 97)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> Noooo, I didn't mean start the whole form or database over!
    I meant this particular procedure... The command button code... That's all...

    Okay... but I'll probably have more questions... Hang in there... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  12. #12
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine (2) Case Selects (Access 97)

    General Info RecordSource is General Info
    General Info By Code RecordSource is General Info By Code

    I know I have gone astray from using rpt and qry.


    All Fields in one query are in the other query.

  13. #13
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine (2) Case Selects (Access 97)

    I deleted all the reports except the (2) Generals, all the forms except the 1, all the queries except the (2) generals and all the "user tables", and other unneeded tables for the database and it only comes down to 21.1MB in size. Then even tried to delete all but only 1 table and still the sam result. Zipping it only brings it down to 5.71MB. Therefore, I do not think a sample is possible.

  14. #14
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine (2) Case Selects (Access 97)

    Did you compact the database after deleting everything that wasn't needed? ...Delete most of the records in the tables involved??

  15. #15
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine (2) Case Selects (Access 97)

    Right click on the file and "compact"?
    If so, I have tried that before, but when I attach it anywhere, it wants to go as the full size. I "compacted" that way once, when a test FE was going out, but it was too large for our e-mail and we can send up to 9MB internally.

    Is there a trick I am missing?

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •