Help with autopopulating a form

[chulo]

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

[June7]

Can always extract date parts with intrinsic date and format functions:

Month([dateentered]) will return number of month
Year([dateentered]) will return the year
Day([dateentered]) will the day
Format([dateentered], "mmm") will return 3 letter month name
Format([dateentered],"mmmm") will return full month name

To learn more, do a search on: date functions, format function

[Robeen]

I wouldn't do anything like that in the Table itself.

In your Form you can use this:

 
=Format([YourDateField],"mm-yy")

in the Data Tab -> Control Source for the text Box in which you want the MonthID to appear.

In the Query, you can do something like this in the Date field:

 
MonthID: Format([YourDateField],"mm-yy")

Hope this helps.

[chulo]

Hi all,

the suggestions of format function worked, thank you very much!!.

there is one one particular detail I need to mention. When I check, the MonthID autopopulates the month and year, however, the fiscal month for my company starts on the 22nd of every month and runs until the 21st of the next month (for example, February runs from 1/22 until 2/21 and march runs from 2/22 until 3/21). so, the issue I am having is that for an order that was processed on february 12th it reports for february, but for an order that was processed on february 24th, it reports for february also, but it needs to be march. How do I make it so it recognizes the change of months?

thanks for all your help.

Alex

[June7]

Check if the day falls before or after 21st and adjust date if needed.

Month(IIf(Day(dateentered)<=21, dateentered, DateAdd("m",1,dateentered)))

Format(IIf(Day(dateentered)<=21, dateentered, DateAdd("m",1,dateentered)), "mmm")

You might want to get familiar with all the date and string manipulation functions. http://www.techonthenet.com/excel/fo.../index_vba.php

[chulo]

Awesome, it worked. Thank you both.

[chulo]

I have one more thing to ask. Is it possible to do the same thing on the actual table? I would like to export all the data into an xcel database and do a pivot table based on the monthID, but when I do that, the month ID shows blank. I guess what we did was just for the form and queries. Can we do something similar for the table?

[chulo]

actually, I just figured it out. I just ran a query to pull up all the records and add the month ID to each row. Thanks