Welcome to SPN

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

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page