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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|