Date problem

[Charlie O'Neill]

I have customer accounts that expire on various dates. Once a month a
mailing list must be printed. On a form I am using a control which is
formatted as a date picker. When a clerk enters a date in the control any
accounts => than the date will print. The problem is if the clerk enters a
date of 5/12/2010 any account dated between 5/1 and 5/11 does not print.

I want to change the control to a drop down showing Jan, Feb, Mar etc. When
the clerk picks May I want a hidden control 'Mail' to show 5/1/2010 and use
this control for my query.

I tried

If Me.Combo35.Value = "April" Then
Me.Mail.Value = #04/01/2010#
End If
If Me.Combo35.Value = "May" Then
Me.Mail.Value = #05/01/2010#
End If
If Me.Combo35.Value = "June" Then
Me.Mail.Value = #05/01/2010#

End If
End Sub

But of course when 2011 arrives I need to change the code to 2011.

I tried using the following

I created another hidden control named 'Year' with the following in the
control properties
=Right(Date(),4)

Private Sub Combo35_AfterUpdate()

If Me.Combo35.Value = "April" Then
Me.Mail.Value = "4/1/" & Me.Year.Value
End If
If Me.Combo35.Value = "May" Then
Me.Mail.Value = "5/1/" & Me.Year.Value
End If
If Me.Combo35.Value = "June" Then
Me.Mail.Value = "6/1/" & Me.Year.Value
End If
End Sub

Although it appears that the correct date is entered in the me.Mail control
I cannot print accounts with dates greater than 2010. 2011 accounts do not
print.

Charlie

 

[Tom van Stiphout]

On Wed, 21 Apr 2010 21:07:01 -0700, Charlie O'Neill

Why not write:
If Day(myDatePicker) <> 1 then Msgbox "Yo! Gotta pick a first day of
the month", vbExclamation

-Tom.
Microsoft Access MVP

 

[John W. Vinson]

I have customer accounts that expire on various dates. Once a month a
mailing list must be printed. On a form I am using a control which is
formatted as a date picker. When a clerk enters a date in the control any
accounts => than the date will print. The problem is if the clerk enters a
date of 5/12/2010 any account dated between 5/1 and 5/11 does not print.

I want to change the control to a drop down showing Jan, Feb, Mar etc. When
the clerk picks May I want a hidden control 'Mail' to show 5/1/2010 and use
this control for my query.

Consider having a combo box cboMonth with twelve rows and two fields:
MonthName and monthNo (January = 1, February = 2 and so on). Display the month
name but use the monthno as the bound column.

You could use a criterion

=DateSerial(Year(Date()), Forms!YourForm!cboMonth, 1) AND < DateSerial(Year(Date()), Forms!YourForm!cboMonth + 1, 1)

to get all dates in the selected month during the current year.

If you're worried that they'll pick February and expect it to pull February
2011, you can use somewhat more complex expressions to get the right year.

 

[John Spencer]

If you just want to force the date back to the first of the month, you could
use the dateSerial function.

DateSerial(Year(SomeDate]),Month(SomeDate),1)

IF you are referencing the control in the query.

DateSerial(Year(Forms![YourFormName]![YourControl]),Month(Forms![YourFormName]![YourControl]),1)

On the other hand if you just want the current month date to be used you could
use criteria like
DateSerial(Year(Date()),Month(Date()),1)
and not even bother to ask the clerk for a date.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Charlie O'Neill]

I thank all of you for some great suggestions. It seems simple when you know
what you are doing.