Date coming is 12/31/1899

[Stephen Lynch]

When I try and run my code, the append to table for the date field shows the
time only and a date of 12/31/1899.

I have tried everything, Format(), using a hard date in the code, etc.

The table field 6 is set to ShortDate for the import.

This is my line for the append in the loop: PD = Format(StartDate,
"mm/dd/yyyy")

Do I have my variable types wrong? URGGG!

Any help is appreciated.

STEVE



Function LoanAmort2(BeginPrincipal As Currency, InterestRate As Double,
Frequency As Double, TotPmts As Double, StartDate As Date)

Dim FVal As Double
Dim PayType As Double
Dim Payment As Currency
Dim Period As Integer
Dim P As Currency 'Principal Payment
Dim I As Currency 'Interest Payment
Dim EB As Currency 'Ending Balance
Dim RemainBalance As Currency
Dim PD As Date 'Payment Date


FVal = 0 ' Usually 0 for a loan.
If InterestRate > 1 Then InterestRate = InterestRate / 100 ' Ensure
proper form.
PayType = 0 'Payments made at the beginning of the month

Payment = Abs(-Pmt(InterestRate / Frequency, TotPmts, BeginPrincipal, FVal,
PayType))
RemainBalance = BeginPrincipal

'loop through each payment

For Period = 1 To TotPmts
EB = RemainBalance
P = PPmt(InterestRate / Frequency, Period, TotPmts, -BeginPrincipal,
FVal, PayType)
P = (Int((P + 0.005) * 100) / 100) ' Round principal.
I = Payment - P
I = (Int((I + 0.005) * 100) / 100) ' Round interest.
PD = Format(StartDate, "mm/dd/yyyy")
EB = RemainBalance - P
RemainBalance = EB

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Table1 (1,2,3,4,5,6) SELECT (" & Period & ")AS
1,(" & P & ")AS 2,(" & I & ")AS 3 ,(" & Payment & ")AS 4,(" & EB & ")AS 5,("
& PD & ")AS 6;"

Next

End Function

 

[Jeff Boyce]

Stephen

Access stores true Date/Time data type values as 'real numbers'. The digits
to the left of the decimal place are the days since some date in the late
1800's -- ?!? 12/31/1899 ?!? -- while the digits to the right are the
decimal fraction of a single day (i.e., the time).

It sounds like your values in that date/time field (better confirm that the
datatype is Date/Time) are all less than 1.0.

Be aware that using Format and ShortDate do NOT affect what is stored, only
how it is displayed.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Stephen Lynch]

Somehow it is not perceiving it as a date. If I change the last like in the
append query to #12/31/2008# AS 6 it appends the date fine.

So I am stuck for the last 3 hours on some little thing that I cannot figure
out.

 

[Jeff Boyce]

Stephen

If you can feed it #12/31/2008# and it works, then what you have been
feeding it is NOT a date, even if it looks like one. I can type the
following characters ... "12/31/2008" but that doesnt' mean Access knows it
to be a date.

Check the underlying data type.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

'loop through each payment

For Period = 1 To TotPmts
EB = RemainBalance
P = PPmt(InterestRate / Frequency, Period, TotPmts, -BeginPrincipal,
FVal, PayType)
P = (Int((P + 0.005) * 100) / 100) ' Round principal.
I = Payment - P
I = (Int((I + 0.005) * 100) / 100) ' Round interest.
PD = Format(StartDate, "mm/dd/yyyy")
EB = RemainBalance - P
RemainBalance = EB

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Table1 (1,2,3,4,5,6) SELECT (" & Period & ")AS
1,(" & P & ")AS 2,(" & I & ")AS 3 ,(" & Payment & ")AS 4,(" & EB & ")AS 5,("
& PD & ")AS 6;"

Change the line

PD = Format(StartDate, "mm/dd/yyyy")

to

PD = Format(StartDate, "\#mm/dd/yyyy\#")

and you'll be fine. As we both guessed, it's interpreting the expression in
your Insert statement as a division operation.

 

[Stephen Lynch]

FINALLY!!!!

Date fields with variables nee to be coded as such:

#" & PD & "# AS 7

 

[Stephen Lynch]

John:

Thanks,

I just posted another way. I hate it when I get stuck over a simple thing.

Thanks for the reply.

 

[Tony Toews [MVP]]

I just posted another way. I hate it when I get stuck over a simple thing.

That still happens to me occasionally. Then I blog about it and won't
ever do it again. Well, at least for a year or two.

It's kind of funny actually. I wrote a reply a few days ago with
several longish paragraphs on duplicate indexes not taking any extra
space. Then I decided to search for a KB article from five or six
years ago which also illustrated the problem. The first hit at
Google was a web page I had written earlier this year on that topic
complete with downloadable demo database. <sigh>

I knew that topic was familiar. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/