ASCII File From Access Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following code works perfectly for a report that creates an ASCII file.
The problem centers on the last month of the year (December) and the first
month of the year (January). The ASCII file display the year 2003 for
January (when it should be the current year) and the month 0228 for January
(when it should be 131). Here's the code. Any help will be appreciated:

Private Function LastDayOfMonth() As String

ActForm = Me.Name
FuncName = "LastDayOfMonth"
SectNum = 1
On Error GoTo LastDayOfMonth_Err

Dim dDate As Date
Dim sDate1 As String

sDate1 = "01" & "/" & Format([txtdtEnd].Value + 30, "MMM") & "/" &
Year([txtdtEnd].Value)
dDate = CDate(sDate1) - 1

LastDayOfMonth = Format(dDate, "YYYYMMDD")

LastDayOfMonth_Exit:
Exit Function

LastDayOfMonth_Err:
iENum = Err
EMsg = Error
ret = Add2ErrorLog(iENum, EMsg, FuncName, SectNum, ActForm)
ret = ErrorMessage(iENum, EMsg, FuncName, SectNum, ActForm)
Resume Next

End Function
 
Are you trying to return the last day of the month for whatever date's in
txtdtEnd?

DateSerial(Year([txtdtEnd]), Month([txtdtEnd]) + 1, 0)
 
Doug,

The report generates the year 2003 for only the December report and 0228
as the month for the January report. All other months display the report
properly....meaning that the year is 2004 and the month is whatever month the
report indicates in the proper format (i.e. 0331, 0430 etc.). It's pretty
odd? Any insight will be greatly appreciated.

Vince
Douglas J. Steele said:
Are you trying to return the last day of the month for whatever date's in
txtdtEnd?

