In between dates with a different twist

[Guest]

I need to capture the month and year that the [ap_date] falls between two
dates. But the between dates are capturing some dates in the previous month
too.

An example of this would be.

The [ap_date] is 9/28/2005 and I want it to return 10/2005 because the
between dates are #9/24/2005# and #10/23/2005#.

Another example would be The [ap_date] is 10/1/2005 and I want it to return
10/2005.

I don't want to re-type every month and change the months in the between
operator. I would like it to do it by itself.

I thought that I could do month = n and for the between dates do n and n+1.
But that wouldn't work for my second example.

Any help would be greatly appreciated!

 

[Guest]

What would be your frame of reference for the dates? Will it always be from
the 24th to the 23rd? Will it be the fourth week throught third week?

 

[Guest]

Thanks Karl for your response.

Yes, the dates will always be the 24th to the 23rd. Basically, xx/24/2005 to
xx/23/2005.

I've tried so many things but can't grasp it. I thought about storing the
between dates in a table and just build it out to 2008 but I want to try and
stay away from that. But I will save that as a last resort.

Thanks again for any help!
Keith

What would be your frame of reference for the dates? Will it always be from
the 24th to the 23rd? Will it be the fourth week throught third week?

I need to capture the month and year that the [ap_date] falls between two
dates. But the between dates are capturing some dates in the previous month
too.

An example of this would be.

The [ap_date] is 9/28/2005 and I want it to return 10/2005 because the
between dates are #9/24/2005# and #10/23/2005#.

Another example would be The [ap_date] is 10/1/2005 and I want it to return
10/2005.

I don't want to re-type every month and change the months in the between
operator. I would like it to do it by itself.

I thought that I could do month = n and for the between dates do n and n+1.
But that wouldn't work for my second example.

Any help would be greatly appreciated!

 

[Guest]

This will work from the first of the month through the 23rd.

Between CVDate(Month(Date()-Day(Date())) & "/" & 24 & "/" &
Year(Date()-Day(Date()))) And DateAdd("m",1,CVDate(Month(Date()-Day(Date()))
& "/" & 24 & "/" & Year(Date()-Day(Date()))))-1

Thanks Karl for your response.

Yes, the dates will always be the 24th to the 23rd. Basically, xx/24/2005 to
xx/23/2005.

I've tried so many things but can't grasp it. I thought about storing the
between dates in a table and just build it out to 2008 but I want to try and
stay away from that. But I will save that as a last resort.

Thanks again for any help!
Keith

What would be your frame of reference for the dates? Will it always be from
the 24th to the 23rd? Will it be the fourth week throught third week?

I need to capture the month and year that the [ap_date] falls between two
dates. But the between dates are capturing some dates in the previous month
too.

An example of this would be.

The [ap_date] is 9/28/2005 and I want it to return 10/2005 because the
between dates are #9/24/2005# and #10/23/2005#.

Another example would be The [ap_date] is 10/1/2005 and I want it to return
10/2005.

I don't want to re-type every month and change the months in the between
operator. I would like it to do it by itself.

I thought that I could do month = n and for the between dates do n and n+1.
But that wouldn't work for my second example.

Any help would be greatly appreciated!

 

[Guest]

Karl,
Thanks for your suggestion. When I put your statement in, it returns a 0 or
a -1. Do I have to write something else to get mm/yyyy in that column instead
of the 0 or -1. Or did I do it wrong?

I just put in "date: [ap_date]" before the start of your between.

date: [ap_date] Between CVDate(Month(Date()-Day(Date())) & "/" & 24 & "/" &

Year(Date()-Day(Date()))) And DateAdd("m",1,CVDate(Month(Date()-Day(Date())) > & "/" & 24 & "/" & Year(Date()-Day(Date()))))-1

Sorry for being a pain but I really appreciate the help.

Keith

This will work from the first of the month through the 23rd.

Between CVDate(Month(Date()-Day(Date())) & "/" & 24 & "/" &
Year(Date()-Day(Date()))) And DateAdd("m",1,CVDate(Month(Date()-Day(Date()))
& "/" & 24 & "/" & Year(Date()-Day(Date()))))-1

Thanks Karl for your response.

Yes, the dates will always be the 24th to the 23rd. Basically, xx/24/2005 to
xx/23/2005.

I've tried so many things but can't grasp it. I thought about storing the
between dates in a table and just build it out to 2008 but I want to try and
stay away from that. But I will save that as a last resort.

Thanks again for any help!
Keith

What would be your frame of reference for the dates? Will it always be from
the 24th to the 23rd? Will it be the fourth week throught third week?

:

