Variously exclude certain fields from a record based on criteria

[MikeInToronto]

Hi guys,

I have a table of social events. Each event gets funded twice. Basically, the fields in the table are:

EventName
AmountOfFirstFunding
DateOfFirstFunding
AmountOfSecondFunding
DateOfSecondFunding



I have a form that collects a date range via text boxes and then the user hits a command button to launch a report. I want the report to list every event that received funding in the given date range. Now, for any given event, it may have received its first funding amount in the date range, or it may have received the second funding amount in the date range, it may have received both funding amounts in the date range, or it might not have received any funding in the date range, in which case I don't want the event listed at all.

For example, here's a record:
EventName: Internet Marketing
AmountOfFirstFunding: $1000
DateOfFirstFunding: September 1, 2013
AmountOfSecondFunding: $2000
DateOfSecondFunding: October 1, 2013

If the user inputs the date range of September 1, 2013 - September 30, 2013 (which excludes the second funding amount), I want to see in the report:

EVENT NAME          AMOUNT OF FIRST FUNDING        DATE OF FIRST FUNDING          AMOUNT OF SECOND FUNDING        DATE OF SECOND FUNDING
Internet Marketing  $1000                          September 1, 2013

When the user puts in September 1, 2013 - October 30, 2013 (which includes both funding amounts), I want to see:

EVENT NAME          AMOUNT OF FIRST FUNDING        DATE OF FIRST FUNDING          AMOUNT OF SECOND FUNDING        DATE OF SECOND FUNDING
Internet Marketing  $1000                          September 1, 2013              $2000                           October 1, 2013

Similarly, if the date range covers the second funding amount but not the first, I only want the second funding information displayed. And if the date range given does not match at all with the event's funding information, I do not want the event shown at all.

I'm lost at how to do this using the criteria parameters in the query design window.

Any tips? I have no experience using SQL but I can do VBA functions.

[June7]

Not a normalized structure but can work with.

Try:

Calculate a field in query:

FundDate: Nz([DateOfSecondFunding], [DateOfFirstFunding])

Then apply the date range criteria to that constructed field.

Can do the same with the amount fields.

FundAmount: Nz([AmountOfSecondFunding], [AmountOfFirstFunding])

and

FundSeq: IIf(Not IsNull([AmountOfSecondFunding], "Second", IIf(Not IsNull([AmountOfFirstFunding]), "First", Null))


Now just show those constructed fields in report.

[MikeInToronto]

Thanks for the response! Got this working.