Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jun 2004
    Location
    Whistler, Br. Columbia, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering records using option group

    I have a table of work orders. I have designed a query that filters it via a number of combo boxes on a form, but I have run into a snag trying to implement one more filter. I'd like to have an option group with three selections (Finished, Unfinished, All) so that the command button will show the corresponding set of records. How do I add these criteria to the design view (can it be done here? I have never written SQL code from scratch)?

    Here's the SQL from SQL view of the query thus far:
    SELECT tblWorkOrder.ID, tblWorkOrder.LocationMain, tblWorkOrder.ReportedDate, tblWorkOrder.ReportedBy, tblWorkOrder.LocationSpecific, tblWorkOrder.Description, tblWorkOrder.Notes, tblWorkOrder.Priority, tblWorkOrder.AssignedTo, tblWorkOrder.CompletedDate, tblWorkOrder.CompletedBy
    FROM tblWorkOrder
    WHERE (((tblWorkOrder.LocationMain) Like [Forms]![frmMainMenu]![cmbRoom]) AND ((tblWorkOrder.ReportedDate)>=[Forms]![frmMainMenu]![txtReportedDate]) AND ((tblWorkOrder.Priority)>=[Forms]![frmMainMenu]![cmbPriority])) OR (((tblWorkOrder.ReportedDate)>=[Forms]![frmMainMenu]![txtReportedDate]) AND ((tblWorkOrder.Priority)>=[Forms]![frmMainMenu]![cmbPriority]) AND ((([tblWorkOrder].[LocationMain]) Like [Forms]![frmMainMenu]![cmbRoom]) Is Null));

    Thanks

  2. #2
    New Lounger
    Join Date
    Jun 2012
    Posts
    11
    Thanks
    1
    Thanked 1 Time in 1 Post

    Soft-Coding a SQL Statement from Forms Data

    There are a number of ways to do this, but they come down to the same thing: your sql string will be modified based on the value of the option group. You don't say which field you will test the result against, so I will call it WorkStatus for this reply. Several steps are in order.

    1) Turn the above string into a template by inserting the string " {0}" into the last line immediately before the semi-colon that ends it. No quotes, of course.

    2)
    After doing that, execute this code:

    if nz(optGroup) = 0 then
    end sub
    end if

    dim strworkstatus as string

    select case optGroup
    case 1
    strworkstatus = "Finished"
    case 2
    strworkstatus = "Unfinished"
    case 3
    strworkstatus = "All"
    end select

    dim strfind as string
    strfind = "AND (tblWorkOrder.WorkStatus = """ & strworkstatus & """) "
    sql = replace(sql, "{0}", strfind)

    Now you have SQL code you can requery as a filter

  3. The Following User Says Thank You to LasVegan For This Useful Post:

    Lastcall (2012-06-12)

  4. #3
    Lounger
    Join Date
    Jun 2004
    Location
    Whistler, Br. Columbia, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for this.

    The field I am testing is tblWorkOrder.CompletedDate. If there is a value (date value, obv) in here, the WO should be finished. If it is blank, the WO is unfinished. It seems that your code uses the string value "Finished" or "Unfinished" to test against a field that might contain that string.

    Also, forgive my ignorance, but step 2 says to 'execute this code'... does this mean that the code should be the result of clicking the button? this part has me a little confused.

Posting Permissions

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