Issue with IIF

[DEllis]

Hi everyone, I want to pull everything in a query criteria if only a year is selected on a report dashboard. So I select the year and I get nothing. If I select a month, then I get just those for the month. Can anyone tell me what is going on with my logic here.

IIf([FORMS]![Reporting Dashboard]![Month_Listbox] Is Not Null,[forms]![Reporting Dashboard]![Month_Listbox],'*')

So how I read this is if, the month list box is not null (something is selected) then give me the months for the year I selected; however, if it is null, give me all data for the year.

If I put the number 5 where the asterisk is, I get I do get the month. But what I want is everything if only the year is selected.

Help please.

Thank you,
DEllis

 

[welshgasman]

I read that as give me everything in the month column if the control is null (which is logical), as I presume you have put this in the month column of the query designer?
You should put similar logic in the year column.
That way if no month is selected you get all months, if no year is selected you get all years. If you select a month and a year you get only data only for that criteria.

 

[DEllis]

Hi Welshgasman, thank you for your response. You are correct in your assumption that I want everything from the month column. If I put in a number it pulls the month, what do I need to put in there to pull everything if a year is selected? The asterisk (*) is not working to pull in everything.

Thank you so much for responding, looking forward to your reply.

Take care,
DEllis

 

[welshgasman]

Split the query data into month and year of the date from the field. Then query each individually.
So if you leave month empty on your form and put in a year, you get everything for that year.
I do not believe you can do this on one field alone.

 

[DEllis]

Hi Again, we do have the year and month in separate columns. The issue comes when we put a condition for the month. The everything Wildcard "*" is not working. Is there another way to pull in all months when only the year in the year column is selected? The "*" asterisk works alone when we put in the criteria field but not in the IIF statement.

 

[welshgasman]

DEllis,
My apologies, you are correct, it will not work as you have it.
However I recreated your scenario and

Like IIf([Forms]![Form1]![txtMonth] Is Not Null,[Forms]![Form1]![txtMonth],"*")

does work.

 

[welshgasman]

According to this thread, True should work, but I could not get it to work.
However post #2 here https://access-programmers.co.uk/forums/showthread.php?t=46391 does work.

SELECT [old Transactions].*, Month([date]) AS MonthDate
FROM [old Transactions]
WHERE (((Month([date]))=[Forms]![Form1]![txtMonth])) OR ((([Forms]![Form1]![txtMonth]) Is Null));

 

[DEllis]

Thank you!