Solved

Define a date in a variable with changing year part

Posted on 2007-11-30
5
318 Views
Last Modified: 2013-11-25
When I first did this and hard-coded 12/31/2008 in the if staement, it worked fine.  But I need this to update for 12/31 and a new year.  So, how do I tell the variable that add the year from my form?  I need either the Year(maxDate) for 12/31 & FORMS!frmSalesPayroll.txtForecastYear.  I think it has something to do with the pounds but I am not sure as everything I tried still does not work.
Public Sub AppendCalendarDate()
On Error GoTo ErrorHandler
Dim addDate As Date, maxDate As Date, i As Integer, strAppendDate As String, dteEOY As Date
 
maxDate = DMax("CalDate", "tblSalesDaily445_and_Calendar")
dteEOY = #12/31/Year(maxDate)#
If maxDate < dteEOY Then
    i = DatePart("d", maxDate) + 1
Debug.Print maxDate
Debug.Print i
Debug.Print dteEOY
 
 
For i = i To 31
strAppendDate = "INSERT INTO tblSalesDaily445_and_Calendar ( ForecastYear, CalDate, CalMonth ) " & _
    "SELECT DISTINCT Year([CalDate]) AS " & _
    "ForecastYear, #12/" & i & "/" & Forms!frmSalesPayroll.txtForecastYear & "# AS CalDate, " & _
    "MonthName(Month([CalDate]),True) AS CalMonth " & _
    "FROM tblSalesDaily445_and_Calendar "
    
DoCmd.RunSQL strAppendDate
Next i
End If
Exit_ErrorHandler:
Exit Sub
ErrorHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_ErrorHandler
    
End Sub

Open in new window

0
Comment
Question by:ssmith94015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 14

Expert Comment

by:RDWaibel
ID: 20383887
Dim sYear as string
sYear = Year(maxDate)
dteEOY = cdate("12/31/" & sYear)

there ya go!
0
 
LVL 77

Expert Comment

by:peter57r
ID: 20383894
dteEOY = Dateserial(Year(maxDate),12,31)
0
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 500 total points
ID: 20383906
How about...
dteEOY = CDate("12/31/" & Year(maxDate))

Open in new window

0
 

Author Comment

by:ssmith94015
ID: 20383939
Rick had the simplest, thank you all however.
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20384039
My thanks as well ssmith.  Just glad you liked the approach.  As for anyone who read this in the future though it is worth pointing out that everyone offered a valid and accurate solution, there was simply a multitude of ways that this issue could be solved.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question