Half years in a query

[msmuzila]

I am making a query and need to split it into half years. When I split
it into quarters i use:

Format([SHIP_DATE],"q")

is there another letter/s to use in place of "q" for half years

Thanks
Matt

 

[storrboy]

I am making a query and need to split it into half years. When I split
it into quarters i use:

Format([SHIP_DATE],"q")

is there another letter/s to use in place of "q" for half years

Thanks
Matt

I don't think so, but you can probably use this is a calculated field.
IIF(Month([Ship_Date])<7,1,2)
(Based on June being 1st half)

 

[msmuzila]

I am making a query and need to split it into half years. When I split
it into quarters i use:

Format([SHIP_DATE],"q")

is there another letter/s to use in place of "q" for half years

Thanks
Matt

I don't think so, but you can probably use this is a calculated field.
IIF(Month([Ship_Date])<7,1,2)
(Based on June being 1st half)

How would i write that if i want 1 to be the last 180 days and 2 to be
the 180 days before that (180 to 360 days ago)

 

[storrboy]

I think it would be better then to use..
IIF(DateDiff("d",Date(),[SHIPDATE])<=180,1,2)

Check the results, not sure if I have the two date values in DateDiff
in the right order. There may however be a problem if it encounters
future dates. This might return a negative number and as such think it
is in the first half. You would either have to plan for that
possiblity (I don't know the circumstances so I can't), or it may be
that you have to have a fixed starting point for one of the two halves.

 

[msmuzila]

I think it would be better then to use..
IIF(DateDiff("d",Date(),[SHIPDATE])<=180,1,2)

Check the results, not sure if I have the two date values in DateDiff
in the right order. There may however be a problem if it encounters
future dates. This might return a negative number and as such think it
is in the first half. You would either have to plan for that
possiblity (I don't know the circumstances so I can't), or it may be
that you have to have a fixed starting point for one of the two halves.

I need to have it so i can show the last five 6 month periods

 

[John W. Vinson/MVP]

How would i write that if i want 1 to be the last 180 days and 2 to be
the 180 days before that (180 to 360 days ago)

Do you want 180 days? Or do you want half-years (182, 183 or 184 days
depending on which half-year)?

I'd suggest using six-month intervals: try a calculated field:

DateDiff("m", [datefield], Date()) \ 6

This will be 0 for the most recent six months (from six months ago today
until today), 1 for the six months before that, 2 for the previous half
year, etc.

If you want half-years from January through June, July through December,
that's another issue - it's not clear from your post which you prefer.

John W. Vinson [MVP]

 

[msmuzila]

Thanks, that worked exactly the way i needed