searching within dates

[evilcowstare via AccessMonster.com]

Hi, I have searched the forum for answers on this and to be honest as a
novice I find it a bit confusing so apologies if it is simple.

There are some searches that I want to apply to my database.

1. To search for all records between 2 dates and display them in a report
2. To be able to show all records which have a selection against them made
from a combo box
3. To be able to combine the two, selecting the option from a combo and then
selecting dates within that

Ok and now in more depth so I can hopefully get across what I mean....

1. I have already made a form with 2 text boxes, all I want to set up is so
that in the first box (startdate) you enter the date and in the second
(enddate) you enter the last date, then by a push on a button it brings up a
report that just shows all the records within those two dates, I can make
standard reports ok and im guessing for this i need some sort of filter, I
just dont know how to do it.

2. In my form I have several drop down boxes that get their info from a table
via a query, when the whole form is saved all the info is stored in a main
table. What i want to be able to do is have another form with a combo (a copy
of one from the main form) and when an item is selected from the combo I can
have a button that will bring up a report of all the records that had that
item selected for it.

3. A combination of two, to be able to 1st select the item and then put two
dates in and get a report of all the records that have the item between the
dates.

I am quite new to this so please keep it simple, im trying to learn but as
you can imagine a lot of this stuff takes time :-)

Thank you loads to anyone that spares the time to help me!

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200702/1

[Allen Browne]

For part 1, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
The example shows how to build the WhereCondition for OpenReport to limit
the report to the dates.

For an example of how to combine the dates with other criteria, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
This one is a bit more involved, but it contains a downloadable example that
combines dates, combos, and text boxes. The examples applies the results to
the Filter of a form, but the code is identical to create the WhereCondition
for OpenReport. About 3/4 of the way down the article it shows how to add
one line to apply it to a report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"evilcowsta re via AccessMonster.c om" <u31650@uwewrot e in message
news:6dab91d5a3 6e8@uwe...

Hi, I have searched the forum for answers on this and to be honest as a
novice I find it a bit confusing so apologies if it is simple.

There are some searches that I want to apply to my database.

1. To search for all records between 2 dates and display them in a report
2. To be able to show all records which have a selection against them made
from a combo box
3. To be able to combine the two, selecting the option from a combo and
then
selecting dates within that

Ok and now in more depth so I can hopefully get across what I mean....

1. I have already made a form with 2 text boxes, all I want to set up is
so
that in the first box (startdate) you enter the date and in the second
(enddate) you enter the last date, then by a push on a button it brings up
a
report that just shows all the records within those two dates, I can make
standard reports ok and im guessing for this i need some sort of filter, I
just dont know how to do it.

2. In my form I have several drop down boxes that get their info from a
table
via a query, when the whole form is saved all the info is stored in a main
table. What i want to be able to do is have another form with a combo (a
copy
of one from the main form) and when an item is selected from the combo I
can
have a button that will bring up a report of all the records that had that
item selected for it.

3. A combination of two, to be able to 1st select the item and then put
two
dates in and get a report of all the records that have the item between
the
dates.

I am quite new to this so please keep it simple, im trying to learn but as
you can imagine a lot of this stuff takes time :-)

Thank you loads to anyone that spares the time to help me!

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200702/1

[evilcowstare via AccessMonster.com]

Hi Thanks very much for the links they are really good. Just one problem I
have is that In the 1st link I am using Method 2, I have followed it through
but when I click OK in the form nothing happens. Im probably being thick but
what am I missing, the report doesnt open, should that open based on the code
it says to use? or do i need to open the report then run the code, at which
point how do i do that if the code is already in the OnClick?

My Report is called : Date Report
The field on the report containing the date is called: Date Job Received

The Code is.....
Option Compare Database

Private Sub Command4_Click( )
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Date Report"
strField = "Date Job Received"

