Date problem
in [General]
|
Prev: Do Long Names Corrupt?
Next: RR
From: Charlie O'Neill on 22 Apr 2010 00:07 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
From: Tom van Stiphout on 22 Apr 2010 00:15 On Wed, 21 Apr 2010 21:07:01 -0700, Charlie O'Neill <CharlieONeill(a)discussions.microsoft.com> 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
From: John W. Vinson on 22 Apr 2010 01:02 On Wed, 21 Apr 2010 21:07:01 -0700, Charlie O'Neill <CharlieONeill(a)discussions.microsoft.com> 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]
From: raskew via AccessMonster.com on 22 Apr 2010 08:08 Hi - This little function will return the month number when provided a complete or partial month name: Public Function RtnMonthNum(pstrMonName As String) As Integer '******************************************* 'Purpose: Returns month number (1 - 12) ' when provided a complete or partial ' month name. 'Coded by: raskew 'Input: From debug (immediate) window: ' 1) ? RtnMonthNum("April") ' 1) ? RtnMonthNum("Sep") 'Output: 1) 4 ' 2) 9 '******************************************* Dim strHold As String Dim strMonth As String Dim intMonth As Integer strMonth = "JanFebMarAprMayJunJulAugSepOctNovDec" strHold = Left(pstrMonName, 3) intMonth = InStr(strMonth, strHold) RtnMonthNum = intMonth \ 3 + 1 End Function HTH - Bob John W. Vinson 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 >[quoted text clipped - 5 lines] >>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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access/201004/1
From: John Spencer on 22 Apr 2010 10:18
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 |