trying to look at dates

[sparks]

Right now I had to build a report that allowed the people to check for
gross outliers in their data input.
short I am looking at 2.5* std dev + -

anyway I used 2 dummy variables in the query the report is tied to.
start date and end date. This pops up an input box for these values
and they put in 11/01/08 and 11/31/08 and it runs the report showing
the outliers.
I watched and everyone is looking at month not date
Now I was thinking instead fof this in the query under recDATE

>=[start_date] And <=[end_date]

is there a way of just asking for a month and year and then comparing
the recDATE without day in it?

so one check for a new input of CKdate being 11/08

Later I might even do something first to ask month or range.

[Salad]

sparks wrote:

Right now I had to build a report that allowed the people to check for
gross outliers in their data input.
short I am looking at 2.5* std dev + -

anyway I used 2 dummy variables in the query the report is tied to.
start date and end date. This pops up an input box for these values
and they put in 11/01/08 and 11/31/08 and it runs the report showing
the outliers.
I watched and everyone is looking at month not date
Now I was thinking instead fof this in the query under recDATE

>>=[start_date] And <=[end_date]

is there a way of just asking for a month and year and then comparing
the recDATE without day in it?

so one check for a new input of CKdate being 11/08

Later I might even do something first to ask month or range.

? format(date(),"yyyymm")
200810
? datepart("m",date())
10
? datepart("d",date())
15
? datepart("yyyy",date())
2008

Using Format or DatePart you should be able to create a criteria string
to pass when opening the report. Notice that Format returns a string
value and DatePart a numeric value. You could also use Year(), Month(),
Day() as well.

[Tom van Stiphout]

On Wed, 15 Oct 2008 12:56:15 GMT, sparks <sp****@comcast.netwrote:

I would suggest you create a Criteria form, where the user can enter a
Month and Year. You then turn that into a date range which you feed to
the report.

-Tom.
Microsoft Access MVP

>Right now I had to build a report that allowed the people to check for
gross outliers in their data input.
short I am looking at 2.5* std dev + -

anyway I used 2 dummy variables in the query the report is tied to.
start date and end date. This pops up an input box for these values
and they put in 11/01/08 and 11/31/08 and it runs the report showing
the outliers.
I watched and everyone is looking at month not date
Now I was thinking instead fof this in the query under recDATE

>>=[start_date] And <=[end_date]

is there a way of just asking for a month and year and then comparing
the recDATE without day in it?

so one check for a new input of CKdate being 11/08

Later I might even do something first to ask month or range.

[sparks]

Ok the datepart worked fine on the month entry.

When you say criteria form do you mean a form with something like this

start_date text box
end_date text box

button to open the report.
if you click on the button it will pop up a start_date box for input
then and end_date box for input.

can the start_date and end_date be passed to the report instead

DoCmd.OpenReport stDocName, acPreview it has a where and an open args
that can be passed to the report.

start_date=text1.value
end_date=text2.value
something like that?

have to try that it sounds interesting

On Wed, 15 Oct 2008 06:57:01 -0700, Tom van Stiphout
<to*************@cox.netwrote:

>Criteria form

[Salad]

sparks wrote:

Ok the datepart worked fine on the month entry.

When you say criteria form do you mean a form with something like this

start_date text box
end_date text box

button to open the report.
if you click on the button it will pop up a start_date box for input
then and end_date box for input.

can the start_date and end_date be passed to the report instead

DoCmd.OpenReport stDocName, acPreview it has a where and an open args
that can be passed to the report.

start_date=text1.value
end_date=text2.value
something like that?

have to try that it sounds interesting

On Wed, 15 Oct 2008 06:57:01 -0700, Tom van Stiphout
<to*************@cox.netwrote:

>>Criteria form

Yes. Create a form that has the data entry fields for the report.
Docmd.Openform "ReportNameCriteria"

You'd have text boxes for your filter on the form and a command button
to open the report, another to exit. For the Command button to run/open
the report you'd have some code similar to the aircode below.
Dim strF As String
If Not IsNull(Me.TextBoxName1) then
strF = "FldNameToFilter1 = " & Me.TextBoxName1 & " And "
Endif
If Not IsNull(Me.TextBoxName2) then
strF = strF & _
"FieldNameToFilter2 = " & Me.TextBoxName2 & " And "
Endif
....any other additional filtering that is required

