Report capturing data from 'red flagged' field

[Norma]

I am trying to capture data in a date parameter query that spans from
1 to 3 months. I have a field that is a checkbox that notes whether or
not this data is 'red flagged' (so you know, the red flagged data is
end of the month production that is reported in the following months
report)
I need to capture the red flagged production from the previous month
and the production from the entered month(s) not including the red
flagged production for the last month that was entered in the
parameter. Does anyone have any ideas on how to capture this?

Any help would be greatly appreciated

Thanks in advance,
Norma
njhildebrand@su scom.net

[Salad]

Re: Report capturing data from 'red flagged' field

Norma wrote:
[color=blue]
> I am trying to capture data in a date parameter query that spans from
> 1 to 3 months. I have a field that is a checkbox that notes whether or
> not this data is 'red flagged' (so you know, the red flagged data is
> end of the month production that is reported in the following months
> report)
> I need to capture the red flagged production from the previous month
> and the production from the entered month(s) not including the red
> flagged production for the last month that was entered in the
> parameter. Does anyone have any ideas on how to capture this?
>
> Any help would be greatly appreciated
>
> Thanks in advance,
> Norma
> njhildebrand@su scom.net[/color]
Let's say the table is called Table1 and the field to compare is called
DateField. The primary key is ID, and you have a field called RedFlagged.

Create 2 queries. Select Table1. Drag the ID and RedFlagged fields to
the first 2 columns. In the criteria row for RedFlagged, enter True.
Move to the blank 3rd column and in the Field rowenter the following
Expr1:Month(Dat eField)
and in the criteria row enter
Month(DateAdd(" m",-1,Date())

This will select all redflagged records from the prior month. Since
this will pull up red flagged records from prior years, you might want
to simply exclude this third field and in the criteria row under
DateField enter
Between
DateSerial(Year (dateadd("m",-1,DateField)),M onth(dateadd("m ",-1,DateField)),1 )
And DateField - Day(DateField)

This will select all records from the first to last day of the prior month.

Save this query as Q1.

Now click on Query tab, select New/FindUnmatched wizard

Select Table1 and Q1, set the link between ID field and follow the
wizard steps. You will need to set a criteria to filter within a date
range. You enter enter under date field
Between [ENter From Date] And [Enter To Date]

[Norma]

Re: Report capturing data from 'red flagged' field

I am getting an error message. It says that I have too many
Parenthesis. It does not like the ending of the statement:
Between
DateSerial(Year (dateadd("m",-1,DateField)),M onth(dateadd("m ",-1,DateField)),1 )
And DateField - Day(DateField)
It highlights the next to the last parenthesis. Any ideas??
And thanks so much for your help so far..
Norma

Salad <oil@vinegar.co m> wrote in message news:<hLqec.653 4$A_4.3246@news read1.news.pas. earthlink.net>. ..[color=blue]
> Norma wrote:
>[color=green]
> > I am trying to capture data in a date parameter query that spans from
> > 1 to 3 months. I have a field that is a checkbox that notes whether or
> > not this data is 'red flagged' (so you know, the red flagged data is
> > end of the month production that is reported in the following months
> > report)
> > I need to capture the red flagged production from the previous month
> > and the production from the entered month(s) not including the red
> > flagged production for the last month that was entered in the
> > parameter. Does anyone have any ideas on how to capture this?
> >
> > Any help would be greatly appreciated
> >
> > Thanks in advance,
> > Norma
> > njhildebrand@su scom.net[/color]
> Let's say the table is called Table1 and the field to compare is called
> DateField. The primary key is ID, and you have a field called RedFlagged.
>
> Create 2 queries. Select Table1. Drag the ID and RedFlagged fields to
> the first 2 columns. In the criteria row for RedFlagged, enter True.
> Move to the blank 3rd column and in the Field rowenter the following
> Expr1:Month(Dat eField)
> and in the criteria row enter
> Month(DateAdd(" m",-1,Date())
>
> This will select all redflagged records from the prior month. Since
> this will pull up red flagged records from prior years, you might want
> to simply exclude this third field and in the criteria row under
> DateField enter
> Between
> DateSerial(Year (dateadd("m",-1,DateField)),M onth(dateadd("m ",-1,DateField)),1 )
> And DateField - Day(DateField)
>
> This will select all records from the first to last day of the prior month.
>
> Save this query as Q1.
>
> Now click on Query tab, select New/FindUnmatched wizard
>
> Select Table1 and Q1, set the link between ID field and follow the
> wizard steps. You will need to set a criteria to filter within a date
> range. You enter enter under date field
> Between [ENter From Date] And [Enter To Date][/color]

[Salad]

Re: Report capturing data from 'red flagged' field

Norma wrote:
[color=blue]
> I am getting an error message. It says that I have too many
> Parenthesis. It does not like the ending of the statement:
> Between
> DateSerial(Year (dateadd("m",-1,DateField)),M onth(dateadd("m ",-1,DateField)),1 )
> And DateField - Day(DateField)
> It highlights the next to the last parenthesis. Any ideas??
> And thanks so much for your help so far..
> Norma
>[/color]

Brain Freeze.

Please change
DateField
to
Date()
with the parenthesis.

Here is my SQL Statement where I want to select all records from the
prior month that have been redflagged. The table/query field names are
ID, DateField, and RedFlagged.

SELECT ID
FROM Table1
WHERE DateField Between
DateSerial(Year (DateAdd("m",-1,Date())),Mont h(DateAdd("m",-1,Date())),1)
And Date()-Day(Date()) AND RedFlagged = True;