Filtering query between 2 date ranges

  • Thread starter Thread starter gtslabs
  • Start date Start date
G

gtslabs

I have a query that has 2 columns: Dates and Projects.
I want to view the projects that were done on and between 2 dates.
I had set up a form and put 3 list boxes.
lboxStartDate
lboxEndDate
lboxProjects.

I tried some examples found on the net but could not get it to work


I also was not able to filter the lboxEndDate to be show dates >=
lboxStartDate.

Can I get some help with this?
 
Post the SQL statement of the query that you tried to use, but that didn't
work. How are you running this query -- from a command button on the form
that holds the three listboxes? What are the following properties of each
listbox:
RowSourceType
RowSource (if = name of query or table, give details about
table/query)
BoundColumn
Format
ColumnCount
ColumnWidths
 
Post the SQL statement of the query that you tried to use, but that didn't
work. How are you running this query -- from a command button on the form
that holds the three listboxes? What are the following properties of each
listbox:
        RowSourceType
        RowSource  (if = name of query or table, give detailsabout
table/query)
        BoundColumn
        Format
        ColumnCount
        ColumnWidths

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/









- Show quoted text -
Click to expand...

Ken,

my LboxStartDate has the following SQL:

SELECT DISTINCT qryBreakScheduleByDate.BDate
FROM qryBreakScheduleByDate
ORDER BY qryBreakScheduleByDate.BDate;

and my LboxEndDate has the following:
SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)=[Forms]![frmSummary]!
[LboxStartDate]))
ORDER BY qryBreakScheduleByDate.BDate;
Both are bound to column 1

Currently I have the EndDate Lbox equal to the first box and that
works as written below but I wanted to get only dates >= the
StartDate.
I tried >= but it did not work. I tried adding WHERE >= [Forms]!
[frmSummary]![LboxStartDate] and that gave me the aggregate expression
error.
 
Change the query for LboxEndDate to the following:

SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)>=Format([Forms]![frmSummary]!
[LboxStartDate], "\#mm\/dd\/yyyy\#")))
ORDER BY qryBreakScheduleByDate.BDate;

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Post the SQL statement of the query that you tried to use, but that didn't
work. How are you running this query -- from a command button on the form
that holds the three listboxes? What are the following properties of each
listbox:
RowSourceType
RowSource (if = name of query or table, give details about
table/query)
BoundColumn
Format
ColumnCount
ColumnWidths

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/









- Show quoted text -
Click to expand...

Ken,

my LboxStartDate has the following SQL:

SELECT DISTINCT qryBreakScheduleByDate.BDate
FROM qryBreakScheduleByDate
ORDER BY qryBreakScheduleByDate.BDate;

and my LboxEndDate has the following:
SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)=[Forms]![frmSummary]!
[LboxStartDate]))
ORDER BY qryBreakScheduleByDate.BDate;
Both are bound to column 1

Currently I have the EndDate Lbox equal to the first box and that
works as written below but I wanted to get only dates >= the
StartDate.
I tried >= but it did not work. I tried adding WHERE >= [Forms]!
[frmSummary]![LboxStartDate] and that gave me the aggregate expression
error.
 
Change the query for LboxEndDate to the following:

SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)>=Format([Forms]![frmSummary]!
[LboxStartDate], "\#mm\/dd\/yyyy\#")))
ORDER BY qryBreakScheduleByDate.BDate;

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Post the SQL statement of the query that you tried to use, but that didn't
work. How are you running this query -- from a command button on the form
that holds the three listboxes? What are the following properties of each
listbox:
RowSourceType
RowSource (if = name of query or table, give details about
table/query)
BoundColumn
Format
ColumnCount
ColumnWidths

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
Click to expand...
- Show quoted text -
Click to expand...

Ken,

my LboxStartDate has the following SQL:

SELECT DISTINCT qryBreakScheduleByDate.BDate
FROM qryBreakScheduleByDate
ORDER BY qryBreakScheduleByDate.BDate;

and my LboxEndDate has the following:
SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)=[Forms]![frmSummary]!
[LboxStartDate]))
ORDER BY qryBreakScheduleByDate.BDate;
 Both are bound to column 1

Currently I have the EndDate Lbox equal to the first box and that
works as written below but I wanted to get only dates >= the
StartDate.
I tried >= but it did not work. I tried adding WHERE >= [Forms]!
[frmSummary]![LboxStartDate] and that gave me the aggregate expression
error.- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks Ken but it did not filter the EndDate.
I checked for errors when I copied and pasted.
 
Are you requering the second listbox after you update the first listbox? You
need to do that so that the second listbox will be properly filtered. You
can run code like this, using the first listbox' AfterUpdate event:

Private Sub LboxStartDate_AfterUpdate()
Me.LboxEndDate.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Change the query for LboxEndDate to the following:

SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)>=Format([Forms]![frmSummary]!
[LboxStartDate], "\#mm\/dd\/yyyy\#")))
ORDER BY qryBreakScheduleByDate.BDate;

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Post the SQL statement of the query that you tried to use, but that
didn't
work. How are you running this query -- from a command button on the
form
that holds the three listboxes? What are the following properties of
each
listbox:
RowSourceType
RowSource (if = name of query or table, give details about
table/query)
BoundColumn
Format
ColumnCount
ColumnWidths

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
Click to expand...
- Show quoted text -
Click to expand...

Ken,

my LboxStartDate has the following SQL:

SELECT DISTINCT qryBreakScheduleByDate.BDate
FROM qryBreakScheduleByDate
ORDER BY qryBreakScheduleByDate.BDate;

and my LboxEndDate has the following:
SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)=[Forms]![frmSummary]!
[LboxStartDate]))
ORDER BY qryBreakScheduleByDate.BDate;
Both are bound to column 1

Currently I have the EndDate Lbox equal to the first box and that
works as written below but I wanted to get only dates >= the
StartDate.
I tried >= but it did not work. I tried adding WHERE >= [Forms]!
[frmSummary]![LboxStartDate] and that gave me the aggregate expression
error.- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks Ken but it did not filter the EndDate.
I checked for errors when I copied and pasted.
 
Are you requering the second listbox after you update the first listbox? You
need to do that so that the second listbox will be properly filtered. You
can run code like this, using the first listbox' AfterUpdate event:

Private Sub LboxStartDate_AfterUpdate()
Me.LboxEndDate.Requery
End Sub

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Change the query for LboxEndDate to the following:
Click to expand...
SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)>=Format([Forms]![frmSummary]!
[LboxStartDate], "\#mm\/dd\/yyyy\#")))
ORDER BY qryBreakScheduleByDate.BDate;
--
Click to expand...

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
Click to expand...
my LboxStartDate has the following SQL:
Click to expand...
SELECT DISTINCT qryBreakScheduleByDate.BDate
FROM qryBreakScheduleByDate
ORDER BY qryBreakScheduleByDate.BDate;
Click to expand...
and my LboxEndDate has the following:
SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)=[Forms]![frmSummary]!
[LboxStartDate]))
ORDER BY qryBreakScheduleByDate.BDate;
Both are bound to column 1
Click to expand...
Currently I have the EndDate Lbox equal to the first box and that
works as written below but I wanted to get only dates >= the
StartDate.
I tried >= but it did not work. I tried adding WHERE >= [Forms]!
[frmSummary]![LboxStartDate] and that gave me the aggregate expression
error.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Thanks Ken but it did not filter the EndDate.
I checked for errors when I copied and pasted.- Hide quoted text -

- Show quoted text -
Click to expand...

Yes, I had that code already.
 
Are you requering the second listbox after you update the first listbox? You
need to do that so that the second listbox will be properly filtered. You
can run code like this, using the first listbox' AfterUpdate event:

Private Sub LboxStartDate_AfterUpdate()
Me.LboxEndDate.Requery
End Sub

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Change the query for LboxEndDate to the following:
Click to expand...
SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)>=Format([Forms]![frmSummary]!
[LboxStartDate], "\#mm\/dd\/yyyy\#")))
ORDER BY qryBreakScheduleByDate.BDate;
--
Click to expand...

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
Click to expand...
my LboxStartDate has the following SQL:
Click to expand...
SELECT DISTINCT qryBreakScheduleByDate.BDate
FROM qryBreakScheduleByDate
ORDER BY qryBreakScheduleByDate.BDate;
Click to expand...
and my LboxEndDate has the following:
SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)=[Forms]![frmSummary]!
[LboxStartDate]))
ORDER BY qryBreakScheduleByDate.BDate;
Both are bound to column 1
Click to expand...
Currently I have the EndDate Lbox equal to the first box and that
works as written below but I wanted to get only dates >= the
StartDate.
I tried >= but it did not work. I tried adding WHERE >= [Forms]!
[frmSummary]![LboxStartDate] and that gave me the aggregate expression
error.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Thanks Ken but it did not filter the EndDate.
I checked for errors when I copied and pasted.- Hide quoted text -

- Show quoted text -
Click to expand...

I was looking at the code here http://allenbrowne.com/casu-08.html
The code at that link worked when I used the textboxes but I changed
the textboxes to listboxes it did not.
I looks like that there is a difference in the way a textbox is called
vs a listbox?
I noticed that the listbox had double quotes around the date and the
text box had none at the line where the debug ended.
Could that be the problem?
 
Let's see your code that you're running.... show all of it, please.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Are you requering the second listbox after you update the first listbox?
You
need to do that so that the second listbox will be properly filtered. You
can run code like this, using the first listbox' AfterUpdate event:

