IIF Function help

[Rosadocc]

Hi. Im fairly new to running Access queries but if anybody can help me it would be greatly appreciated.

I am trying to set up an automated query that will be run monthly. It will pull sales totals by month for the FY. I have that part down.

The user will be prompted to enter a begin date(10/01/2014) and end date(current date).

Where I'm having difficulties is that I want the user to also be prompted to enter Month. THis is for a third column that will return only the sales total for that month entered.

For example the returned data would look like if the user entered February

Sales	Month	Current Month
$100	October
$250	November
$300	December
$150	January
$500	February	$500
$450	March
$250	April
$300	May
$350	June

<tbody>
</tbody>

I am attempting to use a calculated field for this but not sure how to do it.

Thanks!

 

[Micron]

If the month you want your user to supply is criteria that is contained in one of the two already supplied, just get the month portion of that date. Here is a great reference for all sorts of Access functions that you can look at. See the Date functions and see which one would work best for you (you might decide to work with workdays or some other variable). You can simply use the function as criteria in a calculated query field.

 

[Rosadocc]

If the month you want your user to supply is criteria that is contained in one of the two already supplied, just get the month portion of that date. Here is a great reference for all sorts of Access functions that you can look at. See the Date functions and see which one would work best for you (you might decide to work with workdays or some other variable). You can simply use the function as criteria in a calculated query field.

I suppose having the user enter the month isn't necessary, They can input it in the criteria in design view. My question is whether there is a IIF expression built in a calculated field (column) that would return Total Sales IF it is a certain month or between a certain date such as >=06/01 AND <06/30?

 

[Micron]

Sheesh! I forgot the link. Sorry...
MS Access: Functions - Listed by Category
Usually, I don't ask a poster why they want to do something because I assume they shouldn't have to explain their business case, but for your issue I feel the need. Why would you want a specific month to be totalled in one row of a recordset returned by a query when you already have it in some other column/field? After giving it some thought, I don't know how to do what you're attempting although I gave it a try:
IIf(Month([single month])=Month([startdate]),Sum([nameOfSalesField]),"") but it choked. Further investigation leads me to believe this can never work in a Totals query, which is what I think you have given that you are summing sales figures. The reason is that every field in such a query has to be either part of a group by clause or a sql aggregate function. An IIF function creates a calculated field that cannot be grouped or aggregated. Even if you could get it to work, I think there are so many things that could cause this to fail. For example, what if the user enters the third date incorrectly, or is it beyond the begin or start dates supplied? What value should the IIF function return where the match is not met? What if a row date is NULL? It might have to be a zero on every row or "" - depends on the field data type. If you didn't want zeros, then the field has to be text. Now you're going to have to try to add text characters? What I think you need to do is use a calculated textbox on a form or report where you might be able to use something similar to what I used in this post as a totals textbox in a grouping footer. However, I think I would first try an unbound textbox that has a query as it's source. This query would return the sum only where the table dates match the month part of a date contained in one of the form/report controls. I still think you will need to review the date functions as already noted.

To answer your question on how to use functions in a query, the site at the posted link shows images for this so do check it out.

 

[Rosadocc]

Sheesh! I forgot the link. Sorry...
MS Access: Functions - Listed by Category
Usually, I don't ask a poster why they want to do something because I assume they shouldn't have to explain their business case, but for your issue I feel the need. Why would you want a specific month to be totalled in one row of a recordset returned by a query when you already have it in some other column/field? After giving it some thought, I don't know how to do what you're attempting although I gave it a try:
IIf(Month([single month])=Month([startdate]),Sum([nameOfSalesField]),"") but it choked. Further investigation leads me to believe this can never work in a Totals query, which is what I think you have given that you are summing sales figures. The reason is that every field in such a query has to be either part of a group by clause or a sql aggregate function. An IIF function creates a calculated field that cannot be grouped or aggregated. Even if you could get it to work, I think there are so many things that could cause this to fail. For example, what if the user enters the third date incorrectly, or is it beyond the begin or start dates supplied? What value should the IIF function return where the match is not met? What if a row date is NULL? It might have to be a zero on every row or "" - depends on the field data type. If you didn't want zeros, then the field has to be text. Now you're going to have to try to add text characters? What I think you need to do is use a calculated textbox on a form or report where you might be able to use something similar to what I used in this post as a totals textbox in a grouping footer. However, I think I would first try an unbound textbox that has a query as it's source. This query would return the sum only where the table dates match the month part of a date contained in one of the form/report controls. I still think you will need to review the date functions as already noted.

To answer your question on how to use functions in a query, the site at the posted link shows images for this so do check it out.

Thanks for giving it a shot. I got close to what I was looking for in my table above. The function I came up with is:
Current Month: IIf([MM]=" 6",([dbo_XYZ]![QTY]*[dbo_XYZ]![PRICE]),"")

Using this function, the Current Month column returns the total sales price (QTY * PRICE) for MM=6 (June). Everything works with that function except that the sales are not in a dollar format (which is not a big deal) and that this doesn't prompt the user to enter the month.

If there is a way to prompt the user to enter month instead of having to go into the expression to change the month, that would be ideal.

 

[Micron]

Current Month: IIf([MM]=" 6",([dbo_XYZ]![QTY]*[dbo_XYZ]![PRICE]),"")
If there is a way to prompt the user to enter month instead of having to go into the expression to change the month, that would be ideal.

I think if you replace the 6 with a parameter such as [Month Number] Access will prompt you for it. You haven't indicated if your expression is in the criteria grid or field heading, so I'm not positive. If it is in the field header and my suggestion does not work, try adding it to the criteria design grid as well.