Nested IIF's or not

[ronwmon]

Have a table titled BillMonth, in the table are just the months, January, February, etc. I have a form with a cambo box to select the month and a text box where the user can enter the year. The date in the table that needs to be searched is always the first day of the month such as 01/01/2012. Internally our bill month is always the month after the month in the table. For example if our bill month is July we need to search for 06/01/2012.

I set up the query with a nested Iif statement that contains all 12 months and the related result depending on the month selected. I get an error saying the Iif statement is to complex.

How should I be handling this?

Thanks,

Ron--

 

[MajP]

Not sure why you would need a nested iif at all. Please provide you query so that we can guess what you are trying to accomplish. However I never ever use nested iifs. They are difficult to error check and hard to read. If you in fact need this functionality you are usually better off building a UDF.

 

[dhookom]

In addition to MajP's comments, I would not store month names. Month numbers are much more flexible and functional. You can easily pull the name of the month if you have the number. It's more difficult the other way around.

Duane
Hook'D on Access
MS Access MVP

 

[ronwmon]

I just need a way to create a date to use to search a query for data. I have the query that works great but I have manually entered the date when I need to run the query. Now I want a form that another employee can use to get the report. I want to make it as easy as possible for the employee to do this job. There are only two elements I need the employee to choose. They are the "File Date" and the "Company".

A comvbo box is a great way to make a choice, the choices are limited a easy to choose. The company list is simple and very easy to associate with the query. The file date is tricky. Iw would be simpler if the employee could choose the invoice date because this report is requested by our customer(s) after they get their invoice. The file date will always be the first day of the month preceeding the invoice date. The format for the date is standare "MM/DD/YYYY".

I Was trying to creat the file date by having the employee choose the invoice month, then enter the year in a text box. I have a combo box with the months and a text box where the year can be entered. I then need to look at the month and convert it to a two digit number that is one month earlier than the invoice month. Then I simply need to add the day that will always be 01 and the year from the text box. This seemed simple to me. I was wrong.

I am very flexible. The form is being created and is esay to change. I will consider all input.

If you see a good way for me to accomplish what I am trying to do I appreciate your input.

Thanks,

Ron--

 

[MajP]

I would build a combo for the month. It should have two columns with the first column hidden and bound. The first column is the month number the second is the display. Ex:

1 January
2 February

column count: 2
bound column: 1
column widths: 0";somewidth"

call it "cmboMonth"
then you have a textbox for the year. Assume it is called "txtYear"

Then on the form have a hidden text box "txtDate" with the following control source
=dateserial(txtYear,cmboMonth - 1,1)

that returns the first day of the previous month. Now you can use that in a query by referencing it as

forms!YourFormName!txtDate

Depending on how you use a date in a query you may have to wrap the value in "#