'now remove the " And " at the end of the filter string
If strF "" Then strF = Left(strF,Len(strF)-5)

Docmd.OpenReport "ReportName",,,strF

You can do the same/similar thing to open a Form

Things to remember.
String require quotes
Dates require #
Numbers nothing

"StringFld = '" & TextBoxVal & "'"
"DateFld = #" & TextBoxVal & "#"
"NumberFld = " & TextBoxVal

[sparks]

OK after modifying everything I can think of.
I have sent these 3 strings to the report

DoCmd.OpenReport stDocName, acPreview, , , strF

debug.print strF

Start_date = #11/1/2006# And End_Date = #11/30/2006#
Start_date = 11/1/2006 And End_Date = 11/30/2006
Start_date = "11/1/2006" And End_Date = "11/30/2006"
every one returns type mismatch

I guess I don't understand what is up

On Wed, 15 Oct 2008 09:17:07 -0700, Salad <oi*@vinegar.comwrote:

>sparks wrote:

>Ok the datepart worked fine on the month entry.

When you say criteria form do you mean a form with something like this

start_date text box
end_date text box

button to open the report.
if you click on the button it will pop up a start_date box for input
then and end_date box for input.

can the start_date and end_date be passed to the report instead

DoCmd.OpenReport stDocName, acPreview it has a where and an open args
that can be passed to the report.

start_date=text1.value
end_date=text2.value
something like that?

have to try that it sounds interesting

On Wed, 15 Oct 2008 06:57:01 -0700, Tom van Stiphout
<to*************@cox.netwrote:

>>>Criteria form

Yes. Create a form that has the data entry fields for the report.
Docmd.Openform "ReportNameCriteria"

You'd have text boxes for your filter on the form and a command button
to open the report, another to exit. For the Command button to run/open
the report you'd have some code similar to the aircode below.
Dim strF As String
If Not IsNull(Me.TextBoxName1) then
strF = "FldNameToFilter1 = " & Me.TextBoxName1 & " And "
Endif
If Not IsNull(Me.TextBoxName2) then
strF = strF & _
"FieldNameToFilter2 = " & Me.TextBoxName2 & " And "
Endif
....any other additional filtering that is required

'now remove the " And " at the end of the filter string
If strF "" Then strF = Left(strF,Len(strF)-5)

Docmd.OpenReport "ReportName",,,strF

You can do the same/similar thing to open a Form

Things to remember.
String require quotes
Dates require #
Numbers nothing

"StringFld = '" & TextBoxVal & "'"
"DateFld = #" & TextBoxVal & "#"
"NumberFld = " & TextBoxVal

[sparks]

now I see what is up..
teach me to cut and paste
DoCmd.OpenReport stDocName, acPreview, , , ,strF
needed another ,

now it passes any of them..opens the report and request input of
Start_Date and End_Date

I am wondering that since these are dummy variables in the query that
the report is tied to that it can not pass them to the report to then
pass them to the query.

On Wed, 15 Oct 2008 19:47:25 GMT, sparks <sp****@comcast.netwrote:

>OK after modifying everything I can think of.
I have sent these 3 strings to the report

DoCmd.OpenReport stDocName, acPreview, , , strF

debug.print strF

Start_date = #11/1/2006# And End_Date = #11/30/2006#
Start_date = 11/1/2006 And End_Date = 11/30/2006
Start_date = "11/1/2006" And End_Date = "11/30/2006"
every one returns type mismatch

I guess I don't understand what is up

On Wed, 15 Oct 2008 09:17:07 -0700, Salad <oi*@vinegar.comwrote:

>>sparks wrote:

>>Ok the datepart worked fine on the month entry.

When you say criteria form do you mean a form with something like this

start_date text box
end_date text box

button to open the report.
if you click on the button it will pop up a start_date box for input
then and end_date box for input.

can the start_date and end_date be passed to the report instead

DoCmd.OpenReport stDocName, acPreview it has a where and an open args
that can be passed to the report.

start_date=text1.value
end_date=text2.value
something like that?

have to try that it sounds interesting

On Wed, 15 Oct 2008 06:57:01 -0700, Tom van Stiphout
<to*************@cox.netwrote:
Criteria form

Yes. Create a form that has the data entry fields for the report.
Docmd.Openform "ReportNameCriteria"

