format date in table

[WaterGirl]

First time posting, so I hope you can help. I have been out of access about
5 years....

I would like to have a date [BudgetDate] in my table to be only = the first
day of month 01/01/08 (any month and year, the day must be the 1st)

I would also like to have a drop down box to use on a form of those dates.
I know I can not use a lookup to another table with dates. I tried. This is
a date that I would like someone to just pull down and pick, I can then set
for what date to and from also.

thanks

 

[Arvin Meyer [MVP]]

Put the following function in a module (saved with a different name than the
function)

Function FOM() As Variant
' PURPOSE : Returns the first day of the current month.
FOM = DateSerial(Year(Date), Month(Date), 1)
End Function

and set the DefaultValue property of the date textbox on your form to:

= FOM()

You can also just set the DefaultValue property of your table's date field
to:

DateSerial(Year(Date()),Month(Date()),1)

 

[Allen Browne]

Presumably you use a form to enter the budget dates, so you can use the
BeforeUpdate event of the form to check that the date is the first of the
month.

This kind of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
With Me.BudgetDate
If Day(.Value) <> 1 Then
.Value = .Value - Day(.Value) + 1
End If
End With
End Sub

To create a list of dates, you could create a table with one field of type
Number (marked as primary key), and enter the numbers 0 to 59. Name the
field (say) CountID, and save the table as tblCount. You can then use
something like this as the RowSource for your combo to get all the dates for
last year, the year before, this year, and the next 2 years):

SELECT DateAdd("m", [CountID], DateSerial(Year(Date()) - 2, 1,1)) AS TheDate
FROM tblCount ORDER BY CountID;

 

[Tom van Stiphout]

On Mon, 19 Jan 2009 18:57:01 -0800, WaterGirl

Why not create a new table with one column: ValidBudgetDates, and fill
it with your dates?
Alternatively you can use a RowSourceType of Value List for your
dropdown rather than a Table/Query, and set that property to
myPublicFunction()
and in this function you concatenate a few years worth of dates and
return it as a string.
(of course you need to change object names as-needed)

-Tom.
Microsoft Access MVP