ASCII File From Access Report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vincent - 31 Dec 2004 00:55 GMT

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

Reply to this Message

Douglas J. Steele - 31 Dec 2004 01:14 GMT

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

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(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
[quoted text clipped - 29 lines]
>
> End Function

Reply to this Message

Vincent - 31 Dec 2004 01:37 GMT

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

> Are you trying to return the last day of the month for whatever date's in
> txtdtEnd?
[quoted text clipped - 36 lines]
> >
> > End Function

Reply to this Message

Vincent - 31 Dec 2004 01:47 GMT

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.

> Are you trying to return the last day of the month for whatever date's in
> txtdtEnd?
[quoted text clipped - 36 lines]
> >
> > End Function

Reply to this Message

Douglas J. Steele - 31 Dec 2004 12:39 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Doug,
> I am trying to return the last day of the month..also the current year. As
[quoted text clipped - 25 lines]
> > >
> > > sDate1 = "01" & "/" & Format([txtdtEnd].Value + 30, "MMM") & "/" &

> > > Year([txtdtEnd].Value)
> > > dDate = CDate(sDate1) - 1
[quoted text clipped - 12 lines]
> > >
> > > End Function

Reply to this Message

Vincent - 31 Dec 2004 14:49 GMT

Thank you, Doug!

> 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.
[quoted text clipped - 58 lines]
> > > >
> > > > End Function

Reply to this Message