You'd have text boxes for your filter on the form and a command button
to open the report, another to exit. For the Command button to run/open
the report you'd have some code similar to the aircode below.
Dim strF As String
If Not IsNull(Me.TextBoxName1) then
strF = "FldNameToFilter1 = " & Me.TextBoxName1 & " And "
Endif
If Not IsNull(Me.TextBoxName2) then
strF = strF & _
"FieldNameToFilter2 = " & Me.TextBoxName2 & " And "
Endif
....any other additional filtering that is required

'now remove the " And " at the end of the filter string
If strF "" Then strF = Left(strF,Len(strF)-5)

Docmd.OpenReport "ReportName",,,strF

You can do the same/similar thing to open a Form

Things to remember.
String require quotes
Dates require #
Numbers nothing

"StringFld = '" & TextBoxVal & "'"
"DateFld = #" & TextBoxVal & "#"
"NumberFld = " & TextBoxVal

[Salad]

sparks wrote:

now I see what is up..
teach me to cut and paste
DoCmd.OpenReport stDocName, acPreview, , , ,strF
needed another ,

now it passes any of them..opens the report and request input of
Start_Date and End_Date

I am wondering that since these are dummy variables in the query that
the report is tied to that it can not pass them to the report to then
pass them to the query.

Actually, you have two extra commas.

DOcmd.OpenReport "RptName","view","filter","where condition","arguments"

If you want to preview first...
DoCmd.OpenReport stDocName, acPreview,,strF
would work better.

BTW, use the # around the dates if the table fields are type datetime.

I've never used the "filter" argument in the command line, only the
"where condition"

You can pass an argument to the report that is accessable via the
OpenArgs property. For example...
Docmd.OpenReport "rptname",,,,Me.Name
and in the OnOpen event (or any report event) enter something like
msgbox "the calling form name is " & Me.OpenArgs

>
On Wed, 15 Oct 2008 19:47:25 GMT, sparks <sp****@comcast.netwrote:

>>OK after modifying everything I can think of.
I have sent these 3 strings to the report

DoCmd.OpenReport stDocName, acPreview, , , strF

debug.print strF

Start_date = #11/1/2006# And End_Date = #11/30/2006#
Start_date = 11/1/2006 And End_Date = 11/30/2006
Start_date = "11/1/2006" And End_Date = "11/30/2006"
every one returns type mismatch

I guess I don't understand what is up

On Wed, 15 Oct 2008 09:17:07 -0700, Salad <oi*@vinegar.comwrote:

>>>sparks wrote:
Ok the datepart worked fine on the month entry.

When you say criteria form do you mean a form with something like this

start_date text box
end_date text box

button to open the report.
if you click on the button it will pop up a start_date box for input
then and end_date box for input.

can the start_date and end_date be passed to the report instead

DoCmd.OpenReport stDocName, acPreview it has a where and an open args
that can be passed to the report.

start_date=text1.value
end_date=text2.value
something like that?

have to try that it sounds interesting

On Wed, 15 Oct 2008 06:57:01 -0700, Tom van Stiphout
<to*************@cox.netwrote:

>Criteria form
Yes. Create a form that has the data entry fields for the report.
Docmd.Openform "ReportNameCriteria"

You'd have text boxes for your filter on the form and a command button
to open the report, another to exit. For the Command button to run/open
the report you'd have some code similar to the aircode below.
Dim strF As String
If Not IsNull(Me.TextBoxName1) then
strF = "FldNameToFilter1 = " & Me.TextBoxName1 & " And "
Endif
If Not IsNull(Me.TextBoxName2) then
strF = strF & _
"FieldNameToFilter2 = " & Me.TextBoxName2 & " And "
Endif
....any other additional filtering that is required

'now remove the " And " at the end of the filter string
If strF "" Then strF = Left(strF,Len(strF)-5)

Docmd.OpenReport "ReportName",,,strF

You can do the same/similar thing to open a Form

Things to remember.
String require quotes
Dates require #
Numbers nothing

"StringFld = '" & TextBoxVal & "'"
"DateFld = #" & TextBoxVal & "#"
"NumberFld = " & TextBoxVal

[sparks]

I tried passing the filter and even passed filteron= true on the
openargs...
after removing the criteria of >=[start_date] And <=[end_date] in the
query.
I decied to try it and see what happened so then I tried this

