By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,503 Members | 2,788 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,503 IT Pros & Developers. It's quick & easy.

Date Query problem

P: n/a
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

May 22 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi Luis,

Try running just the query in the Access Query builder with your
parameters in question and see what results you get. If the query
produces the wrong results (or no results) then your problem is in the
sql.

If the query produces the correct results in the query builder then the
problem is not in the sql. Your form code may not be passing in the
corret parameters to the sql code. To find out where the problem is -
set break points in the form code and step through the code in Debug
mode (you can press the F8 key to step through the code in debug mode -
set a break point first by clicking the left edge of the code moddule
next to any line of code except the titles of subroutines). Then you
can press the F8 key to step through the code. You can place your mouse
over a variable and see the contents of that variable in a message that
will appear right next to the mouse pointer.

hth
Rich

*** Sent via Developersdex http://www.developersdex.com ***
May 22 '06 #2

P: n/a
Hello Rich,

Thankyou for your reply.

I tested the sql code in a query like this:

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) Between [Forms]![report]![TxtDate1].[value]
And [Forms]![report]![TxtDate2].[value]))
ORDER BY acc.kitid, orders.orderdate;

Everything in the query came out perfect but it appears I just can't
seem to put it in the vba code correctly. For what I can see above, the
code sintax is not the same but the references to the objects are
correct.
Any ideas?

Regards,
Luís Torres

May 23 '06 #3

P: n/a
This SQL (yours, as posted):

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) Between [Forms]![report]![TxtDate1].[value]
And [Forms]![report]![TxtDate2].[value]))
ORDER BY acc.kitid, orders.orderdate;

Becomes this when you try to write it in vba:

dim strSQL as strintg
strSQL = "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) Between #" & [Forms]![report]![TxtDate1].[value]
& "#
And #" & [Forms]![report]![TxtDate2].[value])) & "#
ORDER BY acc.kitid, orders.orderdate;"

I have ignored line continuation, but you will have deal with that to spread
the SQL across several lines.

Summary: First replace double quotes with single quotes. Then put a double
quote at beginning and end of SQL string. Then break the references to the
field dates apart so that you can insert the "#" delimiter for the date
fields.

HTH
May 23 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.