Delete query date field pulling more than one month

[Bill Fischer via AccessMonster.com]

In a nutshell here is what I'm trying to do:

I run an append query to update a table with current month's totals, works
great and also updates the prior month until the 4th day of current month. I
need a delete query to remove the updated record for the current month and
prior month.

I have a been working off of the date field stamp in the table that is in
place when the append query runs with a Dmin expression. Also works great,
until prior month is updated after the 1st of the current month and there is
two records to be removed.

DELETE DISTINCTROW tblMtdYtdAllRolling.MtdYtdID, tblMtdYtdAllRolling.*
FROM tblMtdYtdAllRolling INNER JOIN qselDupltblMtdYtdAllRolling ON
tblMtdYtdAllRolling.MtdYtdID = qselDupltblMtdYtdAllRolling.MtdYtdID

WHERE (((tblMtdYtdAllRolling.MtdYtdID)=DMin("MtdYtdID","tblMtdYtdAllRolling",
" IIf(Date()>(DateSerial(Year(Date()),Month(Date()),4)), ([DateEntered]

=DateSerial(Year(Date()),Month(Date()),1)), (([DateEntered]>=DateSerial(Year

(Date()),Month(Date()),1)) Or ([DateEntered]>=DateSerial(Year(Date()),Month
(Date())-1,1))))
")));

As you see from the INNER JOIN the qselDupltblMtdYtdAllRolling checks for
duplicate values so just one record will not be removed if query is ran
independent (shouldn't happen but good to build in a catch net I thought).

Field Data Type Data example 1
Data example 2

AYear number 2006
2006
AMonth number 2
3
SumofInvoice currrency $40,620.75
$22,000.00
MtdYtdID PK autonumber 104
105
DateEntered Date/Time 3/1/2006 2:14:51 PM
3/1/2006 2:14:51 PM

I also have two fields in the table with year and month that will not change
on updating the table. I have been trying to tie in the month field into the
where statement to, if needed, beyond the date/time field for 2 reasons 1.
being as above stated, prior month maybe updated with current month datestamp.
2. when appended both prior and current month will have the same date stamp.
With those conditions my DMin expression will always obviously select only
the one oldest record.

I have trying to AMonth field to equal the SerialDate Month Criteria but of
course the reason I'm posting I have been unsucessful. Is there another way
to do this? Hopefully I am clear on the intentions of this process.

Also thougth of using the PK autonumber but this is a replicated database.

Very much appreciate any help (A day and a half of trying and kicking myself)

Bill Fischer

 

[Rob Oldfield]

Think you're getting a bit too much into the detail of a method which isn't
working. Probably worth reposting just with what it is that you actually
want to achieve (i.e. I can't figure that out from what you've said so far.)

In a nutshell here is what I'm trying to do:

I run an append query to update a table with current month's totals, works
great and also updates the prior month until the 4th day of current month. I
need a delete query to remove the updated record for the current month and
prior month.

I have a been working off of the date field stamp in the table that is in
place when the append query runs with a Dmin expression. Also works great,
until prior month is updated after the 1st of the current month and there is
two records to be removed.

DELETE DISTINCTROW tblMtdYtdAllRolling.MtdYtdID, tblMtdYtdAllRolling.*
FROM tblMtdYtdAllRolling INNER JOIN qselDupltblMtdYtdAllRolling ON
tblMtdYtdAllRolling.MtdYtdID = qselDupltblMtdYtdAllRolling.MtdYtdID

WHERE (((tblMtdYtdAllRolling.MtdYtdID)=DMin("MtdYtdID","tblMtdYtdAllRolling",
" IIf(Date()>(DateSerial(Year(Date()),Month(Date()),4)), ([DateEntered]

=DateSerial(Year(Date()),Month(Date()),1)),

(([DateEntered]>=DateSerial(Year
(Date()),Month(Date()),1)) Or ([DateEntered]>=DateSerial(Year(Date()),Month
(Date())-1,1))))
")));

As you see from the INNER JOIN the qselDupltblMtdYtdAllRolling checks for
duplicate values so just one record will not be removed if query is ran
independent (shouldn't happen but good to build in a catch net I thought).

Field Data Type Data example 1
Data example 2

AYear number 2006
2006
AMonth number 2
3
SumofInvoice currrency $40,620.75
$22,000.00
MtdYtdID PK autonumber 104
105
DateEntered Date/Time 3/1/2006 2:14:51 PM
3/1/2006 2:14:51 PM

I also have two fields in the table with year and month that will not change
on updating the table. I have been trying to tie in the month field into the
where statement to, if needed, beyond the date/time field for 2 reasons 1.
being as above stated, prior month maybe updated with current month datestamp.
2. when appended both prior and current month will have the same date stamp.
With those conditions my DMin expression will always obviously select only
the one oldest record.

I have trying to AMonth field to equal the SerialDate Month Criteria but of
course the reason I'm posting I have been unsucessful. Is there another way
to do this? Hopefully I am clear on the intentions of this process.

Also thougth of using the PK autonumber but this is a replicated database.

Very much appreciate any help (A day and a half of trying and kicking myself)

Bill Fischer

 

[Bill Fischer via AccessMonster.com]

Rob,

As I look back at my post (written at a frustrating point in time) you have
an excellent reply.

Here goes:

I have a DB that one function tracks daily invoices into 5 monthly total
categories via a total queries. Invoice table is linked to a business
Information table by PK. I would leave all alone but, we need to have the
history of these totals and unfortunately a business can change into a
different catergory sometime into the future which will of course will affect
the totals history as the database is set up now.

What I have done is create a table (just the one for overall totals for now
as a test bed) from the total query, turned that query into append query to
only append previous months totals and current months totals (if current
month date is less than the 5th of the that month then both months update if
not only current months totals update).

All above works well, what I need to do is take the duplicate months out of
the table via a delete query. And sucessfully have done with WHERE statement
previously posted until the append datestamp for the previous month is
actually in the current month (up to the 4th of that month). Fields other
the business data are a PK-(autonumber), date stamp, month(numeric), year.

I played around with an update query but wasn't able to get very far with it.
Maybe I need to revisit that possiblity.

Hopefully this gives you some insight to what I'm looking to try to
accomplish.

Thanks for the reply,

Bill

Think you're getting a bit too much into the detail of a method which isn't
working. Probably worth reposting just with what it is that you actually
want to achieve (i.e. I can't figure that out from what you've said so far.)

In a nutshell here is what I'm trying to do:

[quoted text clipped - 54 lines]

Bill Fischer

 

[Rob Oldfield]

I'm not sure I'm quite with you yet. Are you saying that you just want to
delete records that are a) duplications of previous records, and b) where
their date is in the first four days of the month?

Rob,

As I look back at my post (written at a frustrating point in time) you have
an excellent reply.

Here goes:

I have a DB that one function tracks daily invoices into 5 monthly total
categories via a total queries. Invoice table is linked to a business
Information table by PK. I would leave all alone but, we need to have the
history of these totals and unfortunately a business can change into a
different catergory sometime into the future which will of course will affect
the totals history as the database is set up now.

What I have done is create a table (just the one for overall totals for now
as a test bed) from the total query, turned that query into append query to
only append previous months totals and current months totals (if current
month date is less than the 5th of the that month then both months update if
not only current months totals update).

All above works well, what I need to do is take the duplicate months out of
the table via a delete query. And sucessfully have done with WHERE statement
previously posted until the append datestamp for the previous month is
actually in the current month (up to the 4th of that month). Fields other
the business data are a PK-(autonumber), date stamp, month(numeric), year.

I played around with an update query but wasn't able to get very far with it.
Maybe I need to revisit that possiblity.

Hopefully this gives you some insight to what I'm looking to try to
accomplish.

Thanks for the reply,

Bill

Think you're getting a bit too much into the detail of a method which isn't
working. Probably worth reposting just with what it is that you actually
want to achieve (i.e. I can't figure that out from what you've said so far.)

In a nutshell here is what I'm trying to do:

[quoted text clipped - 54 lines]

Bill Fischer

 

[Bill Fischer via AccessMonster.com]

Rob,

Now that you say it like that, do I feel like a 1st grader. A) is true, b)
no, but rethinking this from a duplicate stand point. The append query will
at most add 2 new records, which if I use my duplicate query on the table
(inner join to the delete query), will show at most 4 records and may only be
2 records. Which will be 2 each of a month with a date/time stamp different
for each record, from that is there a way to filter down the 4 records down
to 2 by earliest date of each month to delete in one step. Or a way around
would possibly always run the delete query twice.