Private Sub LboxStartDate_AfterUpdate()
Me.LboxEndDate.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Change the query for LboxEndDate to the following:
Click to expand...
SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)>=Format([Forms]![frmSummary]!
[LboxStartDate], "\#mm\/dd\/yyyy\#")))
ORDER BY qryBreakScheduleByDate.BDate;
--
Click to expand...

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
Click to expand...
my LboxStartDate has the following SQL:
Click to expand...
SELECT DISTINCT qryBreakScheduleByDate.BDate
FROM qryBreakScheduleByDate
ORDER BY qryBreakScheduleByDate.BDate;
Click to expand...
and my LboxEndDate has the following:
SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)=[Forms]![frmSummary]!
[LboxStartDate]))
ORDER BY qryBreakScheduleByDate.BDate;
Both are bound to column 1
Click to expand...
Currently I have the EndDate Lbox equal to the first box and that
works as written below but I wanted to get only dates >= the
StartDate.
I tried >= but it did not work. I tried adding WHERE >= [Forms]!
[frmSummary]![LboxStartDate] and that gave me the aggregate expression
error.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Thanks Ken but it did not filter the EndDate.
I checked for errors when I copied and pasted.- Hide quoted text -

- Show quoted text -
Click to expand...

I was looking at the code here http://allenbrowne.com/casu-08.html
The code at that link worked when I used the textboxes but I changed
the textboxes to listboxes it did not.
I looks like that there is a difference in the way a textbox is called
vs a listbox?
I noticed that the listbox had double quotes around the date and the
text box had none at the line where the debug ended.
Could that be the problem?
 
Let's see your code that you're running.... show all of it, please.

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Are you requering the second listbox after you update the first listbox?
You
need to do that so that the second listbox will be properly filtered. You
can run code like this, using the first listbox' AfterUpdate event:
Click to expand...
Private Sub LboxStartDate_AfterUpdate()
Me.LboxEndDate.Requery
End Sub

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
Click to expand...
Change the query for LboxEndDate to the following:
SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)>=Format([Forms]![frmSummary]!
[LboxStartDate], "\#mm\/dd\/yyyy\#")))
ORDER BY qryBreakScheduleByDate.BDate;
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
On Nov 15, 9:08 am, "Ken Snell \(MVP\)"
Post the SQL statement of the query that you tried to use, but that
didn't
work. How are you running this query -- from a command button on the
form
that holds the three listboxes? What are the following properties of
each
listbox:
RowSourceType
RowSource (if = name of query or table, give details about
table/query)
BoundColumn
Format
ColumnCount
ColumnWidths
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

I have a query that has 2 columns: Dates and Projects.
I want to view the projects that were done on and between 2 dates..
I had set up a form and put 3 list boxes.
lboxStartDate
lboxEndDate
lboxProjects.
I tried some examples found on the net but could not get it to work
I also was not able to filter the lboxEndDate to be show dates >=
lboxStartDate.
Can I get some help with this?- Hide quoted text -
- Show quoted text -
Ken,
my LboxStartDate has the following SQL:
SELECT DISTINCT qryBreakScheduleByDate.BDate
FROM qryBreakScheduleByDate
ORDER BY qryBreakScheduleByDate.BDate;
and my LboxEndDate has the following:
SELECT qryBreakScheduleByDate.BDate, qryBreakScheduleByDate.Project,
qryBreakScheduleByDate.SerialNo
FROM qryBreakScheduleByDate
WHERE (((qryBreakScheduleByDate.BDate)=[Forms]![frmSummary]!
[LboxStartDate]))
ORDER BY qryBreakScheduleByDate.BDate;
Both are bound to column 1
Currently I have the EndDate Lbox equal to the first box and that
works as written below but I wanted to get only dates >= the
StartDate.
I tried >= but it did not work. I tried adding WHERE >= [Forms]!
[frmSummary]![LboxStartDate] and that gave me the aggregate expression
error.- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Thanks Ken but it did not filter the EndDate.
I checked for errors when I copied and pasted.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

I was looking at the code herehttp://allenbrowne.com/casu-08.html
The code at that link worked when I used the textboxes but I changed
the textboxes to listboxes it did not.
I looks like that there is a difference in the way a textbox is called
vs a listbox?
I noticed that the listbox had double quotes around the date and the
text box had none at the line where the debug ended.
Could that be the problem?- Hide quoted text -

- Show quoted text -
Click to expand...

Not sure how to post code for all objects on a form.
However, I switched to Combo Boxes and everything is working as
needed.
Thanks for your help.
 
Not sure how to post code for all objects on a form.
However, I switched to Combo Boxes and everything is working as
needed.
Thanks for your help.
Click to expand...

My request was to have you show all the code in the form that you're using
for the listboxes. If it's working for comboboxes, then it also can be made
to work for listboxes. But glad to hear you have got it to work.
 
Back
Top