Show Expense if within Date Range

[kami217]

Hi,

I've been doing some searching online and haven't been able to find specifically what I'm looking for.
I'm familiar with SQL/Excel - but not as up to speed with Access, so unsure how to implement this query.

I have a table with the following Data:

Rent 1, Rent 1 Start, Rent 1 End, Rent 2, Rent 2 Start, Rent 2 End, (etc...),Utilities, Insurance

I want to be able to run a query that says - if Today() is between rent start/rent end, pull that value.

So for example:

Rent 1	Rent 1 Start	Rent 1 End	Rent 2	Rent 2 Start	Rent 2 End	Utilities	Insurance
$1000	1/1/17	12/31/17	$2000	1/1/18	12/31/18	$500	$350

I would like the report to only pull the Rent for $2000 (since it's 2018) + $500 (Utilities) + $350 (Insurance) = $2,850 expenses per month




Can anyone assist? I'm assuming I would have to select all and then use an If/Then statement?


Thanks in advance for any help you can provide.

[June7]

This is not a normalized data structure and that greatly complicates search and filter and summarizing efforts.

Did you import spreadsheet into Access?

Yes, an IIf is likely needed.

IIf(Date() BETWEEN [Rent 1 Start] AND [Rent 1 End], [Rent 1], [Rent 2]) + [Utilities] + [Insurance]