Date Range-repost (can't find my question and answer)

  • Thread starter Thread starter gal
  • Start date Start date
G

gal

Hi all, I am stuck with this form....
I have a form that runs report by specifying a date range.
I've set up the form when a user enters StartDate
(txtStart) and EndDate (txtEnd) and I get the results for
the specified date range. How do I code my form when a
user enters only a StartDate the reports shows all data
from the date to the end, or when a user enters only
EndDate the report displays all records up to this date,
and finally when the date boxes are left blank , the
report shows all records.
Thank you
Galin
 
Don't really think you can based on the way you designed it.

If you build a form that pops up and asks for the values, I gues you could
create some code to create a filter based on your criteria.

If your query is looking for values between [EnterStartDate] and
[EnterEndDate], then that is what it looks for.

You could train your users to enter a really old date to get all up to a
date.

You might come up with some really fancy criteria using the Nz function.
Basically:

If Start date null and end not null, do one thing.
If end null and start not, do sometyhing else
If neither null do something else
and if both null do something else.

Would be a really long string but it should work.

Hope that gets you on the right track.

Rick B


Hi all, I am stuck with this form....
I have a form that runs report by specifying a date range.
I've set up the form when a user enters StartDate
(txtStart) and EndDate (txtEnd) and I get the results for
the specified date range. How do I code my form when a
user enters only a StartDate the reports shows all data
from the date to the end, or when a user enters only
EndDate the report displays all records up to this date,
and finally when the date boxes are left blank , the
report shows all records.
Thank you
Galin
 
When you press the Continue button on your form to open the report, set the
value of the empty textbox(es) to the limit of the dates in the recordset
before you actually open the report. Another option would be to just set
them to some ridiculous date which you'll never reach (perhaps 1/1/1900 and
12/31/2300).

To get the maximum or minimum date in the recordset use the DMax or DMin
function.
 
You mean I go to properties on each box(StartDate and
EndDate) then Default Value and set criteria
[dteStartDate]>1995/1/1 in dteStartDate
and
[dteEndDate]<2004/5/5 in dteEndDate

I tried the above but it didn't work...
Maybe you are reffering to something else
 
One other approach may be to design different queries. Example.
qryOne where date >= to the forms startedate
qryTwo where date <= forms enddate
qryThree where date between forms startedate and enddate
qryFour with no criteria.

If statement to check the controls on your form and set the recordsources of
your report accordingly.
(Air Code)

Private Sub Report_Open(Cancel As Integer)
Dim dtStart As Date
Dim dtEnd As Date

dtStart = Forms!MyForm!txtStart
dtEnd = Forms!MyForm!txtStart

If IsNull(dtStart) And IsNull(dtEnd) Then
Me.RecordSource = "qryFour"
ElseIf Not IsNull(dtStart) And IsNull(dtEnd) Then
Me.RecordSource = "qryOne"
ElseIf IsNull(dtStart) And Not IsNull(dtEnd) Then
Me.RecordSource = "qryTwo"
Else
Me.RecordSource = "qryThree"
End If
--
Reggie

www.smittysinet.com
----------
You mean I go to properties on each box(StartDate and
EndDate) then Default Value and set criteria
[dteStartDate]>1995/1/1 in dteStartDate
and
[dteEndDate]<2004/5/5 in dteEndDate

I tried the above but it didn't work...
Maybe you are reffering to something else



-----Original Message-----
When you press the Continue button on your form to open the report, set the
value of the empty textbox(es) to the limit of the dates in the recordset
before you actually open the report. Another option would be to just set
them to some ridiculous date which you'll never reach (perhaps 1/1/1900 and
12/31/2300).

To get the maximum or minimum date in the recordset use the DMax or DMin
function.

--
Wayne Morgan
Microsoft Access MVP





.
Click to expand...
Click to expand...
 
No, setting the Default Value may be a good place to start, it will give the
user an idea of what the possible range is, but what if the user deletes one
of the values then forgets to put another one in. I am simply referring to
assigning a value to the textbox if the textbox is left blank. To use dates,
you'll need date delimiters so that they don't appear to be a division
problems (i.e. #1995/1/1#). Also, the query will probably want the value to
be in US format (month/day/year).

After the user fills in the dates, what are you doing next?

--
Wayne Morgan
Microsoft Access MVP


You mean I go to properties on each box(StartDate and
EndDate) then Default Value and set criteria
[dteStartDate]>1995/1/1 in dteStartDate
and
[dteEndDate]<2004/5/5 in dteEndDate

I tried the above but it didn't work...
Maybe you are reffering to something else



-----Original Message-----
When you press the Continue button on your form to open the report, set the
value of the empty textbox(es) to the limit of the dates in the recordset
before you actually open the report. Another option would be to just set
them to some ridiculous date which you'll never reach (perhaps 1/1/1900 and
12/31/2300).

