How to create a list box with months

P

prana1

Hi all,

Fairly new to Access, taking a class, and trying to do something at my job.

I have an accounting spreadsheet for preparing invoices to be processed. I
imported it into Access without a hitch. One of the fields is 'month', and
usually it will be the current month that the invoice will be processed.
Sometimes I have to change that to the next month if I antipate the invoice
will be processed in the following month, like close to the 30th or 31st.

So I though a drop down would be my best bet. I'd like to have thethe
current month as a default value.

I'm trying this out, but keep running into errors, like it displaying the
date instead of a month. I also looked at
http://support.microsoft.com/kb/210604, functions for calculating date and
time, but that didn't solve my issue.

Can anyone help?

Thanks,

Eric in Florida
 
F

Franck

here few code
dateadd(d,1,mydatefield) = mydatefield + 1 day
dateadd(d,-1,mydatefield) = mydatefield - 1 day

dateadd(m,1,mydatefield) = mydatefield + 1 month
dateadd(m,-1,mydatefield) = mydatefield - 1 month

so i guess you see where im going to with this, just do a month() of
one of those solutions as this example
2007-04-04 you want last month
month(dateadd(m,-1,2007-04-04)) = 03
or
monthname(dateadd(m,-1,2007-04-04)) = March
 
I

Ian B

Hi Eric,

I have done exactly what you asked.

Please do following:

Add following as a module

Function GetMonthsRowSource()
Dim strMonth As String
Dim lngCount As Long
Dim dteDate As Date

dteDate = DateSerial(Year(Now()), Month(Now()), 1) 'set date as 1st
of month

For lngCount = 1 To 12 ' Repeat for 12 months
strMonth = strMonth & Format(dteDate, "mmmm") & ";"
dteDate = DateAdd("m", 1, dteDate)
Next lngCount

strMonth = Left(strMonth, Len(strMonth) - 1) 'get rid of final ";"
GetMonthsRowSource = strMonth
End Function

Then on your form on current write the following code (Replace
"SelectAmonth" with your field name) Every New record should reset the value)

Me.SelectAMonth.RowSource = GetMonthsRowSource()
Me.SelectAMonth.Value = Format(Now(), "mmmm")

This should make the list box show all the months in the year (Starting with
Current) then show the current month as the initial value,

Hope this helps

Ian B
 
S

Scottgem (MVP)

First, a spreadsheet is not the best format for Access. If you just
imported the sheet, its probably not normalized properly and that needs
to be done.

Second, doesn't the table have a date field? You should be keying off
that field instead of using a Month field. Otherwise you have redundant
data.

So please reply back and l;et us know how you are setup and we can
advise further.
 
P

prana1

Kind of need to take it from the top. I'm still pretty new - I've got an
access book here - for dumies actually, and it's a little too basic. where
would this go and would this show the actual month, like April, May, June,
and not the number 4, 5, or 6?
 
P

prana1

Hi Scott -
The spreadsheet imported pretty clean, I think. Just has plain text items
and some numbers, which represented dollar amounts. The months currently are
in one column, and are sequential and spelled out, January, February, March,
....

I see the long date format as an option in the 'General' tab of my table's
design view. That's where I am at and getting stuck. I am wondering if that
is what you referring to.
 
S

Scottgem (MVP)

Can you give us an idea of what your table looks like? What I was asking
is whether there is a date field in your record or is all you have a
column with month names?
 
P

prana1

OK, sorry for the delayed reply. I was waiting for an email to tell me I had
a reply. Never got one, so I just checked today.

OK, my table looks like the following - heading on 1st line, example
underneath.

Vendor Month Invoice_no. Invoice_Date Amount
--------- ------- ------------- --------------- ---------
AmEx April 134653 3/16/2007 $800.00


I made a lookup column and have the months listed there, but then the
dropdown comes down and they just list alphabetically, not by current month,
then the following month.

I hope that helps.

-Eric
 
P

prana1

Hey Ian -

It worked! Thank you for sharing this with me. This is pretty awesome to
see it working.

-Eric
 
S

Scottgem (MVP)

Ok, So the month may be different from the Invoice date. Is it always
the following month (i.e. April for March Invoices). If it is, then
there is NO reason to have a Month field. And that's what I was getting
at. If you submit an invoice for April for all orders done in March Then
just use a filter to return all March invoices.
 
P

prana1

I think you are right. I was copying the fields of an excel file that had
that for some reason.
 
Top