Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query based on form (Access 2K)

    I have a list box (List5) that has a row source of qryOrderSch. This query has several field among them are Order_ID, Order_Date, Cust_Name, and a few others. I have a form which has a listbox on it which displays the columns in the query. Beneath the List box, I have four unbound controls: txtSchOrderID, txtSchOrderFrom, txtSchOrderTo, and cboSchCustName. In addition to these unbound controls, I have a command button titled Search.

    In qryOrderSch, I have the criteria for Order_ID as [Forms]![FrmMain]![txtSchOrderID], for Order_Date as Between [Forms]![frmMain]![txtSchOrderFrom] And [Forms]![frmmain]![txtSchOrderTo] and for Cust_Name as [Forms]![frmMain]![cboSchCustname]

    The VBA code for the Search command button is List5.requery.

    The problem is that utilizing this method, I have to specify criteria for each field or I do not get any results. I would like to figure out how to change the criteria so that if the control on the form is blank (null) the criteria in the query is non-existent.

    I tried:

    Like IIF("IsNull[Forms]![frmMain]![txtSchOrderID]", "*","[Forms]![frmMain]![txtSchOrderID]") but this will not include those fields that are NULL, only fields that contain data.

    Any help would be appreciated.

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query based on form (Access 2K)

    Switch the role of field and parameter and test whether the parameter equals the field or is null. The date parameter needs a slightly modified approach. The WHERE part of the query becomes:

    WHERE ([Forms]![FrmMain]![txtSchOrderID] Is Null Or [Forms]![FrmMain]![txtSchOrderID]=[Order_ID]) And ([Forms]![frmMain]![txtSchOrderFrom] Is Null Or [Forms]![frmMain]![txtSchOrderFrom]<=[Order_Date]) And ([Forms]![frmmain]![txtSchOrderTo] Is Null Or [Forms]![frmmain]![txtSchOrderTo]>=[Order_Date]) And ([Forms]![frmMain]![cboSchCustname] Is Null Or [Forms]![frmMain]![cboSchCustname]=[Cust_Name])

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query based on form (Access 2K)

    As usual, your advice worked like a charm.

    Thanks again.

  4. #4
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query based on form (Access 2K)

    A quick follow up...

    I added the other fields from the list box to the SQL statement following the format you provided. I am also setting up a couple of quick searches (queries for the user) and wonder if there is a way to modify the SQL you provided to search for all records where the DateRec is Null without messing up the search function I created.

    Original SQL

    SELECT qryOpenOrders.Order_ID, qryOpenOrders.Order_Date, qryOpenOrders.Cust_Name, qryOpenOrders.Cust_DistCenter, qryOpenOrders.Cust_Store, qryOpenOrders.Cust_PO, qryOpenOrders.Date2HS, qryOpenOrders.ShipDate, qryOpenOrders.DateRec
    FROM qryOpenOrders
    WHERE ((([Forms]![FrmMain]![txtSchOrderID]) Is Null Or ([Forms]![FrmMain]![txtSchOrderID])=[Order_ID]) AND (([Forms]![frmMain]![txtSchOrderFrom]) Is Null Or ([Forms]![frmMain]![txtSchOrderFrom])<=[Order_Date]) AND (([Forms]![frmMain]![txtSchOrderTo]) Is Null Or ([Forms]![frmMain]![txtSchOrderTo])>=[Order_Date]) AND (([Forms]![frmMain]![cboSchCustname]) Is Null Or ([Forms]![frmMain]![cboSchCustname])=[Cust_Name]) AND (([Forms]![frmMain]![cboSchDistCent]) Is Null Or ([Forms]![frmMain]![cboSchDistCent])=[Cust_DistCenter]) AND (([Forms]![frmMain]![cboSchStoreName]) Is Null Or ([Forms]![frmMain]![cboSchStoreName])=[Cust_Store]) AND (([Forms]![frmMain]![txtSchPurchOrd]) Is Null Or ([Forms]![frmMain]![txtSchPurchOrd])=[Cust_PO]) AND (([Forms]![frmMain]![txtSchDate2HSFrom]) Is Null Or ([Forms]![frmMain]![txtSchDate2HSFrom])<=[Date2HS]) AND (([Forms]![frmMain]![txtSchDate2HSTo]) Is Null Or ([Forms]![frmMain]![txtSchDate2HSTo])>=[Date2HS]) AND (([Forms]![frmMain]![txtSchShipDateFrom]) Is Null Or ([Forms]![frmMain]![txtSchShipDateFrom])<=[ShipDate]) AND (([Forms]![frmMain]![txtSchShipDateTo]) Is Null Or ([Forms]![frmMain]![txtSchShipDateTo])>=[ShipDate]) AND (([Forms]![frmMain]![txtSchDateRecFrom]) Is Null Or ([Forms]![frmMain]![txtSchDateRecFrom])<=[DateRec]) AND (([Forms]![frmMain]![txtSchDateRecTo]) Is Null Or ([Forms]![frmMain]![txtSchDateRecTo])>=[DateRec]))
    ORDER BY qryOpenOrders.Order_ID;


    VBA code for the command button labeled Today's Orders (note that Date +1 used as Orders are tracked using Now())

    Private Sub Command69_Click()
    On Error GoTo Err_Command69_Click

    txtSchOrderFrom = Date
    txtSchOrderTo = Date + 1
    txtSchOrderID = Null
    cboSchCustName = Null
    cboSchDistCent = Null
    cboSchStoreName = Null
    txtSchPurchOrd = Null
    txtSchDate2HSFrom = Null
    txtSchDate2HSTo = Null
    txtSchShipDateFrom = Null
    txtSchShipDateTo = Null
    txtSchDateRecFrom = Null
    txtSchDateRecTo = Null
    List5.Requery

    Exit_Command69_Click:
    Exit Sub

    Err_Command69_Click:
    MsgBox Err.Description
    Resume Exit_Command69_Click
    End Sub

    This clears all the fields except for order date where it inserts today's date as txtSchOrderFrom and tomorrow in the txtSchOrderTo field and requeries List5.

    Is there an easy way to do this or do I need to look into switching the rowsource of List5 to a different query with this information. I was hoping to work the List box from the one query.

    Thanks in advance for your assistance.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query based on form (Access 2K)

    I hope the following will work: add an unbound hidden text box named txtDateRecNull to the form. In the On Click event procedure for Command69, set its value to something (it doesn't matter what, as long as it isn't empty), and set it to Null in the On Click event procedure for the other command buttons. Add the following to the WHERE part of the query:

    ... AND ([Forms]![frmMain]![txtDateRecNull] Is Null Or [DateRec] Is Null)

  6. #6
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query based on form (Access 2K)

    Thanks for the help. I actually incorporated the unbound hidden text box you suggested as a visible check box with the label Open Orders only. I incorporated this into the regular search criteria, and gave it a value in the on click events of the command buttons used for queries with open orders.

    Thanks again for the assistance.

    On a side note, is there any way to repay the moderators and people like yourself who give so much to those of us in need? Anything we can do to help the cause?

Posting Permissions

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