If IsNull(Me.txtSt artDate) Then
If Not IsNull(Me.txtEn dDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEn dDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEn dDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtSt artDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtSt artDate,
conDateFormat) _
& " And " & Format(Me.txtEn dDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenRepor t strReport, acViewPreview, , strWhere
End Sub

Private Sub Command5_Click( )
DoCmd.Close acForm, Me.Name
End Sub

Private Sub OK_Click()

End Sub
Any Ideas what im missing?

Thank You

Allen Browne wrote:

>For part 1, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
The example shows how to build the WhereCondition for OpenReport to limit
the report to the dates.

For an example of how to combine the dates with other criteria, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
This one is a bit more involved, but it contains a downloadable example that
combines dates, combos, and text boxes. The examples applies the results to
the Filter of a form, but the code is identical to create the WhereCondition
for OpenReport. About 3/4 of the way down the article it shows how to add
one line to apply it to a report.

>Hi, I have searched the forum for answers on this and to be honest as a
novice I find it a bit confusing so apologies if it is simple.

[quoted text clipped - 39 lines]

>>
Thank you loads to anyone that spares the time to help me!

--
Message posted via http://www.accessmonster.com

[evilcowstare via AccessMonster.com]

Hi Thanks very much for the links they are really good. Just one problem I
have is that In the 1st link I am using Method 2, I have followed it through
but when I click OK in the form nothing happens. Im probably being thick but
what am I missing, the report doesnt open, should that open based on the code
it says to use? or do i need to open the report then run the code, at which
point how do i do that if the code is already in the OnClick?

My Report is called : Date Report
The field on the report containing the date is called: Date Job Received

The Code is.....
Option Compare Database

Private Sub Command4_Click( )
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Date Report"
strField = "Date Job Received"

If IsNull(Me.txtSt artDate) Then
If Not IsNull(Me.txtEn dDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEn dDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEn dDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtSt artDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtSt artDate,
conDateFormat) _
& " And " & Format(Me.txtEn dDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenRepor t strReport, acViewPreview, , strWhere
End Sub

Private Sub Command5_Click( )
DoCmd.Close acForm, Me.Name
End Sub

Private Sub OK_Click()

End Sub
Any Ideas what im missing?

Thank You.

Allen Browne wrote:

>For part 1, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
The example shows how to build the WhereCondition for OpenReport to limit
the report to the dates.

For an example of how to combine the dates with other criteria, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
This one is a bit more involved, but it contains a downloadable example that
combines dates, combos, and text boxes. The examples applies the results to
the Filter of a form, but the code is identical to create the WhereCondition
for OpenReport. About 3/4 of the way down the article it shows how to add
one line to apply it to a report.

>Hi, I have searched the forum for answers on this and to be honest as a
novice I find it a bit confusing so apologies if it is simple.

[quoted text clipped - 39 lines]

>>
Thank you loads to anyone that spares the time to help me!

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200702/1

[Allen Browne]

The report should open.

The field contains spaces in its name, so will need square brackets around
it, like this:
strField = "[Date Job Received]"

Immediately above the DoCmd.OpenRepor t line, remove the single quote from
the start of the Debug.Print line. On the next line, enter:
Stop

When the button clicks, it should stop at this line. (If it doesn't the code
is not executing at all.) Then press Ctrl+G to open the Immediate Window and
see what was written there. It should be just like the WHERE clause in a
query. Then press F8 to single-step through the routine. Does the
DoCmd.OpenRepor t line get hightlighted? Then does the report open? If not,
is something cancelling it? The NoData event perhaps?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"evilcowsta re via AccessMonster.c om" <u31650@uwewrot e in message
news:6db5b9984b 220@uwe...

Hi Thanks very much for the links they are really good. Just one problem I
have is that In the 1st link I am using Method 2, I have followed it
through
but when I click OK in the form nothing happens. Im probably being thick
but
what am I missing, the report doesnt open, should that open based on the
code
it says to use? or do i need to open the report then run the code, at
which
point how do i do that if the code is already in the OnClick?

My Report is called : Date Report
The field on the report containing the date is called: Date Job Received

The Code is.....
Option Compare Database

Private Sub Command4_Click( )
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "Date Report"
strField = "Date Job Received"

If IsNull(Me.txtSt artDate) Then
If Not IsNull(Me.txtEn dDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEn dDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEn dDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtSt artDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtSt artDate,
conDateFormat) _
& " And " & Format(Me.txtEn dDate, conDateFormat)
End If
End If

' Debug.Print strWhere 'For debugging purposes only.
DoCmd.OpenRepor t strReport, acViewPreview, , strWhere
End Sub

Private Sub Command5_Click( )
DoCmd.Close acForm, Me.Name
End Sub

Private Sub OK_Click()

End Sub
Any Ideas what im missing?

Thank You.

Allen Browne wrote:

>>For part 1, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
The example shows how to build the WhereCondition for OpenReport to limit
the report to the dates.

For an example of how to combine the dates with other criteria, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
This one is a bit more involved, but it contains a downloadable example
that
combines dates, combos, and text boxes. The examples applies the results
to
the Filter of a form, but the code is identical to create the
WhereConditio n
for OpenReport. About 3/4 of the way down the article it shows how to add
one line to apply it to a report.

>>Hi, I have searched the forum for answers on this and to be honest as a
novice I find it a bit confusing so apologies if it is simple.

[quoted text clipped - 39 lines]

>>>
Thank you loads to anyone that spares the time to help me!

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200702/1

[evilcowstare via AccessMonster.com]

Ok added the [ ] to the names, wrote Stop below Debug.Print strWhere
Still didnt do anything.
When I click the runsub/user button it brings up a macro box with macro name
at the top, if i type anything in i get the option to click create.

I brought up the Immediate area and there is nothing in it

Maybe there is an easier way to bring up a report and filter it between 2
different dates?

Allen Browne wrote:

>The report should open.

The field contains spaces in its name, so will need square brackets around
it, like this:
strField = "[Date Job Received]"

Immediately above the DoCmd.OpenRepor t line, remove the single quote from
the start of the Debug.Print line. On the next line, enter:
Stop

>
When the button clicks, it should stop at this line. (If it doesn't the code
is not executing at all.) Then press Ctrl+G to open the Immediate Window and
see what was written there. It should be just like the WHERE clause in a
query. Then press F8 to single-step through the routine. Does the
DoCmd.OpenRepo rt line get hightlighted? Then does the report open? If not,
is something cancelling it? The NoData event perhaps?

>Hi Thanks very much for the links they are really good. Just one problem I
have is that In the 1st link I am using Method 2, I have followed it

[quoted text clipped - 80 lines]

>>>>
Thank you loads to anyone that spares the time to help me!

--
Message posted via http://www.accessmonster.com

[Allen Browne]

If the code is not running, it's not connected to your button.

Open the form in design view.
Right-click the command button, and choose Properties.
Set the On Click property to [Event Procedure]
Click the Build button (...) beside that.
Access opens the code window.
The code should be in there.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"evilcowsta re via AccessMonster.c om" <u31650@uwewrot e in message
news:6dc0b95d8d 7e6@uwe...

Ok added the [ ] to the names, wrote Stop below Debug.Print strWhere
Still didnt do anything.
When I click the runsub/user button it brings up a macro box with macro
name
at the top, if i type anything in i get the option to click create.

I brought up the Immediate area and there is nothing in it

Maybe there is an easier way to bring up a report and filter it between 2
different dates?

Allen Browne wrote:

>>The report should open.

The field contains spaces in its name, so will need square brackets around
it, like this:
strField = "[Date Job Received]"

Immediately above the DoCmd.OpenRepor t line, remove the single quote from
the start of the Debug.Print line. On the next line, enter:
Stop

>>
When the button clicks, it should stop at this line. (If it doesn't the
code
is not executing at all.) Then press Ctrl+G to open the Immediate Window
and
see what was written there. It should be just like the WHERE clause in a
query. Then press F8 to single-step through the routine. Does the
DoCmd.OpenRep ort line get hightlighted? Then does the report open? If not,
is something cancelling it? The NoData event perhaps?

>>Hi Thanks very much for the links they are really good. Just one problem
I
have is that In the 1st link I am using Method 2, I have followed it

[quoted text clipped - 80 lines]

>>>>>
Thank you loads to anyone that spares the time to help me!

[evilcowstare via AccessMonster.com]

Hi, the event procedure was there, but after I looked at your reply I started
to look properly into and noticed that on the drop down box on the left in VB
in said (general) no the "ok" button.
Changed it over, now works fab. Thank you very much for the kick to the head :
-)

Allen Browne wrote:

>If the code is not running, it's not connected to your button.

Open the form in design view.
Right-click the command button, and choose Properties.
Set the On Click property to [Event Procedure]
Click the Build button (...) beside that.
Access opens the code window.
The code should be in there.

>Ok added the [ ] to the names, wrote Stop below Debug.Print strWhere
Still didnt do anything.

[quoted text clipped - 32 lines]

>>>>>>
>Thank you loads to anyone that spares the time to help me!

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200702/1