I need to capture the month and year that the [ap_date] falls between two
dates. But the between dates are capturing some dates in the previous month
too.

An example of this would be.

The [ap_date] is 9/28/2005 and I want it to return 10/2005 because the
between dates are #9/24/2005# and #10/23/2005#.

Another example would be The [ap_date] is 10/1/2005 and I want it to return
10/2005.

I don't want to re-type every month and change the months in the between
operator. I would like it to do it by itself.

I thought that I could do month = n and for the between dates do n and n+1.
But that wouldn't work for my second example.

Any help would be greatly appreciated!

 

[Guest]

The post was a criteria for your date column.

What kind of column are you getting a 0 or -1 ?

Karl,
Thanks for your suggestion. When I put your statement in, it returns a 0 or
a -1. Do I have to write something else to get mm/yyyy in that column instead
of the 0 or -1. Or did I do it wrong?

I just put in "date: [ap_date]" before the start of your between.

date: [ap_date] Between CVDate(Month(Date()-Day(Date())) & "/" & 24 & "/" &

Year(Date()-Day(Date()))) And DateAdd("m",1,CVDate(Month(Date()-Day(Date())) > & "/" & 24 & "/" & Year(Date()-Day(Date()))))-1

Sorry for being a pain but I really appreciate the help.

Keith

This will work from the first of the month through the 23rd.

Between CVDate(Month(Date()-Day(Date())) & "/" & 24 & "/" &
Year(Date()-Day(Date()))) And DateAdd("m",1,CVDate(Month(Date()-Day(Date()))
& "/" & 24 & "/" & Year(Date()-Day(Date()))))-1

Thanks Karl for your response.

Yes, the dates will always be the 24th to the 23rd. Basically, xx/24/2005 to
xx/23/2005.

I've tried so many things but can't grasp it. I thought about storing the
between dates in a table and just build it out to 2008 but I want to try and
stay away from that. But I will save that as a last resort.

Thanks again for any help!
Keith



:

What would be your frame of reference for the dates? Will it always be from
the 24th to the 23rd? Will it be the fourth week throught third week?

:

I need to capture the month and year that the [ap_date] falls between two
dates. But the between dates are capturing some dates in the previous month
too.

An example of this would be.

The [ap_date] is 9/28/2005 and I want it to return 10/2005 because the
between dates are #9/24/2005# and #10/23/2005#.

Another example would be The [ap_date] is 10/1/2005 and I want it to return
10/2005.

I don't want to re-type every month and change the months in the between
operator. I would like it to do it by itself.

I thought that I could do month = n and for the between dates do n and n+1.
But that wouldn't work for my second example.

Any help would be greatly appreciated!

 

[Guest]

I put the statement in the Field Criteria of the query.

Maybe I'm not explaining myself correctly. I have the [ap_date] column and
on the next column [posted] I would like to state what month/year it falls
into (according to our accounting rules).

e.g.
ap_date Posted
9/25/2005 10/2005
10/1/2005 10/2005
10/27/2005 11/2005

Thanks for being patient with me and trying to understand.

The post was a criteria for your date column.

What kind of column are you getting a 0 or -1 ?

Karl,
Thanks for your suggestion. When I put your statement in, it returns a 0 or
a -1. Do I have to write something else to get mm/yyyy in that column instead
of the 0 or -1. Or did I do it wrong?

I just put in "date: [ap_date]" before the start of your between.

date: [ap_date] Between CVDate(Month(Date()-Day(Date())) & "/" & 24 & "/" &

Year(Date()-Day(Date()))) And DateAdd("m",1,CVDate(Month(Date()-Day(Date())) > & "/" & 24 & "/" & Year(Date()-Day(Date()))))-1

Sorry for being a pain but I really appreciate the help.

Keith

This will work from the first of the month through the 23rd.

Between CVDate(Month(Date()-Day(Date())) & "/" & 24 & "/" &
Year(Date()-Day(Date()))) And DateAdd("m",1,CVDate(Month(Date()-Day(Date()))
& "/" & 24 & "/" & Year(Date()-Day(Date()))))-1

:

Thanks Karl for your response.

Yes, the dates will always be the 24th to the 23rd. Basically, xx/24/2005 to
xx/23/2005.

I've tried so many things but can't grasp it. I thought about storing the
between dates in a table and just build it out to 2008 but I want to try and
stay away from that. But I will save that as a last resort.

Thanks again for any help!
Keith



:

What would be your frame of reference for the dates? Will it always be from
the 24th to the 23rd? Will it be the fourth week throught third week?

:

I need to capture the month and year that the [ap_date] falls between two
dates. But the between dates are capturing some dates in the previous month
too.

An example of this would be.

