Date Format Problems...

[dodgyone]

I am using a text file to import data from a text file.

The problem is that the date field variables do not seem to come through. I get Overflow (Error 6) if I try to import the date fields 'Dim ... As Date'. I have set the import table up correctly to Short Date to receive the dates but no joy. The dates import if the variables are set 'As String' but obviously the resulting date displayed is all wrong. Here's my code:

Dim db As DAO.Database, rst As DAO.Recordset
Dim no As Integer
Dim PNo As Long
Dim PNew, POld As Currency
Dim ADPerc As Single
Dim ad1, ad2, ad3, ad4, Sur, notes, rules, title, TypPen, mySQL As String
Dim AdDat, WPen, POldDate, PNewDate, PGranted As Date

Set db = CurrentDb()
Set rst = db.OpenRecordset("Import&quot

Open "C:\test.txt" For Input As #1

Do Until EOF(1)

Input #1, no, ad1, ad2, ad3, ad4, AdDat, WPen, Sur, PNew, notes, POld, POldDate, PNewDate, PGranted, PNo, ADPerc, rules, title, TypPen

rst.AddNew

rst.Fields("ID&quot = no
rst.Fields("Address1&quot = ad1
rst.Fields("Address2&quot = ad2
rst.Fields("Address3&quot = ad3
rst.Fields("Address4&quot = ad4
rst.Fields("AdjustDate&quot = AdDat
rst.Fields("WidowsPen&quot = WPen
rst.Fields("Surname&quot = Sur
rst.Fields("PensionNewAmount&quot = PNew
rst.Fields("Notes&quot = notes
rst.Fields("PensionOldAmount&quot = POld
rst.Fields("PensionOldDate&quot = POldDate
rst.Fields("PensionNewDate&quot = PNewDate
rst.Fields("PensionGranted&quot = PGranted
rst.Fields("PensionNo&quot = PNo
rst.Fields("AdjustmentPercentage&quot = ADPerc
rst.Fields("Rules&quot = rules
rst.Fields("TitleInitials&quot = title
rst.Fields("TypeOfPension&quot = TypPen

rst.Update

Loop


Set db = Nothing
Set rst = Nothing
Close #1

 

[bdmclennan]

Hi,

try formating the incoming date like this. You will need to change your dimensioned dates to string.


rst.Fields("AdjustDate&quot = cdate(format(cdate(adat),"DD/MMM/YYYY&quot)

Steps.
1. Import the date as string
2. Convert the string to a date format it to a short date then reconvert it back to a date to go into your table.

Hope this works for you.

Cheers

B.

 

[Junior1544]

on a side note...

PNew is being dim'd as a varient... that line should read as:
Dim PNew As Currency, POld As Currency
then they will both be set to currency...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.