Date Query Problem

[luis.c.torres]

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

 

[Rick B]

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



--
Rick B



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