The [ap_date] is 9/28/2005 and I want it to return 10/2005 because the
between dates are #9/24/2005# and #10/23/2005#.

Another example would be The [ap_date] is 10/1/2005 and I want it to return
10/2005.

I don't want to re-type every month and change the months in the between
operator. I would like it to do it by itself.

I thought that I could do month = n and for the between dates do n and n+1.
But that wouldn't work for my second example.

Any help would be greatly appreciated!

 

[Guest]

Your Posted field appears to be a text field. If it is a datetime field I
think this will give you what you want.

SELECT Accounting.ap_date, Accounting.Posted,
Format(IIf(Day([AP_DATE])<24,CVDate(Month([AP_Date]-Day([AP_Date])) & "/" &
24 & "/" &
Year([AP_Date]-Day([AP_Date]))),CVDate(Month([AP_Date]-Day([AP_Date]))+1 &
"/" & 24 & "/" & Year([AP_Date]-Day([AP_Date])))),"m/yyyy") AS [Accounting
Month],
IIf(DateDiff("m",IIf(Day([AP_DATE])<24,CVDate(Month([AP_Date]-Day([AP_Date]))
& "/" & 24 & "/" &
Year([AP_Date]-Day([AP_Date]))),CVDate(Month([AP_Date]-Day([AP_Date]))+1 &
"/" & 24 & "/" & Year([AP_Date]-Day([AP_Date])))),[POSTED])>1,"More than one
Accounting month","") AS [Error Check]
FROM Accounting;

I put the statement in the Field Criteria of the query.

Maybe I'm not explaining myself correctly. I have the [ap_date] column and
on the next column [posted] I would like to state what month/year it falls
into (according to our accounting rules).

e.g.
ap_date Posted
9/25/2005 10/2005
10/1/2005 10/2005
10/27/2005 11/2005

Thanks for being patient with me and trying to understand.

The post was a criteria for your date column.

What kind of column are you getting a 0 or -1 ?

Karl,
Thanks for your suggestion. When I put your statement in, it returns a 0 or
a -1. Do I have to write something else to get mm/yyyy in that column instead
of the 0 or -1. Or did I do it wrong?

I just put in "date: [ap_date]" before the start of your between.

date: [ap_date] Between CVDate(Month(Date()-Day(Date())) & "/" & 24 & "/" &
Year(Date()-Day(Date()))) And DateAdd("m",1,CVDate(Month(Date()-Day(Date())) > & "/" & 24 & "/" & Year(Date()-Day(Date()))))-1


Sorry for being a pain but I really appreciate the help.

Keith

:

This will work from the first of the month through the 23rd.

Between CVDate(Month(Date()-Day(Date())) & "/" & 24 & "/" &
Year(Date()-Day(Date()))) And DateAdd("m",1,CVDate(Month(Date()-Day(Date()))
& "/" & 24 & "/" & Year(Date()-Day(Date()))))-1

:

Thanks Karl for your response.

Yes, the dates will always be the 24th to the 23rd. Basically, xx/24/2005 to
xx/23/2005.

I've tried so many things but can't grasp it. I thought about storing the
between dates in a table and just build it out to 2008 but I want to try and
stay away from that. But I will save that as a last resort.

Thanks again for any help!
Keith



:

What would be your frame of reference for the dates? Will it always be from
the 24th to the 23rd? Will it be the fourth week throught third week?

:

I need to capture the month and year that the [ap_date] falls between two
dates. But the between dates are capturing some dates in the previous month
too.

An example of this would be.

The [ap_date] is 9/28/2005 and I want it to return 10/2005 because the
between dates are #9/24/2005# and #10/23/2005#.

Another example would be The [ap_date] is 10/1/2005 and I want it to return
10/2005.

I don't want to re-type every month and change the months in the between
operator. I would like it to do it by itself.

I thought that I could do month = n and for the between dates do n and n+1.
But that wouldn't work for my second example.

Any help would be greatly appreciated!

 

[Gary Walter]

Hi Keith,

Would it work for you to think
of [posted] as a date always
the first day of month, i.e.,
"10/2005" ---> date 10/1/2005,
then format it to "mm/yyyy" when
you need to show it?

if so, then the calculated expression
for [posted] could be:
(in Access where true = -1)

DateSerial(Year([ap_date]),Month([ap_date])-(Day([ap_date])>23),1)

the above will easily handle
end-of-the-year dates w/o
any additional logic....

good luck,

gary

I put the statement in the Field Criteria of the query.

Maybe I'm not explaining myself correctly. I have the [ap_date] column and
on the next column [posted] I would like to state what month/year it falls
into (according to our accounting rules).

e.g.
ap_date Posted
9/25/2005 10/2005
10/1/2005 10/2005
10/27/2005 11/2005

<snip>

 

[Guest]

Thanks Karl! I really appreciate the help you gave me.

Your Posted field appears to be a text field. If it is a datetime field I
think this will give you what you want.

SELECT Accounting.ap_date, Accounting.Posted,
Format(IIf(Day([AP_DATE])<24,CVDate(Month([AP_Date]-Day([AP_Date])) & "/" &
24 & "/" &
Year([AP_Date]-Day([AP_Date]))),CVDate(Month([AP_Date]-Day([AP_Date]))+1 &
"/" & 24 & "/" & Year([AP_Date]-Day([AP_Date])))),"m/yyyy") AS [Accounting
Month],
IIf(DateDiff("m",IIf(Day([AP_DATE])<24,CVDate(Month([AP_Date]-Day([AP_Date]))
& "/" & 24 & "/" &
Year([AP_Date]-Day([AP_Date]))),CVDate(Month([AP_Date]-Day([AP_Date]))+1 &
"/" & 24 & "/" & Year([AP_Date]-Day([AP_Date])))),[POSTED])>1,"More than one
Accounting month","") AS [Error Check]
FROM Accounting;

I put the statement in the Field Criteria of the query.

Maybe I'm not explaining myself correctly. I have the [ap_date] column and
on the next column [posted] I would like to state what month/year it falls
into (according to our accounting rules).

e.g.
ap_date Posted
9/25/2005 10/2005
10/1/2005 10/2005
10/27/2005 11/2005

Thanks for being patient with me and trying to understand.

The post was a criteria for your date column.

What kind of column are you getting a 0 or -1 ?

:

Karl,
Thanks for your suggestion. When I put your statement in, it returns a 0 or
a -1. Do I have to write something else to get mm/yyyy in that column instead
of the 0 or -1. Or did I do it wrong?

I just put in "date: [ap_date]" before the start of your between.

date: [ap_date] Between CVDate(Month(Date()-Day(Date())) & "/" & 24 & "/" &
Year(Date()-Day(Date()))) And DateAdd("m",1,CVDate(Month(Date()-Day(Date())) > & "/" & 24 & "/" & Year(Date()-Day(Date()))))-1


Sorry for being a pain but I really appreciate the help.

Keith

:

This will work from the first of the month through the 23rd.

Between CVDate(Month(Date()-Day(Date())) & "/" & 24 & "/" &
Year(Date()-Day(Date()))) And DateAdd("m",1,CVDate(Month(Date()-Day(Date()))
& "/" & 24 & "/" & Year(Date()-Day(Date()))))-1

:

Thanks Karl for your response.

Yes, the dates will always be the 24th to the 23rd. Basically, xx/24/2005 to
xx/23/2005.

I've tried so many things but can't grasp it. I thought about storing the
between dates in a table and just build it out to 2008 but I want to try and
stay away from that. But I will save that as a last resort.

Thanks again for any help!
Keith



:

What would be your frame of reference for the dates? Will it always be from
the 24th to the 23rd? Will it be the fourth week throught third week?

:

I need to capture the month and year that the [ap_date] falls between two
dates. But the between dates are capturing some dates in the previous month
too.

An example of this would be.

The [ap_date] is 9/28/2005 and I want it to return 10/2005 because the
between dates are #9/24/2005# and #10/23/2005#.

Another example would be The [ap_date] is 10/1/2005 and I want it to return
10/2005.

I don't want to re-type every month and change the months in the between
operator. I would like it to do it by itself.

I thought that I could do month = n and for the between dates do n and n+1.
But that wouldn't work for my second example.

Any help would be greatly appreciated!

 

[Guest]

WOW!!! I tried your suggestion and it works great too!
Thank you guys for the great suggestions! This has saved me a lot of time!
You guys are great!
Keith

Hi Keith,

Would it work for you to think
of [posted] as a date always
the first day of month, i.e.,
"10/2005" ---> date 10/1/2005,
then format it to "mm/yyyy" when
you need to show it?

if so, then the calculated expression
for [posted] could be:
(in Access where true = -1)

DateSerial(Year([ap_date]),Month([ap_date])-(Day([ap_date])>23),1)

the above will easily handle
end-of-the-year dates w/o
any additional logic....

good luck,

gary

I put the statement in the Field Criteria of the query.

Maybe I'm not explaining myself correctly. I have the [ap_date] column and
on the next column [posted] I would like to state what month/year it falls
into (according to our accounting rules).

e.g.
ap_date Posted
9/25/2005 10/2005
10/1/2005 10/2005
10/27/2005 11/2005

<snip>