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...

    Similar Threads Forum Date
    Self Update On The Gurdwara Blogs Apr 3, 2017
    Sikh News BREAKING: Young Sikh Shot Dead In Jalalabad, Afghanistan . - Sikh24 News & Updates Breaking News Oct 2, 2016
    Sikh News Sikh Candidate In Hamilton Disgusted By Racist Vandalism - Newstalk ZB Breaking News Aug 22, 2016
    Sikh News Amit Jani: Trump Leverages Fear In Americans, Wrong Candidate For Our Nation's Future Breaking News Feb 18, 2016
    Update Blogs Oct 16, 2015

  3. OP
    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
     
Since you're here... we have a small favor to ask...     Become a Supporter      ::     Make a Contribution     


Share This Page