Here is some sample date from the dupicate query.

Year Month DateEntered ID
SaleTotal

2006 Jan 3/1/2006 1:14:51 PM 128 $40,620.75
2006 Jan 3/2/2006 3:12:59 PM 130 $48,825.31
2006 Feb 3/1/2006 1:14:51 PM 129 $18,545.23
2006 Feb 3/1/2006 3:12:59 PM 131 $22,938.15

Thanks Rob you have already lead me in the right direction, sometimes a
person thinks in circles and digs a large hole for himself.

I'm not sure I'm quite with you yet. Are you saying that you just want to
delete records that are a) duplications of previous records, and b) where
their date is in the first four days of the month?

Rob,

[quoted text clipped - 41 lines]

 

[Rob Oldfield]

Still not 100% certain what you're after. With your sample data, you want
to leave the latest Jan record, and the latest Feb as well?

Rob,

Now that you say it like that, do I feel like a 1st grader. A) is true, b)
no, but rethinking this from a duplicate stand point. The append query will
at most add 2 new records, which if I use my duplicate query on the table
(inner join to the delete query), will show at most 4 records and may only be
2 records. Which will be 2 each of a month with a date/time stamp different
for each record, from that is there a way to filter down the 4 records down
to 2 by earliest date of each month to delete in one step. Or a way around
would possibly always run the delete query twice.

