Hello guys,
I need help with my access database. First of all, let me tell you that I just learned how to use access so I am not that proficient on it. I don’t know anything about VBA or SQL so if the solution can avoid that route that would be great, if not, I understand.
I have a database that tracks sales. I have my table, a form to enter the sales and queries to find out how I am doing and track my orders until fulfillment.(I have attached the database on this thread so you can see)
I need to figure out how to label each order with the month they will be reported too. This will help when I set up queries asking for sales for a specific month and for when doing pivot tables. The problem I am having is that the fiscal month for my company is from the 22nd until the 21st of the next month (from February 22nd until march 21st for example). So an order approved on February 15th is reported in the month of February, and an order approved on February 25th is reported for the month of march.
On my form, I have a field called “date entered”. That is the day where I entered the sale into the system. 2 days later the order is accepted and 3 or 4 days later after acceptance, the order is delivered. I have the field called ‘dateentered’ in the table and in the form, which is set up as a default for today’s date with the “date()” function. I would like to have the field MonthID on my table and form to autopopulate the month the order belongs to (February, march, april, etc…) based on the date I set on “order approved” field in my form. How could I accomplish that?
I have thought of a query using the “iif” function to put it in the default value:
Iif(1/22/2012<=[dateentered]<=2/21/2012,Feb12,(Iif(2/22/2012<=[dateentered]<=3/21/2012,Mar12,(Iif(3/22/2012<=[dateentered]<=4/21/2012,Apr12,(Iif(4/22/2012<=[dateentered]<=5/21/2012,May12,XXX)))))))
I stopped at May but I can continue on and on. Of course, this didn’t work somehow. Like I said, I am new at this so there might be an easier way to do this. Again, I am not good with VBA or SQL so if we could avoid that great, if not that’s ok.
Thanks in advanced.
Alex