strF = "[date1]>= " & "#" & Me.text1 & "#" & " And [date1]<= " & "#" &
Me.text2 & "#"

DoCmd.OpenReport stDocName, acPreview
Reports![RangeCheck].Filter = strF
Reports![RangeCheck].FilterOn = True

and it worked.
this does not make much since to me but getting it to work was at
least a start

On Wed, 15 Oct 2008 14:42:21 -0700, Salad <oi*@vinegar.comwrote:

>sparks wrote:

>now I see what is up..
teach me to cut and paste
DoCmd.OpenReport stDocName, acPreview, , , ,strF
needed another ,

now it passes any of them..opens the report and request input of
Start_Date and End_Date

I am wondering that since these are dummy variables in the query that
the report is tied to that it can not pass them to the report to then
pass them to the query.

Actually, you have two extra commas.

DOcmd.OpenReport "RptName","view","filter","where condition","arguments"

If you want to preview first...
DoCmd.OpenReport stDocName, acPreview,,strF
would work better.

BTW, use the # around the dates if the table fields are type datetime.

I've never used the "filter" argument in the command line, only the
"where condition"

You can pass an argument to the report that is accessable via the
OpenArgs property. For example...
Docmd.OpenReport "rptname",,,,Me.Name
and in the OnOpen event (or any report event) enter something like
msgbox "the calling form name is " & Me.OpenArgs

>>
On Wed, 15 Oct 2008 19:47:25 GMT, sparks <sp****@comcast.netwrote:

>>>OK after modifying everything I can think of.
I have sent these 3 strings to the report

DoCmd.OpenReport stDocName, acPreview, , , strF

debug.print strF

Start_date = #11/1/2006# And End_Date = #11/30/2006#
Start_date = 11/1/2006 And End_Date = 11/30/2006
Start_date = "11/1/2006" And End_Date = "11/30/2006"
every one returns type mismatch

I guess I don't understand what is up

On Wed, 15 Oct 2008 09:17:07 -0700, Salad <oi*@vinegar.comwrote:
sparks wrote:
>Ok the datepart worked fine on the month entry.
>
>When you say criteria form do you mean a form with something like this
>
>start_date text box
>end_date text box
>
>button to open the report.
>if you click on the button it will pop up a start_date box for input
>then and end_date box for input.
>
>can the start_date and end_date be passed to the report instead
>
>DoCmd.OpenReport stDocName, acPreview it has a where and an open args
>that can be passed to the report.
>
>start_date=text1.value
>end_date=text2.value
>something like that?
>
>have to try that it sounds interesting
>
>
>
>
>
>On Wed, 15 Oct 2008 06:57:01 -0700, Tom van Stiphout
><to*************@cox.netwrote:
>
>
>
>>Criteria form
>
>
Yes. Create a form that has the data entry fields for the report.
Docmd.Openform "ReportNameCriteria"

You'd have text boxes for your filter on the form and a command button
to open the report, another to exit. For the Command button to run/open
the report you'd have some code similar to the aircode below.
Dim strF As String
If Not IsNull(Me.TextBoxName1) then
strF = "FldNameToFilter1 = " & Me.TextBoxName1 & " And "
Endif
If Not IsNull(Me.TextBoxName2) then
strF = strF & _
"FieldNameToFilter2 = " & Me.TextBoxName2 & " And "
Endif
....any other additional filtering that is required

'now remove the " And " at the end of the filter string
If strF "" Then strF = Left(strF,Len(strF)-5)

Docmd.OpenReport "ReportName",,,strF

You can do the same/similar thing to open a Form

Things to remember.
String require quotes
Dates require #
Numbers nothing

"StringFld = '" & TextBoxVal & "'"
"DateFld = #" & TextBoxVal & "#"
"NumberFld = " & TextBoxVal

[Salad]

sparks wrote:

I tried passing the filter and even passed filteron= true on the
openargs...
after removing the criteria of >=[start_date] And <=[end_date] in the
query.
I decied to try it and see what happened so then I tried this

strF = "[date1]>= " & "#" & Me.text1 & "#" & " And [date1]<= " & "#" &
Me.text2 & "#"

DoCmd.OpenReport stDocName, acPreview
Reports![RangeCheck].Filter = strF
Reports![RangeCheck].FilterOn = True