Here is some sample date from the dupicate query.

Year Month DateEntered ID
SaleTotal

2006 Jan 3/1/2006 1:14:51 PM 128 $40,620.75
2006 Jan 3/2/2006 3:12:59 PM 130 $48,825.31
2006 Feb 3/1/2006 1:14:51 PM 129 $18,545.23
2006 Feb 3/1/2006 3:12:59 PM 131 $22,938.15

Thanks Rob you have already lead me in the right direction, sometimes a
person thinks in circles and digs a large hole for himself.

I'm not sure I'm quite with you yet. Are you saying that you just want to
delete records that are a) duplications of previous records, and b) where
their date is in the first four days of the month?

Rob,

[quoted text clipped - 41 lines]

Bill Fischer

 

[Bill Fischer via AccessMonster.com]

Exactly, I have to test some more, but this:

WHERE DMin("DateEntered","tblMtdYtdAllRolling","[DateEntered]>=DateSerial
(Year(Date()),Month(Date()),1)")

Seems to be working, althought I don't think the criteria needs to be this
elborate.

Thanks again Rob!!!

Still not 100% certain what you're after. With your sample data, you want
to leave the latest Jan record, and the latest Feb as well?

Rob,

[quoted text clipped - 29 lines]

 

[Rob Oldfield]

I don't think there's going to be a way of doing it without getting a bit
elaborate. Nothing wrong with that though.

I *think* I now have a handle on what you're trying to do, and even if I
don't then hopefully what I'm about to suggest will give you a general
method that you can fine tune anyway.

From your posted set of sample data (which I'm assuming is called
tblMtdYtdAllRolling) I'd do something like this:

Query Q1 - will come up with the earliest date stamp for each month/year.
SELECT tblMtdYtdAllRolling.Year, tblMtdYtdAllRolling.Month,
Min(tblMtdYtdAllRolling.DateEntered) AS MinOfDateEntered
FROM tblMtdYtdAllRolling
GROUP BY tblMtdYtdAllRolling.Year, tblMtdYtdAllRolling.Month;

Query Q2 - links that data back to tblMdtYtdAllRolling to produce the
related IDs.
SELECT tblMtdYtdAllRolling.ID
FROM Q1 INNER JOIN tblMtdYtdAllRolling ON (Q1.MinOfDateEntered =
tblMtdYtdAllRolling.DateEntered) AND (Q1.Month = tblMtdYtdAllRolling.Month)
AND (Q1.Year = tblMtdYtdAllRolling.Year);

Query Q3 - just deletes those IDs
DELETE tblMtdYtdAllRolling.ID
FROM tblMtdYtdAllRolling
WHERE (((tblMtdYtdAllRolling.ID) In (select * from Q2)));

As I say, you might need to do some tuning on that. A couple of things that
might be necessary:

Limit records to delete to only those with a data stamp before the 5th. You
can do that kind of thing by including an additional calculated field onto
Q1 Day([DateEntered]) and applying a criteria (e.g. <5) to that.

This general technique can work in two different ways: either by coming up
with a list of IDs that you want to delete (as in the example above), or a
list of IDs that you want to keep (in which case the criteria on Q3 would be
'Not In' instead of just 'In'.

The last delete query (Q3 above) obviously needs to be applied to an
updatable source. It may be that you work out the relavent IDs to either
delete or keep from a non-updatable query, but then use the base table to
delete from in Q3.

How does that look?

Exactly, I have to test some more, but this:

WHERE DMin("DateEntered","tblMtdYtdAllRolling","[DateEntered]>=DateSerial
(Year(Date()),Month(Date()),1)")

Seems to be working, althought I don't think the criteria needs to be this
elborate.

Thanks again Rob!!!

Still not 100% certain what you're after. With your sample data, you want
to leave the latest Jan record, and the latest Feb as well?

Rob,

[quoted text clipped - 29 lines]

Bill Fischer

 

[Bill Fischer via AccessMonster.com]

Rob I really appreciate the time you have put into this headache of mine.

I think you have the jist of what I am doing, only these records are updated
at least daily throughout the month which has lead into issues of the 1st of
any given month and also the 5th of the month. All that being said I can't
believe someone(you) has been able to follow my my posts and has had the
determination to "stick with it". A huge thanks!

I hope you can help me with this IIf syntax for one query. I can't seem to
get it to go and maybe the issue is theTrue/False DMin isn't something access
can perform. Anyway If the date is between the last day of prior month and
the 6th of the current month (ie. >2/28/2006 and <3/6/2006) I need this to
run:

DMin("DateEntered","tblMtdYtdAllRolling","[AMonth]>(Month(Date())-2) AND
[AYear]=Year(Date())")

If not than this:

DMin("DateEntered","tblMtdYtdAllRolling","[AMonth]=(Month(Date())) AND [AYear]
=Year(Date())")

Both of these have been tested and work perfect for my purposes, just trying
to do all in one query, this is what I have tried and also in different
variations:

IIf ("DateSerial(Year(Date()),Month(Date()),6)>(Date())>DateSerial(Year(Date()
),Month(Date()),0)","DMin("DateEntered","tblMtdYtdAllRolling","[AMonth]>
(Month(Date())-2) AND [AYear]=Year(Date())")","DMin("DateEntered",
"tblMtdYtdAllRolling","[AMonth]=(Month(Date())) AND [AYear]=Year(Date())")")

Whether I can use two queries (a duplicate and delete query) or end of using
three (to split the IIf function above and use VBA to run the statement to
cycle the queries) I'll post my final query SQL just for reference so you can
see how I ended up (with your help) getting this to work.

Again HUGE thanks Rob.

Bill

I don't think there's going to be a way of doing it without getting a bit
elaborate. Nothing wrong with that though.

I *think* I now have a handle on what you're trying to do, and even if I
don't then hopefully what I'm about to suggest will give you a general
method that you can fine tune anyway.

From your posted set of sample data (which I'm assuming is called
tblMtdYtdAllRolling) I'd do something like this:

Query Q1 - will come up with the earliest date stamp for each month/year.
SELECT tblMtdYtdAllRolling.Year, tblMtdYtdAllRolling.Month,
Min(tblMtdYtdAllRolling.DateEntered) AS MinOfDateEntered
FROM tblMtdYtdAllRolling
GROUP BY tblMtdYtdAllRolling.Year, tblMtdYtdAllRolling.Month;

Query Q2 - links that data back to tblMdtYtdAllRolling to produce the
related IDs.
SELECT tblMtdYtdAllRolling.ID
FROM Q1 INNER JOIN tblMtdYtdAllRolling ON (Q1.MinOfDateEntered =
tblMtdYtdAllRolling.DateEntered) AND (Q1.Month = tblMtdYtdAllRolling.Month)
AND (Q1.Year = tblMtdYtdAllRolling.Year);

Query Q3 - just deletes those IDs
DELETE tblMtdYtdAllRolling.ID
FROM tblMtdYtdAllRolling
WHERE (((tblMtdYtdAllRolling.ID) In (select * from Q2)));

As I say, you might need to do some tuning on that. A couple of things that
might be necessary:

Limit records to delete to only those with a data stamp before the 5th. You
can do that kind of thing by including an additional calculated field onto
Q1 Day([DateEntered]) and applying a criteria (e.g. <5) to that.

This general technique can work in two different ways: either by coming up
with a list of IDs that you want to delete (as in the example above), or a
list of IDs that you want to keep (in which case the criteria on Q3 would be
'Not In' instead of just 'In'.

The last delete query (Q3 above) obviously needs to be applied to an
updatable source. It may be that you work out the relavent IDs to either
delete or keep from a non-updatable query, but then use the base table to
delete from in Q3.

How does that look?

Exactly, I have to test some more, but this:

[quoted text clipped - 14 lines]

 

[Bill Fischer via AccessMonster.com]

Rob,

I kept working on this IIF statement, here is what I came up with:

IIf((Date())>DateSerial(Year(Date()),Month(Date()),0) And (Date())<DateSerial
(Year(Date()),Month(Date()),6),(DMin("DateEntered","tblMtdYtdAllRolling","
[AMonth]>(Month(Date())-2) AND [AYear]=Year(Date())")),(DMin("DateEntered",
"tblMtdYtdAllRolling","[AMonth]=(Month(Date())) AND [AYear]=Year(Date())")))

Works perfectly, deleting correct dated record at any given time during a
month.

Here is the duplicate query sql, which is only used to make an absolute of a
duplicate for any given month,

SELECT tblMtdYtdAllRolling.MonthYear, tblMtdYtdAllRolling.AYear,
tblMtdYtdAllRolling.AMonth, tblMtdYtdAllRolling.MtdYtdID, tblMtdYtdAllRolling.
DateEntered
FROM tblMtdYtdAllRolling
WHERE (((tblMtdYtdAllRolling.AYear) In (SELECT [AYear] FROM
[tblMtdYtdAllRolling] As Tmp GROUP BY [AYear],[AMonth] HAVING Count(*)>1 And
[AMonth] = [tblMtdYtdAllRolling].[AMonth])))
ORDER BY tblMtdYtdAllRolling.DateEntered;

And the big one, the delete query sql,

DELETE DISTINCTROW tblMtdYtdAllRolling.DateEntered, tblMtdYtdAllRolling.*
FROM tblMtdYtdAllRolling INNER JOIN qselDupltblMtdYtdAllRolling ON
tblMtdYtdAllRolling.MtdYtdID = qselDupltblMtdYtdAllRolling.MtdYtdID
WHERE (((tblMtdYtdAllRolling.DateEntered)=IIf((Date())>DateSerial(Year(Date())
,Month(Date()),0) And (Date())<DateSerial(Year(Date()),Month(Date()),6),(DMin
("DateEntered","tblMtdYtdAllRolling","[AMonth]>(Month(Date())-2) AND [AYear]
=Year(Date())")),(DMin("DateEntered","tblMtdYtdAllRolling","[AMonth]=(Month
(Date())) AND [AYear]=Year(Date())")))));


Thank you very much for taking the time to post and repost to gain an idea of
what I was after, with your thoughtful questions you definitely lead me down
the path of "winter gold" (Love watching the Olympics). Without your help
and this forum (I received help on the append query also being date driven
query) to gain a bunch of know how I would have struggled considerably
longer and may never have reached an end.

Have a great week!

Type to you later,

Bill

Rob I really appreciate the time you have put into this headache of mine.

I think you have the jist of what I am doing, only these records are updated
at least daily throughout the month which has lead into issues of the 1st of
any given month and also the 5th of the month. All that being said I can't
believe someone(you) has been able to follow my my posts and has had the
determination to "stick with it". A huge thanks!

I hope you can help me with this IIf syntax for one query. I can't seem to
get it to go and maybe the issue is theTrue/False DMin isn't something access
can perform. Anyway If the date is between the last day of prior month and
the 6th of the current month (ie. >2/28/2006 and <3/6/2006) I need this to
run:

DMin("DateEntered","tblMtdYtdAllRolling","[AMonth]>(Month(Date())-2) AND
[AYear]=Year(Date())")

If not than this:

DMin("DateEntered","tblMtdYtdAllRolling","[AMonth]=(Month(Date())) AND [AYear]
=Year(Date())")

Both of these have been tested and work perfect for my purposes, just trying
to do all in one query, this is what I have tried and also in different
variations:

IIf ("DateSerial(Year(Date()),Month(Date()),6)>(Date())>DateSerial(Year(Date()
),Month(Date()),0)","DMin("DateEntered","tblMtdYtdAllRolling","[AMonth]>
(Month(Date())-2) AND [AYear]=Year(Date())")","DMin("DateEntered",
"tblMtdYtdAllRolling","[AMonth]=(Month(Date())) AND [AYear]=Year(Date())")")

Whether I can use two queries (a duplicate and delete query) or end of using
three (to split the IIf function above and use VBA to run the statement to
cycle the queries) I'll post my final query SQL just for reference so you can
see how I ended up (with your help) getting this to work.

Again HUGE thanks Rob.

Bill

I don't think there's going to be a way of doing it without getting a bit
elaborate. Nothing wrong with that though.

[quoted text clipped - 48 lines]

 

[Rob Oldfield]

Excellent. Glad you got it all working and happy to help out.

One thing that you might want to think about the next time you run into a
situation like this would be to do it via code. The way that that would
work would be something like looping through the records in
tblMtdYtdAllRolling and then checking for a particular criteria that allows
you to decide whether to delete or not (or take some other action with
them - archiving for example). In general terms, doing this kind of thing
purely through queries is going to be more efficient than that (i.e. the Jet
database engire is better than VBA) but sometimes the logic just gets too
convoluted.

Rob,

I kept working on this IIF statement, here is what I came up with:

IIf((Date())>DateSerial(Year(Date()),Month(Date()),0) And (Date())<DateSerial
(Year(Date()),Month(Date()),6),(DMin("DateEntered","tblMtdYtdAllRolling","
[AMonth]>(Month(Date())-2) AND [AYear]=Year(Date())")),(DMin("DateEntered",
"tblMtdYtdAllRolling","[AMonth]=(Month(Date())) AND [AYear]=Year(Date())")))

Works perfectly, deleting correct dated record at any given time during a
month.

Here is the duplicate query sql, which is only used to make an absolute of a
duplicate for any given month,

SELECT tblMtdYtdAllRolling.MonthYear, tblMtdYtdAllRolling.AYear,
tblMtdYtdAllRolling.AMonth, tblMtdYtdAllRolling.MtdYtdID, tblMtdYtdAllRolling.
DateEntered
FROM tblMtdYtdAllRolling
WHERE (((tblMtdYtdAllRolling.AYear) In (SELECT [AYear] FROM
[tblMtdYtdAllRolling] As Tmp GROUP BY [AYear],[AMonth] HAVING Count(*)>1 And
[AMonth] = [tblMtdYtdAllRolling].[AMonth])))
ORDER BY tblMtdYtdAllRolling.DateEntered;

And the big one, the delete query sql,

DELETE DISTINCTROW tblMtdYtdAllRolling.DateEntered, tblMtdYtdAllRolling.*
FROM tblMtdYtdAllRolling INNER JOIN qselDupltblMtdYtdAllRolling ON
tblMtdYtdAllRolling.MtdYtdID = qselDupltblMtdYtdAllRolling.MtdYtdID
WHERE (((tblMtdYtdAllRolling.DateEntered)=IIf((Date())>DateSerial(Year(Date())
,Month(Date()),0) And (Date())<DateSerial(Year(Date()),Month(Date()),6),(DMin
("DateEntered","tblMtdYtdAllRolling","[AMonth]>(Month(Date())-2) AND [AYear]=Year(Date())")),(DMin("DateEntered","tblMtdYtdAllRolling","[AMonth]=(Month
(Date())) AND [AYear]=Year(Date())")))));


Thank you very much for taking the time to post and repost to gain an idea of
what I was after, with your thoughtful questions you definitely lead me down
the path of "winter gold" (Love watching the Olympics). Without your help
and this forum (I received help on the append query also being date driven
query) to gain a bunch of know how I would have struggled considerably
longer and may never have reached an end.

Have a great week!

Type to you later,

Bill

Rob I really appreciate the time you have put into this headache of mine.

I think you have the jist of what I am doing, only these records are updated
at least daily throughout the month which has lead into issues of the 1st of
any given month and also the 5th of the month. All that being said I can't
believe someone(you) has been able to follow my my posts and has had the
determination to "stick with it". A huge thanks!

I hope you can help me with this IIf syntax for one query. I can't seem to
get it to go and maybe the issue is theTrue/False DMin isn't something access
can perform. Anyway If the date is between the last day of prior month and
the 6th of the current month (ie. >2/28/2006 and <3/6/2006) I need this to
run:

DMin("DateEntered","tblMtdYtdAllRolling","[AMonth]>(Month(Date())-2) AND
[AYear]=Year(Date())")

If not than this:

DMin("DateEntered","tblMtdYtdAllRolling","[AMonth]=(Month(Date())) AND [AYear]
=Year(Date())")

Both of these have been tested and work perfect for my purposes, just trying
to do all in one query, this is what I have tried and also in different
variations:

IIf ("DateSerial(Year(Date()),Month(Date()),6)>(Date())>DateSerial(Year(Date()
),Month(Date()),0)","DMin("DateEntered","tblMtdYtdAllRolling","[AMonth]>
(Month(Date())-2) AND [AYear]=Year(Date())")","DMin("DateEntered",
"tblMtdYtdAllRolling","[AMonth]=(Month(Date())) AND [AYear]=Year(Date())")")

Whether I can use two queries (a duplicate and delete query) or end of using
three (to split the IIf function above and use VBA to run the statement to
cycle the queries) I'll post my final query SQL just for reference so you can
see how I ended up (with your help) getting this to work.

Again HUGE thanks Rob.

Bill

I don't think there's going to be a way of doing it without getting a bit
elaborate. Nothing wrong with that though.

[quoted text clipped - 48 lines]

Bill Fischer