DateSerial(Year([txtdtEnd]), Month([txtdtEnd]) + 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Vincent said:
The following code works perfectly for a report that creates an ASCII file.
The problem centers on the last month of the year (December) and the first
month of the year (January). The ASCII file display the year 2003 for
January (when it should be the current year) and the month 0228 for January
(when it should be 131). Here's the code. Any help will be appreciated:

Private Function LastDayOfMonth() As String

ActForm = Me.Name
FuncName = "LastDayOfMonth"
SectNum = 1
On Error GoTo LastDayOfMonth_Err

Dim dDate As Date
Dim sDate1 As String

sDate1 = "01" & "/" & Format([txtdtEnd].Value + 30, "MMM") & "/" &
Year([txtdtEnd].Value)
dDate = CDate(sDate1) - 1

LastDayOfMonth = Format(dDate, "YYYYMMDD")

LastDayOfMonth_Exit:
Exit Function

LastDayOfMonth_Err:
iENum = Err
EMsg = Error
ret = Add2ErrorLog(iENum, EMsg, FuncName, SectNum, ActForm)
ret = ErrorMessage(iENum, EMsg, FuncName, SectNum, ActForm)
Resume Next

End Function
Click to expand...
Click to expand...
 
Doug,
I am trying to return the last day of the month..also the current year. As
ntoed in previous email, everthing works fine...except for the months of
December and January.

Douglas J. Steele said:
Are you trying to return the last day of the month for whatever date's in
txtdtEnd?

DateSerial(Year([txtdtEnd]), Month([txtdtEnd]) + 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Vincent said:
The following code works perfectly for a report that creates an ASCII file.
The problem centers on the last month of the year (December) and the first
month of the year (January). The ASCII file display the year 2003 for
January (when it should be the current year) and the month 0228 for January
(when it should be 131). Here's the code. Any help will be appreciated:

Private Function LastDayOfMonth() As String

ActForm = Me.Name
FuncName = "LastDayOfMonth"
SectNum = 1
On Error GoTo LastDayOfMonth_Err

Dim dDate As Date
Dim sDate1 As String

sDate1 = "01" & "/" & Format([txtdtEnd].Value + 30, "MMM") & "/" &
Year([txtdtEnd].Value)
dDate = CDate(sDate1) - 1

LastDayOfMonth = Format(dDate, "YYYYMMDD")

LastDayOfMonth_Exit:
Exit Function

LastDayOfMonth_Err:
iENum = Err
EMsg = Error
ret = Add2ErrorLog(iENum, EMsg, FuncName, SectNum, ActForm)
ret = ErrorMessage(iENum, EMsg, FuncName, SectNum, ActForm)
Resume Next

End Function
Click to expand...
Click to expand...
 
The function I gave you will give you the last day of the month for any
date, regardless of whether it's December or January.

?DateSerial(Year(#2003-12-26#),Month(#2003-12-26#)+1, 0)
2003-12-31
?DateSerial(Year(#2004-12-26#),Month(#2004-12-26#)+1, 0)
2004-12-31
?DateSerial(Year(#2003-01-26#),Month(#2003-01-26#)+1, 0)
2003-01-31
?DateSerial(Year(#2004-01-26#),Month(#2004-01-26#)+1, 0)
2004-01-31


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Vincent said:
Doug,
I am trying to return the last day of the month..also the current year. As
ntoed in previous email, everthing works fine...except for the months of
December and January.

Douglas J. Steele said:
Are you trying to return the last day of the month for whatever date's in
txtdtEnd?

DateSerial(Year([txtdtEnd]), Month([txtdtEnd]) + 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Vincent said:
The following code works perfectly for a report that creates an ASCII file.
The problem centers on the last month of the year (December) and the first
month of the year (January). The ASCII file display the year 2003 for
January (when it should be the current year) and the month 0228 for January
(when it should be 131). Here's the code. Any help will be appreciated:

Private Function LastDayOfMonth() As String

ActForm = Me.Name
FuncName = "LastDayOfMonth"
SectNum = 1
On Error GoTo LastDayOfMonth_Err

Dim dDate As Date
Dim sDate1 As String

sDate1 = "01" & "/" & Format([txtdtEnd].Value + 30, "MMM") & "/" &
Year([txtdtEnd].Value)
dDate = CDate(sDate1) - 1

LastDayOfMonth = Format(dDate, "YYYYMMDD")

LastDayOfMonth_Exit:
Exit Function

LastDayOfMonth_Err:
iENum = Err
EMsg = Error
ret = Add2ErrorLog(iENum, EMsg, FuncName, SectNum, ActForm)
ret = ErrorMessage(iENum, EMsg, FuncName, SectNum, ActForm)
Resume Next

End Function
Click to expand...
Click to expand...
Click to expand...
 
Thank you, Doug!

Douglas J. Steele said:
The function I gave you will give you the last day of the month for any
date, regardless of whether it's December or January.

?DateSerial(Year(#2003-12-26#),Month(#2003-12-26#)+1, 0)
2003-12-31
?DateSerial(Year(#2004-12-26#),Month(#2004-12-26#)+1, 0)
2004-12-31
?DateSerial(Year(#2003-01-26#),Month(#2003-01-26#)+1, 0)
2003-01-31
?DateSerial(Year(#2004-01-26#),Month(#2004-01-26#)+1, 0)
2004-01-31


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Vincent said:
Doug,
I am trying to return the last day of the month..also the current year. As
ntoed in previous email, everthing works fine...except for the months of
December and January.

Douglas J. Steele said:
Are you trying to return the last day of the month for whatever date's in
txtdtEnd?

DateSerial(Year([txtdtEnd]), Month([txtdtEnd]) + 1, 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



The following code works perfectly for a report that creates an ASCII
file.
The problem centers on the last month of the year (December) and the first
month of the year (January). The ASCII file display the year 2003 for
January (when it should be the current year) and the month 0228 for
January
(when it should be 131). Here's the code. Any help will be appreciated:

Private Function LastDayOfMonth() As String

ActForm = Me.Name
FuncName = "LastDayOfMonth"
SectNum = 1
On Error GoTo LastDayOfMonth_Err

Dim dDate As Date
Dim sDate1 As String

sDate1 = "01" & "/" & Format([txtdtEnd].Value + 30, "MMM") & "/" &
Year([txtdtEnd].Value)
dDate = CDate(sDate1) - 1

LastDayOfMonth = Format(dDate, "YYYYMMDD")

LastDayOfMonth_Exit:
Exit Function

LastDayOfMonth_Err:
iENum = Err
EMsg = Error
ret = Add2ErrorLog(iENum, EMsg, FuncName, SectNum, ActForm)
ret = ErrorMessage(iENum, EMsg, FuncName, SectNum, ActForm)
Resume Next

End Function
Click to expand...
Click to expand...
Click to expand...
 
Back
Top