and it worked.
this does not make much since to me but getting it to work was at
least a start

You can filter the reports recordsource or you can pass the filter to
the report

I prefer doing something like this
Select * from orders
as the recordsource for the report and then open the report like
Docmd.OpenReport "RptName",acViewPreview,,"OrderNo = 1"

Let's say the form to call the report is CallRpt. In the query you
could have something like
Select * From Orders Where OrderNo = " & Forms!CallRpt!OrderNo
and it would search/filter onthe value of the textbox OrderNo in the form.

Since you post partial info I can only throw some mud and hope it sticks.

What you should try is entering the Docmd.Report and then step thru the
options when calling a report as you will be prompted for each argument.

>
On Wed, 15 Oct 2008 14:42:21 -0700, Salad <oi*@vinegar.comwrote:

>>sparks wrote:

>>>now I see what is up..
teach me to cut and paste
DoCmd.OpenReport stDocName, acPreview, , , ,strF
needed another ,

now it passes any of them..opens the report and request input of
Start_Date and End_Date

I am wondering that since these are dummy variables in the query that
the report is tied to that it can not pass them to the report to then
pass them to the query.

Actually, you have two extra commas.

DOcmd.OpenReport "RptName","view","filter","where condition","arguments"

If you want to preview first...
DoCmd.OpenReport stDocName, acPreview,,strF
would work better.

BTW, use the # around the dates if the table fields are type datetime.

I've never used the "filter" argument in the command line, only the
"where condition"

You can pass an argument to the report that is accessable via the
OpenArgs property. For example...
Docmd.OpenReport "rptname",,,,Me.Name
and in the OnOpen event (or any report event) enter something like
msgbox "the calling form name is " & Me.OpenArgs

>>>

On Wed, 15 Oct 2008 19:47:25 GMT, sparks <sp****@comcast.netwrote:

OK after modifying everything I can think of.
I have sent these 3 strings to the report

DoCmd.OpenReport stDocName, acPreview, , , strF

debug.print strF

Start_date = #11/1/2006# And End_Date = #11/30/2006#
Start_date = 11/1/2006 And End_Date = 11/30/2006
Start_date = "11/1/2006" And End_Date = "11/30/2006"
every one returns type mismatch

I guess I don't understand what is up

On Wed, 15 Oct 2008 09:17:07 -0700, Salad <oi*@vinegar.comwrote:

>sparks wrote:
>
>
>
>>Ok the datepart worked fine on the month entry.
>>
>>When you say criteria form do you mean a form with something like this
>>
>>start_date text box
>>end_date text box
>>
>>button to open the report.
>>if you click on the button it will pop up a start_date box for input
>>then and end_date box for input.
>>
>>can the start_date and end_date be passed to the report instead
>>
>>DoCmd.OpenReport stDocName, acPreview it has a where and an open args
>>that can be passed to the report.
>>
>>start_date=text1.value
>>end_date=text2.value
>>something like that?
>>
>>have to try that it sounds interesting
>>
>>
>>
>>
>>
>>On Wed, 15 Oct 2008 06:57:01 -0700, Tom van Stiphout
>><to*************@cox.netwrote:
>>
>>
>>
>>
>>>Criteria form
>>
>>
>Yes. Create a form that has the data entry fields for the report.
> Docmd.Openform "ReportNameCriteria"
>
>You'd have text boxes for your filter on the form and a command button
>to open the report, another to exit. For the Command button to run/open
>the report you'd have some code similar to the aircode below.
> Dim strF As String
> If Not IsNull(Me.TextBoxName1) then
> strF = "FldNameToFilter1 = " & Me.TextBoxName1 & " And "
> Endif
> If Not IsNull(Me.TextBoxName2) then
> strF = strF & _
"FieldNameToFilter2 = " & Me.TextBoxName2 & " And "
> Endif
> ....any other additional filtering that is required
>
> 'now remove the " And " at the end of the filter string
> If strF "" Then strF = Left(strF,Len(strF)-5)
>
> Docmd.OpenReport "ReportName",,,strF
>
>You can do the same/similar thing to open a Form
>
>Things to remember.
> String require quotes
> Dates require #
> Numbers nothing
>
> "StringFld = '" & TextBoxVal & "'"
> "DateFld = #" & TextBoxVal & "#"
> "NumberFld = " & TextBoxVal