HELP!!! Object Variable or Block Variable Not Set

[Bill]

Hi,

I'm new on Access XP. I'm able to import the text file in the table. I'm
trying to convert the text file (date) to Access Table (date). In the
table, I set "Date/Time" but it won't allow me and the message said "Data
Type Conversion Type". I change to "Text" and it works. I don't like the
text file (Date). It look like "19980223" but I want to change to
"DD/MM/YYYY".

Is there a way to set this code (below) that set a default to "DD/MM/YYYY"?
Same problem with "Number".

rs![DATE] = Mid(StrNAME, 166, 8)

Your help would be much appreciated.
Thanks

 

[Wayne Morgan]

Try using the DateSerial function to change the string of numbers into a
date. You can format it later, for now, just get a date value into the
field. Also, Date isn't a good name for a field, it is a reserved word and
has the potential to cause you problems.

strTempDate = Mid(StrNAME, 166, 8)
rs![DATE] = DateSerial(Left(strTempDate, 4), Mid(strTempDate, 5, 2),
Right(strTempDate, 2))

 

[Guest]

Wayne is correct, Bill. What may also be useful to know is that dates in Jet
are not carried as text. They are stored as IEEE 64-bit (8-byte)
floating-point numbers that represent dates ranging from 1 January 100 to 31
December 9999 and times from 0:00:00 to 23:59:59. Access deals with
converting them to something we humans can understand. For displaying dates,
you need to read up on the Format function.

Try using the DateSerial function to change the string of numbers into a
date. You can format it later, for now, just get a date value into the
field. Also, Date isn't a good name for a field, it is a reserved word and
has the potential to cause you problems.

strTempDate = Mid(StrNAME, 166, 8)
rs![DATE] = DateSerial(Left(strTempDate, 4), Mid(strTempDate, 5, 2),
Right(strTempDate, 2))

--
Wayne Morgan
MS Access MVP

Hi,

I'm new on Access XP. I'm able to import the text file in the table.
I'm trying to convert the text file (date) to Access Table (date). In
the table, I set "Date/Time" but it won't allow me and the message said
"Data Type Conversion Type". I change to "Text" and it works. I don't
like the text file (Date). It look like "19980223" but I want to change
to "DD/MM/YYYY".

Is there a way to set this code (below) that set a default to
"DD/MM/YYYY"? Same problem with "Number".

rs![DATE] = Mid(StrNAME, 166, 8)

Your help would be much appreciated.
Thanks

 

[PC Datasheet]

In your text date, is the year always 4 characters, month 2 characters and
day 2 characters? If Yes, try this:
Add a new field named ConvertedDate to your table and set the data type as
Date/Time. Create an update query based on your table and include your Date
field and the ConvertedDate field. Assuming your text date field is named
MyDate, put the following expression in the Update To line under
ConvertedDate:
DateSerial(Val(Left([MyDate],4)),Val(Mid([MyDate],5,2)),Val(Right([MyDate],2)))

When you run this query, ConvertedDate should now be your dates in Date/Time
format.