Split Date Values in Query

[RIck5150]

I have a manufacturer that has sent me payments from 8/2/2012 to 05/20/2013 but there was a wide gap where we did not do business from the end of 2012 to April 2013.

How can split the date ranges up in the query so when I create my report, I can get two ranges for the same manufacture.

For example one list of payments from <#12/31/20112# and the other from >#4/1/2013#

 

[Pat Hartman]

Are you trying to summarize the resultset? Is that why you are trying to make two ranges? Range implies multiple records though so I am confused.

You might be able to use the IIf() function to solve the problem.

IIf(PayDT <= #12/31/2012#, "Older", "Newer") As TimeFrame

 

[RIck5150]

Thank you Pat. Yes, there are dozens of records. I wanted to have a breakdown of each payment that was made by multiple manufacturers and their totals. That part is easy.

However, I worked with one manufacturer for two time periods and wanted to separate each period for that single manufacturer only.

If I am correct, that query function will separate all manufacturers before 1/1/2013 and after?

 

[pr2-eugin]

The sample code Pat has provided will create a New field in the Query result called "TimeFrame" that will show the ones after 1/1/2013 as "Newer", anything else as "Older".. Makes sense? It will not strip them away from the result..

If you could throw in a few examples of what sort of data you have in the table and what you wish to see in the result we will be able to get you a more appropriate solution..

 

[Brianwarnock]

You are presumably doing a sum and Groupby totals query, well include the field timeframe in the Groupby, for the one manufacturer you will get two groups for the rest only one. Given the date ranges you could have used

Year(paydt) as YearPaydt instead

Brian

 

[Brianwarnock]

Ah! On second thoughts you are not doing a Totals query but probably a report, I assumed a Totals query as this is the query forum.
The point about grouping still applie s but this time in the report.

Brian