*REPOST* Select Records based on 2 dates

[Thorson]

Someone helped me out with a problem I had in the past to select records for
the month and up until the 5th of the next month. The criteria I have set up
currently selects records between the 5th of the previous month and the 5th
of the next month, I will probably change it to select between the 1st of the
previous month and the 5th of the next month. This is what I currently have:

Between
DateSerial([Forms]![frmCurrentInventoryDateQuery]![cboYear],[Forms]![frmCurrentInventoryDateQuery]![cboMonth],5)
And
DateAdd("m",1,DateSerial([Forms]![frmCurrentInventoryDateQuery]![cboYear],[Forms]![frmCurrentInventoryDateQuery]![cboMonth],5))

However I would like to change it a little and I can't figure out how, I'm
thinking that I made need some sort of IIf statement in the Criteria line,
but I don't know if that is even possible, I am new to this.

I want the criteria to continue to pull the records up until the 5th of the
next month (which is how it is currently setup). However, if the "DispDate"
(which is the date of the actual record) is in the first 5 days of the next
month and was also entered during the first 5 days of the next month (so the
"EntryDate" is in the first 5 days), it will come up on the previous report,
I don't want this.

Is there anyway to get the Criteria to do what it is doing now, but check to
see if the Disposition Date is the same month as the entry date and therefore
pull that record during the actual month it took place instead of the month
before (this is only a problem during the first 5 days of the month).

My other option is to have all users wait to enter records until the 6th of
the month, but this is not very convenient.

Let me know if you need more info...
Thanks!

 

[Clifford Bass]

Hi Thorson,

You could include the SQL version of your text criteria:

