Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Date Query Problem

Discussion in 'Information Technology' started by luis.c.torres@gmail.com, Jul 28, 2006.

  1. luis.c.torres@gmail.com

    luis.c.torres@gmail.com
    Expand Collapse
    Guest

    Hello.
    I have and unbound form with a subform.
    The form has 4 controls (a combo, two txt boxes and a button).

    The combo has the filter criteria (Equal to, newer than, older than and
    between) and the txt boxes are for the data I want to search for.
    The textboxes are locked and I have two little buttons that open a
    calendar form from where I get the chosen dates.

    When I click the (filter) button on the form I can't seem to filter the
    records on the subform correctly.

    Private Sub FilterBtn_Click()
    Dim frm As Form
    Select Case TxtDate1.Value
    Case "Select Date ->"
    Select Case TxtDate2.Visible
    Case True:
    MsgBox "Please choose valid dates to filter!",
    vbExclamation, "Invalid Dates"
    Exit Sub
    Case False
    MsgBox "Please choose a valid date to filter!",
    vbExclamation, "Invalid Date"
    Exit Sub
    End Select
    Case Else
    Set frm = Form_Report.ordersPrntSbfrm.Form
    Select Case TxtDate2.Visible
    Case False
    Select Case CmbCriteria.ListIndex
    Case 1:
    Case 3:
    Case 4:
    End Select
    Case True
    Select Case TxtDate2.Value
    Case "Select Date ->"
    MsgBox "Please choose a valid second date
    to filter!", vbExclamation, "Invalid Date"
    Case Else
    frm.RecordSource = "SELECT
    [acc-orders].orderid, acc.kitid, orders.orderdate, """" AS [Cheque
    Number], orders.currcod, currency.currency, orders.quantity,
    acc.accnum, IIf([kits].[kitid]=748,[firstname] & "" "" &
    [surname],[companyname]) AS [Account Name], kits.desc FROM orders INNER
    JOIN (kits INNER JOIN ([currency] INNER JOIN (acc INNER JOIN
    [acc-orders] ON acc.accnum = [acc-orders].accnum) ON currency.currcod =
    acc.currcod) ON kits.kitid = acc.kitid) ON orders.orderid =
    [acc-orders].orderid WHERE ([orders].[orderdate] > " & TxtDate1.Value
    & " AND [orders].[orderdate] < " & TxtDate2.Value & ") ORDER BY
    acc.kitid, orders.orderdate;"
    End Select
    End Select
    End Select
    End Sub

    The txtboxes values are formated as #dd/mm/yyyy#.
    If for example I have a record with an order date of the 22/05/2006, if
    I choose a date from 18/05/2006 to 25/05/2006 it will show that record,
    but if I put 10/05/2006 (or sooner) to 25/05/2006, then subform will be
    empty.

    Can anyone help me?
    Regards,
    Luís Torres
     
  2. Loading...


  3. Rick B

    Rick B
    Expand Collapse
    Guest

    One post per issue please.
    Netiquette FAQ
    http://www.mvps.org/access/netiquette.htm



    --
    Rick B



    <luis.c.torres@gmail.com> wrote in message
    news:1148319138.515752.71340@g10g2000cwb.googlegroups.com...
    Hello.
    I have and unbound form with a subform.
    The form has 4 controls (a combo, two txt boxes and a button).

    The combo has the filter criteria (Equal to, newer than, older than and
    between) and the txt boxes are for the data I want to search for.
    The textboxes are locked and I have two little buttons that open a
    calendar form from where I get the chosen dates.

    When I click the (filter) button on the form I can't seem to filter the
    records on the subform correctly.

    Private Sub FilterBtn_Click()
    Dim frm As Form
    Select Case TxtDate1.Value
    Case "Select Date ->"
    Select Case TxtDate2.Visible
    Case True:
    MsgBox "Please choose valid dates to filter!",
    vbExclamation, "Invalid Dates"
    Exit Sub
    Case False
    MsgBox "Please choose a valid date to filter!",
    vbExclamation, "Invalid Date"
    Exit Sub
    End Select
    Case Else
    Set frm = Form_Report.ordersPrntSbfrm.Form
    Select Case TxtDate2.Visible
    Case False
    Select Case CmbCriteria.ListIndex
    Case 1:
    Case 3:
    Case 4:
    End Select
    Case True
    Select Case TxtDate2.Value
    Case "Select Date ->"
    MsgBox "Please choose a valid second date
    to filter!", vbExclamation, "Invalid Date"
    Case Else
    frm.RecordSource = "SELECT
    [acc-orders].orderid, acc.kitid, orders.orderdate, """" AS [Cheque
    Number], orders.currcod, currency.currency, orders.quantity,
    acc.accnum, IIf([kits].[kitid]=748,[firstname] & "" "" &
    [surname],[companyname]) AS [Account Name], kits.desc FROM orders INNER
    JOIN (kits INNER JOIN ([currency] INNER JOIN (acc INNER JOIN
    [acc-orders] ON acc.accnum = [acc-orders].accnum) ON currency.currcod =
    acc.currcod) ON kits.kitid = acc.kitid) ON orders.orderid =
    [acc-orders].orderid WHERE ([orders].[orderdate] > " & TxtDate1.Value
    & " AND [orders].[orderdate] < " & TxtDate2.Value & ") ORDER BY
    acc.kitid, orders.orderdate;"
    End Select
    End Select
    End Select
    End Sub

    The txtboxes values are formated as #dd/mm/yyyy#.
    If for example I have a record with an order date of the 22/05/2006, if
    I choose a date from 18/05/2006 to 25/05/2006 it will show that record,
    but if I put 10/05/2006 (or sooner) to 25/05/2006, then subform will be
    empty.

    Can anyone help me?
    Regards,
    Luís Torres
     

Share This Page