You can add the records programmatically for the next 20 years like this:
Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset
Dim i As Integer
Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
dt = #1/1/2005#
With rs
For i = 0 to 240
.AddNew
!TheDate = DateAdd("m", i, dt)
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
Or, you could programmatically fill the combo using a callback function in
its RowSourceType. It's quaint and esoteric if you can be bothered with it.
This example from the A97 help file shows how to fill a combo with Mondays.
You just set the RowSourceType to:
ListMondays
i.e. no equals, no function brackets, no arguments, and leave the RowSource
blank.
Function ListMondays(fld As Control, ID As Variant, row As Variant, col As
Variant, code As Variant) As Variant
'Modified from Access 97 help file.
Dim intOffset As Integer
Select Case code
Case acLBInitialize ' Initialize.
ListMondays = True
Case acLBOpen ' Open.
ListMondays = Timer ' Unique ID.
Case acLBGetRowCount ' Get rows.
ListMondays = 4
Case acLBGetColumnCount ' Get columns.
ListMondays = 1
Case acLBGetColumnWidth ' Get column width.
ListMondays = -1 ' Use default width.
Case acLBGetValue ' Get the data.
intOffset = Abs((9 - Weekday(Date)) Mod 7)
ListMondays = Format(Date + intOffset + 7 * row, "mmmm d")
End Select
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
hughess7 said:
I guess the only problem with this is that I would have to make sure I had
the date records in tblDates for each new year in there by Jan of the next
year, or just create say 10 years worth now.
Allen Browne said:
Okay, you're right that you need to use a real date for this.
The combo with the bound column zero-width would be the only way to get a
real date in the control but display something else to the user. It would
also mean there is no way that the user could choose a date that is not
the
first of the month.
Thanks for your response Allen.
The control is used to store the reporting month against records. The
user
selects the records in a continuous form via a tickbox then enters the
ReportingDate in the footer of the form and presses a button which runs
an
update query to write the ReportingDate to a field CBReportDate for
each
selected record. I originally just had it as a text field with JAN05,
FEB05
etc and had everything working ok. The user now wants me to change the
YTD
report though, currently it includes all records in a particular year
but
the
user wants it to report on all records up to the month entered. ie if
AUG05
is entered the YTD report should show JAN-AUG05 records only. I could
not
think of an easy way to do this without converting the CBReportDate
field
to
a date field so I could use the comparison operators.
Sue
:
Where are you going with this control?
If you are using it to filter a date/time field for a report, then you
want
to field to contain a real date so that your filter works reliably and
efficiently. Using the Format() function forces it to a string/text
type,
and will not give reliable results. Similary, trying to force it with
an
input mask just gets Access to treat the numbers as month and day of
the
current year, so that won't work either.
It's easy enough to use the AfterUpdate event of the control to change
the
date if Day([ReportingDate] <> 1.
You could set the Format property of the text box to:
mmmyyyy
so that Access shows the date in that format, but if the control is
unbound
there is still a chance that Access may not understand the data type
correctly.
The only way you could display one thing and yet treat it as something
different would be to use a combo (or list box), where the bound
column
is
hidden, and the displayed values are mmmyy whereas the actual value is
a
true date. This would mean creating a table of dates for the first of
each
month, so the combo's RowSource would be:
SELECT TheDate, Format(TheDate, "mmmyy") As ShowDate
FROM tblDate;
I have an unbound control on a form called ReportingDate. It
defaults
to
the
month previous to current month by the following in the default
value
=Format(DateAdd("m",-1,Date()),"mmmyy")
If the user types over this I want them to enter month+year only,
with
the
date defaulting to 01 of month eg Sep05 entered by user and system
stores
01/09/05 in field (the date is entered into a different field via an
update
query run from a button on the form).
I also have an input mask of >L<LL\-00;0;_ which lets the user enter
Month-Year (modification of the medium date mask).
I thought this was working but its not quite right.... if I enter
Sep-05
it
stores 05/09/05 and if I enter Sep-06 it stores 06/09/05.
I can get it to work by changing it to a medium date type with no
input
mask
and default to =DateSerial(Year(Date()),Month(Date())-1,1). I shrink
the
field so the user can only see month+Year but this is a bit fiddly.
Is there a better way? Brain no longer functioning today... ;-)