To get the maximum or minimum date in the recordset use the DMax or DMin
function.

--
Wayne Morgan
Microsoft Access MVP





.
Click to expand...
Click to expand...
 
Hi all, I am stuck with this form....
I have a form that runs report by specifying a date range.
I've set up the form when a user enters StartDate
(txtStart) and EndDate (txtEnd) and I get the results for
the specified date range. How do I code my form when a
user enters only a StartDate the reports shows all data
from the date to the end, or when a user enters only
EndDate the report displays all records up to this date,
and finally when the date boxes are left blank , the
Click to expand...

Great question...and I do this all the time:

you make a nice prompt report. Remove ALL parameters from he query..they are
yuk anyway.

So, the code to open he report will look like:

dim strWhere as string

if isnull(me!txtStartDate) = false then

strWhere = "InvoiceDate >= #" & format(me!txtStartDate,"mm/dd/yyyy") &
"#"

end if

if isnull(me!txtEndDate) = false then

if strWhere <> "" then
strWhere = strWhere & " and "
endif

strWhere = "InvoiceDate <= #" & format(me!txtEndDate,"mm/dd/yyyy") & "#"

end if

docmd.OpenReprot "yourreport",acViewPreview,,strWhere


The above works well..and if you leave both start/end date blank..you get
all....and entering ONLY start, or END date will get you also what you want.

Here is some other ideas for nice report prompt screens:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn
 
Unfortunately, after executing the code I am receiving a
blank report. I copied the code and I changed "InvoiceDate"
from your example with ROUNDED_DATE (which is the date
field in my table). The code compiles and runs but a blank
report is displayed. Am I doing something wrong?
 
After the user fills the dates the form runs a report that
displayes total work hours based on the dates specified.
Do I need a code to assign value to my text box or it's
somewhere in the properties window?
-----Original Message-----
No, setting the Default Value may be a good place to start, it will give the
user an idea of what the possible range is, but what if the user deletes one
of the values then forgets to put another one in. I am simply referring to
assigning a value to the textbox if the textbox is left blank. To use dates,
you'll need date delimiters so that they don't appear to be a division
problems (i.e. #1995/1/1#). Also, the query will probably want the value to
be in US format (month/day/year).

After the user fills in the dates, what are you doing next?

--
Wayne Morgan
Microsoft Access MVP


You mean I go to properties on each box(StartDate and
EndDate) then Default Value and set criteria
[dteStartDate]>1995/1/1 in dteStartDate
and
[dteEndDate]<2004/5/5 in dteEndDate

I tried the above but it didn't work...
Maybe you are reffering to something else



-----Original Message-----
When you press the Continue button on your form to open the report, set the
value of the empty textbox(es) to the limit of the
Click to expand...
dates
in the recordset
before you actually open the report. Another option
Click to expand...
would
be to just set
them to some ridiculous date which you'll never reach (perhaps 1/1/1900 and
12/31/2300).

To get the maximum or minimum date in the recordset use the DMax or DMin
function.

--
Wayne Morgan
Microsoft Access MVP


Hi all, I am stuck with this form....
I have a form that runs report by specifying a date range.
I've set up the form when a user enters StartDate
(txtStart) and EndDate (txtEnd) and I get the results for
the specified date range. How do I code my form when a
user enters only a StartDate the reports shows all data
from the date to the end, or when a user enters only
EndDate the report displays all records up to this date,
and finally when the date boxes are left blank , the
report shows all records.
Thank you
Galin


.
Click to expand...
Click to expand...


.
Click to expand...
 
I'm sorry Albert. I had an extra coma in my code. Now I am
able to display the report but it still doesn't catch the
date range.The report displays all records for the entire
period.
what am I doing wrong?
 
How are you opening the report, by using a button on the form once the dates
are filled in? If so, in the code in the button's click event, set the value
of the textboxes then open the report. Albert's method is different, but
will still need some action in the button's click event prior to opening the
report in order to adjust the filter statement in the DoCmd.OpenReport call.
 
Try placing the debug print as follows right before the OpenReprot:

debug.print strWhere
docmd.OpenReprot "yourreport",acViewPreview,,strWhere

After you run the report...close the report..and the go to the debug.window
(hit ctrl-g to get there).

What does the strWhere caluse show in the debug window?
 
Back
Top