By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

Report capturing data from 'red flagged' field

P: n/a
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
nj**********@suscom.net
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Norma wrote:
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
nj**********@suscom.net

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(DateField)
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)),Month(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]


Nov 12 '05 #2

P: n/a
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)),Month(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 <oi*@vinegar.com> wrote in message news:<hL*****************@newsread1.news.pas.earth link.net>...
Norma wrote:
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
nj**********@suscom.net

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(DateField)
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)),Month(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]

Nov 12 '05 #3

P: n/a
Norma wrote:
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)),Month(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


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())),Month(DateAdd("m",-1,Date())),1)
And Date()-Day(Date()) AND RedFlagged = True;

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.