select ....
from .....
where .... and Month([Disposition Date] = Month([Entry Date])

In the query designer, you would enter the Month([Disposition Date]) in
the top row and the Month([Entry Date]) in the criteria row. Now this will
only work if the time between entry and disposition is short. If it can be
over a year, you would need to add:

..... and Year([Disposition Date]) = Year([Entry Date])

Hope this helps,

Clifford Bass

 

[Thorson]

That will work, but I think then what I would have to do is have 2 separate
queries, one set up the way I originally had and then one set up the way you
suggested and then do a union query to put them together.

Is there anyway to put this all in one query? To pull records that
happened in June but were entered up until the fifth of July but then also if
the record was entered even in the first days of June and it happened in June
that comes up too.

--
Thorson

Hi Thorson,

You could include the SQL version of your text criteria:

select ....
from .....
where .... and Month([Disposition Date] = Month([Entry Date])

In the query designer, you would enter the Month([Disposition Date]) in
the top row and the Month([Entry Date]) in the criteria row. Now this will
only work if the time between entry and disposition is short. If it can be
over a year, you would need to add:

.... and Year([Disposition Date]) = Year([Entry Date])

Hope this helps,

Clifford Bass

Someone helped me out with a problem I had in the past to select records for
the month and up until the 5th of the next month. The criteria I have set up
currently selects records between the 5th of the previous month and the 5th
of the next month, I will probably change it to select between the 1st of the
previous month and the 5th of the next month. This is what I currently have:

Between
DateSerial([Forms]![frmCurrentInventoryDateQuery]![cboYear],[Forms]![frmCurrentInventoryDateQuery]![cboMonth],5)
And
DateAdd("m",1,DateSerial([Forms]![frmCurrentInventoryDateQuery]![cboYear],[Forms]![frmCurrentInventoryDateQuery]![cboMonth],5))

However I would like to change it a little and I can't figure out how, I'm
thinking that I made need some sort of IIf statement in the Criteria line,
but I don't know if that is even possible, I am new to this.

I want the criteria to continue to pull the records up until the 5th of the
next month (which is how it is currently setup). However, if the "DispDate"
(which is the date of the actual record) is in the first 5 days of the next
month and was also entered during the first 5 days of the next month (so the
"EntryDate" is in the first 5 days), it will come up on the previous report,
I don't want this.

Is there anyway to get the Criteria to do what it is doing now, but check to
see if the Disposition Date is the same month as the entry date and therefore
pull that record during the actual month it took place instead of the month
before (this is only a problem during the first 5 days of the month).

My other option is to have all users wait to enter records until the 6th of
the month, but this is not very convenient.

Let me know if you need more info...
Thanks!

 

[Clifford Bass]

Hi Thorson,

Let's take a step back. Based on your second paragraph; why even look
at the entry date? Why not just use only the disposition date?

Clifford Bass

 

[Thorson]

The Disposition Date is when the record actually took place, the users have
until the 5th of the following month to enter the Disposition records, these
will be reported in the month of the Disposition Date.

Occasionally the records will be found days or months later, after the
report has been submitted. The records are then entered in the database.
Once the report is submitted the report cannot be changed, it is set in stone
for that month, the record that was found and reported late is required to be
shown as a correction on the report for the month it was found and entered
(the entry date).

 

[Clifford Bass]

Hi Thorson,

Okay, I think I understand. You want records with a disposition date
in the specified month and year when the entry date is on or before the fifth
of the month following the disposition date. Otherwise, you want records
with an entry date in the specified month and year when the entry date is
greater than the fifth of the month following the disposition date.

Disposition Entry Include in June? Include in July?
6/1/09 6/1/09 Yes No
6/1/09 7/5/09 Yes No
6/1/09 7/6/09 No Yes
7/1/09 7/1/09 No Yes

Try this, adjusting as needed for your actual table(s) and fields:

select ....
from ....
where (Year([Disposition Date]) =
[Forms]![frmCurrentInventoryDateQuery]![cboYear] and Month([Disposition
Date]) = [Forms]![frmCurrentInventoryDateQuery]![cboMonth] and [Entry Date]
<= DateSerial(Year([Disposition Date]), Month([Disposition Date]) + 1, 5)) or
(Year([Entry Date]) = [Forms]![frmCurrentInventoryDateQuery]![cboYear] and
Month([Entry Date]) = [Forms]![frmCurrentInventoryDateQuery]![cboMonth] and
[Entry Date] > DateSerial(Year([Disposition Date]), Month([Disposition Date])
+ 1, 5))

Make the changes in the SQL view to your where condition and then
switch back into the design view to see how it looks in that view. There
should be one line for the first three anded conditions before the "or" and
another line for the three anded conditions after the "or". Note that
DateSerial can take illegal month (and/or day) values and will adjust the
date accordingly. So DateSerial(2009, 12 + 1, 1) will actually give you
January 1, 2010.

Hope that does it,

Clifford Bass

 

[Thorson]

That is exactly what I needed, it works perfectly!!!! Thank you so much!
--
Thorson

Hi Thorson,

Okay, I think I understand. You want records with a disposition date
in the specified month and year when the entry date is on or before the fifth
of the month following the disposition date. Otherwise, you want records
with an entry date in the specified month and year when the entry date is
greater than the fifth of the month following the disposition date.

Disposition Entry Include in June? Include in July?
6/1/09 6/1/09 Yes No
6/1/09 7/5/09 Yes No
6/1/09 7/6/09 No Yes
7/1/09 7/1/09 No Yes

Try this, adjusting as needed for your actual table(s) and fields:

select ....
from ....
where (Year([Disposition Date]) =
[Forms]![frmCurrentInventoryDateQuery]![cboYear] and Month([Disposition
Date]) = [Forms]![frmCurrentInventoryDateQuery]![cboMonth] and [Entry Date]
<= DateSerial(Year([Disposition Date]), Month([Disposition Date]) + 1, 5)) or
(Year([Entry Date]) = [Forms]![frmCurrentInventoryDateQuery]![cboYear] and
Month([Entry Date]) = [Forms]![frmCurrentInventoryDateQuery]![cboMonth] and
[Entry Date] > DateSerial(Year([Disposition Date]), Month([Disposition Date])
+ 1, 5))

Make the changes in the SQL view to your where condition and then
switch back into the design view to see how it looks in that view. There
should be one line for the first three anded conditions before the "or" and
another line for the three anded conditions after the "or". Note that
DateSerial can take illegal month (and/or day) values and will adjust the
date accordingly. So DateSerial(2009, 12 + 1, 1) will actually give you
January 1, 2010.

Hope that does it,

Clifford Bass

The Disposition Date is when the record actually took place, the users have
until the 5th of the following month to enter the Disposition records, these
will be reported in the month of the Disposition Date.

Occasionally the records will be found days or months later, after the
report has been submitted. The records are then entered in the database.
Once the report is submitted the report cannot be changed, it is set in stone
for that month, the record that was found and reported late is required to be
shown as a correction on the report for the month it was found and entered
(the entry date).

 

[Clifford Bass]

Hi Thorson,

Great! You are welcome.

Clifford Bass

 

[Thorson]

I actually have another question along the same lines if you have a few
minutes, otherwise I can just post it as a separate question.

Now that I have the records flowing into the correct month I want the ones
that were entered at a different time than the disposition records to show as
a "Correction" So I have a new column with an Iif statement. This works fine
except if the record was entered the first of a different month. Can I tack
on something to the end of the statment stating "OR if the entry month is >2
months more than the Disposition Month (Expr1) then put "Correction"
otherwise null

This is what I have right now (the >5 should be greater than 5days, it was
working before but isn't now... I need to fix that too):
Correction:
IIf(Month([qryDSACMonthlyDispositionRecordsTransferred]![EntryDate])<>[qryDSACMonthlyDispositionRecordsTransferred]![Expr1]
And ([qryDSACMonthlyDispositionRecordsTransferred]![EntryDate]>5),
"Correction",Null)

 

[Clifford Bass]

Hi Thorson,

You could just use the same condition that is used in criteria section
(modified appropriately as necessary):

Correction: IIf([Entry Date] > DateSerial(Year([Disposition Date]),
Month([Disposition Date]) + 1, 5), "Correction", Null)

Clifford Bass

 

[Thorson]

Thank you, works perfectly. I've been trying to figure this whole thing out
for forever! You were VERY helpful!
--
Thorson

Hi Thorson,

You could just use the same condition that is used in criteria section
(modified appropriately as necessary):

Correction: IIf([Entry Date] > DateSerial(Year([Disposition Date]),
Month([Disposition Date]) + 1, 5), "Correction", Null)

Clifford Bass

I actually have another question along the same lines if you have a few
minutes, otherwise I can just post it as a separate question.

Now that I have the records flowing into the correct month I want the ones
that were entered at a different time than the disposition records to show as
a "Correction" So I have a new column with an Iif statement. This works fine
except if the record was entered the first of a different month. Can I tack
on something to the end of the statment stating "OR if the entry month is >2
months more than the Disposition Month (Expr1) then put "Correction"
otherwise null

This is what I have right now (the >5 should be greater than 5days, it was
working before but isn't now... I need to fix that too):
Correction:
IIf(Month([qryDSACMonthlyDispositionRecordsTransferred]![EntryDate])<>[qryDSACMonthlyDispositionRecordsTransferred]![Expr1]
And ([qryDSACMonthlyDispositionRecordsTransferred]![EntryDate]>5),
"Correction",Null)

 

[Clifford Bass]

Hi Thorson,

You are quite welcome!

Clifford Bass