Error

Hi. I have a SQL backend and an Access data project front end. Each user has
a local copy of the front end on their computer. I have a function set up to
read data off a CD and import it to the system -- this function works on all
computers except one. On the problematic machine, I keep getting Error 8152
-
String or binary data would be truncated. Here's the line of code that
triggers the error:
DoCmd.RunSQL "UPDATE [Table1] SET [Pub Date] = SUBSTRING([Pub Date], 1, 2) +
'/01/' + SUBSTRING([Pub Date], 3, 4)"
What is happening here is that a number of date fields come into the system
in mm/yyyy format. This causes some problems, so I loaded them into a temp
table as a string, and am inserting 01 for the day in the middle. Once that'
s
complete I'll cast it into a datetime and append the records to the live
data. As I said, this all works for every computer but one. The machine in
question has all the correct Access references, and the user is a member of
the correct group and has all the privileges he needs. Is anyone familiar
with this error? Why would it trigger for one user and not the others?mike wrote:
> Hi. I have a SQL backend and an Access data project front end. Each
> user has a local copy of the front end on their computer. I have a
> function set up to read data off a CD and import it to the system --
> this function works on all computers except one. On the problematic
> machine, I keep getting Error 8152 - String or binary data would be
> truncated. Here's the line of code that triggers the error:
> DoCmd.RunSQL "UPDATE [Table1] SET [Pub Date] = SUBSTRING([Pub Date],
> 1, 2) + '/01/' + SUBSTRING([Pub Date], 3, 4)"
> What is happening here is that a number of date fields come into the
> system in mm/yyyy format. This causes some problems, so I loaded them
> into a temp table as a string, and am inserting 01 for the day in the
> middle. Once that's complete I'll cast it into a datetime and append
> the records to the live data. As I said, this all works for every
> computer but one. The machine in question has all the correct Access
> references, and the user is a member of the correct group and has all
> the privileges he needs. Is anyone familiar with this error? Why
> would it trigger for one user and not the others?
The date format you are using is not a portable format. For a date-only
value, you should use:
YYYYMMDD
David Gugick
Imceda Software
www.imceda.com|||David: Thanks for your response, but the data I'm trying to manipulate is of
type varchar. I handle the date conversion later. Also, the code works just
fine on all the computers except one.
I did some more investigating and discovered that when I try to load data
using the problem computer, the system creates and saves copies of the temp
tables I use for data import and manipulation. All other users re-use the
previously defined tables. The new user-specific temp tables default to data
type nvarchar, rather than varchar. When I switched the appropriate fields
from nvarchar to varchar the code worked fine. Go figure.
"David Gugick" wrote:

> mike wrote:
> The date format you are using is not a portable format. For a date-only
> value, you should use:
> YYYYMMDD
> --
> David Gugick
> Imceda Software
> www.imceda.com
>