A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Date problem



 
 
Thread Tools Display Modes
  #1  
Old April 22nd, 2010, 05:07 AM posted to microsoft.public.access
Charlie O'Neill
external usenet poster
 
Posts: 19
Default Date problem

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
  #2  
Old April 22nd, 2010, 05:15 AM posted to microsoft.public.access
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Date problem

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

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

-Tom.
Microsoft Access MVP


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

  #3  
Old April 22nd, 2010, 06:02 AM posted to microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Date problem

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

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 W. Vinson [MVP]
  #4  
Old April 22nd, 2010, 03:18 PM posted to microsoft.public.access
John Spencer
external usenet poster
 
Posts: 7,815
Default Date problem

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 wrote:
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

  #5  
Old April 22nd, 2010, 06:11 PM posted to microsoft.public.access
Charlie O'Neill
external usenet poster
 
Posts: 19
Default Date problem

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

"John W. Vinson" wrote:

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

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 W. Vinson [MVP]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:00 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.