Update Query Fails Over 127 characters

D

Don Reid

I am running an update query to update a field from an
unbound control on the form.

When I execute the query, if my text string in the text
box is longer than 127 characters (ie 128 characters) the
query fails with an "Invalid Argument" error message.

Is there any way I can manage this, so I can actually
update more text?
 
K

Ken Snell

Post the SQL of the update query. Also, check the field size of the field
into which you're trying to put this updated information. Is the length set
to 127 characters?
 
D

Don Reid

Here is the SQL query:

UPDATE tblService SET tblService.SupervisingOfficer =
[Forms]![frmUpdateServiceData]![txtNewSupervisor],
tblService.ServiceName = [Forms]![frmUpdateServiceData]!
[txtNewServiceName], tblService.ServiceType = [Forms]!
[frmUpdateServiceData]![txtNewServiceType],
tblService.Attn = [Forms]![frmUpdateServiceData]!
[txtNewAttn], tblService.LiaisonOfficer = [Forms]!
[frmUpdateServiceData]![txtNewLiaison], tblService.Copy1 =
[Forms]![frmUpdateServiceData]![txtNewCopy1],
tblService.Copy2 = [Forms]![frmUpdateServiceData]!
[txtNewCopy2], tblService.Team = [Forms]!
[frmUpdateServiceData]![txtNewTeam], tblService.Comments =
[Forms]![frmUpdateServiceData]![txtNewComments],
tblService.SendMail = [Forms]![frmUpdateServiceData]!
[chkNewSendMail]
WHERE (((tblService.ServiceID)=[Forms]![frmViewAll]!
[ServiceID]));

The field I am writing back to is a memo field, and I have
no problem storing the data if I enter through the table.
 
K

Ken Snell

First guess.... the total length of the query's update record is too long
(too many bytes) and your textbox is finding the limit of the record.

You could break the query into two separate queries, and run one after the
other.

--
Ken Snell
<MS ACCESS MVP>

Don Reid said:
Here is the SQL query:

UPDATE tblService SET tblService.SupervisingOfficer =
[Forms]![frmUpdateServiceData]![txtNewSupervisor],
tblService.ServiceName = [Forms]![frmUpdateServiceData]!
[txtNewServiceName], tblService.ServiceType = [Forms]!
[frmUpdateServiceData]![txtNewServiceType],
tblService.Attn = [Forms]![frmUpdateServiceData]!
[txtNewAttn], tblService.LiaisonOfficer = [Forms]!
[frmUpdateServiceData]![txtNewLiaison], tblService.Copy1 =
[Forms]![frmUpdateServiceData]![txtNewCopy1],
tblService.Copy2 = [Forms]![frmUpdateServiceData]!
[txtNewCopy2], tblService.Team = [Forms]!
[frmUpdateServiceData]![txtNewTeam], tblService.Comments =
[Forms]![frmUpdateServiceData]![txtNewComments],
tblService.SendMail = [Forms]![frmUpdateServiceData]!
[chkNewSendMail]
WHERE (((tblService.ServiceID)=[Forms]![frmViewAll]!
[ServiceID]));

The field I am writing back to is a memo field, and I have
no problem storing the data if I enter through the table.

-----Original Message-----
Post the SQL of the update query. Also, check the field size of the field
into which you're trying to put this updated information. Is the length set
to 127 characters?
Click to expand...
Click to expand...
 
D

Don Reid

I had a similar thought, and just tried it. I created a
second query that just does the text box -- and it gives
me exactly the same results. At the 128th character, the
query fails and the "Invalid argument" error message is
returned.
-----Original Message-----
First guess.... the total length of the query's update record is too long
(too many bytes) and your textbox is finding the limit of the record.

You could break the query into two separate queries, and run one after the
other.

--
Ken Snell
<MS ACCESS MVP>

Don Reid said:
Here is the SQL query:

UPDATE tblService SET tblService.SupervisingOfficer =
[Forms]![frmUpdateServiceData]![txtNewSupervisor],
tblService.ServiceName = [Forms]![frmUpdateServiceData]!
[txtNewServiceName], tblService.ServiceType = [Forms]!
[frmUpdateServiceData]![txtNewServiceType],
tblService.Attn = [Forms]![frmUpdateServiceData]!
[txtNewAttn], tblService.LiaisonOfficer = [Forms]!
[frmUpdateServiceData]![txtNewLiaison], tblService.Copy1 =
[Forms]![frmUpdateServiceData]![txtNewCopy1],
tblService.Copy2 = [Forms]![frmUpdateServiceData]!
[txtNewCopy2], tblService.Team = [Forms]!
[frmUpdateServiceData]![txtNewTeam], tblService.Comments =
[Forms]![frmUpdateServiceData]![txtNewComments],
tblService.SendMail = [Forms]![frmUpdateServiceData]!
[chkNewSendMail]
WHERE (((tblService.ServiceID)=[Forms]![frmViewAll]!
[ServiceID]));

The field I am writing back to is a memo field, and I have
no problem storing the data if I enter through the table.

-----Original Message-----
Post the SQL of the update query. Also, check the field size of the field
into which you're trying to put this updated
Click to expand...
information.
Is the length set
to 127 characters?
Click to expand...
I am running an update query to update a field from an
unbound control on the form.

When I execute the query, if my text string in the text
box is longer than 127 characters (ie 128 characters) the
query fails with an "Invalid Argument" error message.

Is there any way I can manage this, so I can actually
update more text?
Click to expand...
Click to expand...


.
Click to expand...
 
K

Ken Snell

Is the table's record exceeding 2000 bytes when the textbox contains that
128th character? Take a look at how many fields there are in the table, add
up the sizes of the fields, and see.

--
Ken Snell
<MS ACCESS MVP>

Don Reid said:
I had a similar thought, and just tried it. I created a
second query that just does the text box -- and it gives
me exactly the same results. At the 128th character, the
query fails and the "Invalid argument" error message is
returned.
-----Original Message-----
First guess.... the total length of the query's update record is too long
(too many bytes) and your textbox is finding the limit of the record.

You could break the query into two separate queries, and run one after the
other.

--
Ken Snell
<MS ACCESS MVP>

Don Reid said:
Here is the SQL query:

UPDATE tblService SET tblService.SupervisingOfficer =
[Forms]![frmUpdateServiceData]![txtNewSupervisor],
tblService.ServiceName = [Forms]![frmUpdateServiceData]!
[txtNewServiceName], tblService.ServiceType = [Forms]!
[frmUpdateServiceData]![txtNewServiceType],
tblService.Attn = [Forms]![frmUpdateServiceData]!
[txtNewAttn], tblService.LiaisonOfficer = [Forms]!
[frmUpdateServiceData]![txtNewLiaison], tblService.Copy1 =
[Forms]![frmUpdateServiceData]![txtNewCopy1],
tblService.Copy2 = [Forms]![frmUpdateServiceData]!
[txtNewCopy2], tblService.Team = [Forms]!
[frmUpdateServiceData]![txtNewTeam], tblService.Comments =
[Forms]![frmUpdateServiceData]![txtNewComments],
tblService.SendMail = [Forms]![frmUpdateServiceData]!
[chkNewSendMail]
WHERE (((tblService.ServiceID)=[Forms]![frmViewAll]!
[ServiceID]));

The field I am writing back to is a memo field, and I have
no problem storing the data if I enter through the table.


-----Original Message-----
Post the SQL of the update query. Also, check the field
size of the field
into which you're trying to put this updated information.
Is the length set
to 127 characters?

--
Ken Snell
<MS ACCESS MVP>


I am running an update query to update a field from an
unbound control on the form.

When I execute the query, if my text string in the text
box is longer than 127 characters (ie 128 characters)
the
query fails with an "Invalid Argument" error message.

Is there any way I can manage this, so I can actually
update more text?
Click to expand...


.
Click to expand...
Click to expand...
 
D

Don Reid

I don't thinks so - -I have fields Autonumber (4 bytes),
then text fields of 45, 60, 4, 30, 50, 50, 50, 25
characters, a bit field (yes no) and the memo field. --
which should be (if I calculate correctly) less than 2000
bytes, right?

-----Original Message-----
Is the table's record exceeding 2000 bytes when the textbox contains that
128th character? Take a look at how many fields there are in the table, add
up the sizes of the fields, and see.

--
Ken Snell
<MS ACCESS MVP>

Don Reid said:
I had a similar thought, and just tried it. I created a
second query that just does the text box -- and it gives
me exactly the same results. At the 128th character, the
query fails and the "Invalid argument" error message is
returned.
-----Original Message-----
First guess.... the total length of the query's update record is too long
(too many bytes) and your textbox is finding the limit
Click to expand...
of
the record.
You could break the query into two separate queries,
Click to expand...
and
run one after the
other.

--
Ken Snell
<MS ACCESS MVP>

Here is the SQL query:

UPDATE tblService SET tblService.SupervisingOfficer =
[Forms]![frmUpdateServiceData]![txtNewSupervisor],
tblService.ServiceName = [Forms]! [frmUpdateServiceData]!
[txtNewServiceName], tblService.ServiceType = [Forms]!
[frmUpdateServiceData]![txtNewServiceType],
tblService.Attn = [Forms]![frmUpdateServiceData]!
[txtNewAttn], tblService.LiaisonOfficer = [Forms]!
[frmUpdateServiceData]![txtNewLiaison], tblService.Copy1 =
[Forms]![frmUpdateServiceData]![txtNewCopy1],
tblService.Copy2 = [Forms]![frmUpdateServiceData]!
[txtNewCopy2], tblService.Team = [Forms]!
[frmUpdateServiceData]![txtNewTeam], tblService.Comments =
[Forms]![frmUpdateServiceData]![txtNewComments],
tblService.SendMail = [Forms]![frmUpdateServiceData]!
[chkNewSendMail]
WHERE (((tblService.ServiceID)=[Forms]![frmViewAll]!
[ServiceID]));

The field I am writing back to is a memo field, and I have
no problem storing the data if I enter through the table.


-----Original Message-----
Post the SQL of the update query. Also, check the field
size of the field
into which you're trying to put this updated information.
Is the length set
to 127 characters?

--
Ken Snell
<MS ACCESS MVP>


I am running an update query to update a field
Click to expand...
from
an
unbound control on the form.

When I execute the query, if my text string in the text
box is longer than 127 characters (ie 128 characters)
the
query fails with an "Invalid Argument" error message.

Is there any way I can manage this, so I can actually
update more text?


.
Click to expand...
Click to expand...


.
Click to expand...
 
K

Ken Snell

Hmmmm.....

If you would like, zip up an example of the database and email it to me
(remove this is not real from my reply email address) and I'll take a look
to see what I might find.

--
Ken Snell
<MS ACCESS MVP>

Don Reid said:
I don't thinks so - -I have fields Autonumber (4 bytes),
then text fields of 45, 60, 4, 30, 50, 50, 50, 25
characters, a bit field (yes no) and the memo field. --
which should be (if I calculate correctly) less than 2000
bytes, right?

-----Original Message-----
Is the table's record exceeding 2000 bytes when the textbox contains that
128th character? Take a look at how many fields there are in the table, add
up the sizes of the fields, and see.

--
Ken Snell
<MS ACCESS MVP>

Don Reid said:
I had a similar thought, and just tried it. I created a
second query that just does the text box -- and it gives
me exactly the same results. At the 128th character, the
query fails and the "Invalid argument" error message is
returned.

-----Original Message-----
First guess.... the total length of the query's update
record is too long
(too many bytes) and your textbox is finding the limit of
the record.

You could break the query into two separate queries, and
run one after the
other.

--
Ken Snell
<MS ACCESS MVP>

Here is the SQL query:

UPDATE tblService SET tblService.SupervisingOfficer =
[Forms]![frmUpdateServiceData]![txtNewSupervisor],
tblService.ServiceName = [Forms]! [frmUpdateServiceData]!
[txtNewServiceName], tblService.ServiceType = [Forms]!
[frmUpdateServiceData]![txtNewServiceType],
tblService.Attn = [Forms]![frmUpdateServiceData]!
[txtNewAttn], tblService.LiaisonOfficer = [Forms]!
[frmUpdateServiceData]![txtNewLiaison],
tblService.Copy1 =
[Forms]![frmUpdateServiceData]![txtNewCopy1],
tblService.Copy2 = [Forms]![frmUpdateServiceData]!
[txtNewCopy2], tblService.Team = [Forms]!
[frmUpdateServiceData]![txtNewTeam],
tblService.Comments =
[Forms]![frmUpdateServiceData]![txtNewComments],
tblService.SendMail = [Forms]![frmUpdateServiceData]!
[chkNewSendMail]
WHERE (((tblService.ServiceID)=[Forms]![frmViewAll]!
[ServiceID]));

The field I am writing back to is a memo field, and I
have
no problem storing the data if I enter through the
table.


-----Original Message-----
Post the SQL of the update query. Also, check the field
size of the field
into which you're trying to put this updated
information.
Is the length set
to 127 characters?

--
Ken Snell
<MS ACCESS MVP>


I am running an update query to update a field from
an
unbound control on the form.

When I execute the query, if my text string in the
text
box is longer than 127 characters (ie 128 characters)
the
query fails with an "Invalid Argument" error message.

Is there any way I can manage this, so I can actually
update more text?


.
Click to expand...


.
Click to expand...
Click to expand...
 
G

Guest

I think I got it.

I was trying to run the query like this,

DoCmd.OpenQuery strQuery, acNormal, acEdit
and instead I declared a string and wrote the SQL into the
string, and called the string. That solved the problem,
but I still don't know why.

Don
-----Original Message-----
Hmmmm.....

If you would like, zip up an example of the database and email it to me
(remove this is not real from my reply email address) and I'll take a look
to see what I might find.

--
Ken Snell
<MS ACCESS MVP>

I don't thinks so - -I have fields Autonumber (4 bytes),
then text fields of 45, 60, 4, 30, 50, 50, 50, 25
characters, a bit field (yes no) and the memo field. --
which should be (if I calculate correctly) less than 2000
bytes, right?

-----Original Message-----
Is the table's record exceeding 2000 bytes when the textbox contains that
128th character? Take a look at how many fields there
Click to expand...
are
in the table, add
up the sizes of the fields, and see.

--
Ken Snell
<MS ACCESS MVP>

I had a similar thought, and just tried it. I created a
second query that just does the text box -- and it gives
me exactly the same results. At the 128th character, the
query fails and the "Invalid argument" error message is
returned.

-----Original Message-----
First guess.... the total length of the query's update
record is too long
(too many bytes) and your textbox is finding the
Click to expand...
limit
of
the record.

You could break the query into two separate queries, and
run one after the
other.

--
Ken Snell
<MS ACCESS MVP>

Here is the SQL query:

UPDATE tblService SET tblService.SupervisingOfficer =
[Forms]![frmUpdateServiceData]![txtNewSupervisor],
tblService.ServiceName = [Forms]! [frmUpdateServiceData]!
[txtNewServiceName], tblService.ServiceType = [Forms]!
[frmUpdateServiceData]![txtNewServiceType],
tblService.Attn = [Forms]![frmUpdateServiceData]!
[txtNewAttn], tblService.LiaisonOfficer = [Forms]!
[frmUpdateServiceData]![txtNewLiaison],
tblService.Copy1 =
[Forms]![frmUpdateServiceData]![txtNewCopy1],
tblService.Copy2 = [Forms]![frmUpdateServiceData]!
[txtNewCopy2], tblService.Team = [Forms]!
[frmUpdateServiceData]![txtNewTeam],
tblService.Comments =
[Forms]![frmUpdateServiceData]![txtNewComments],
tblService.SendMail = [Forms]! [frmUpdateServiceData]!
[chkNewSendMail]
WHERE (((tblService.ServiceID)=[Forms]! [frmViewAll]!
[ServiceID]));

The field I am writing back to is a memo field, and I
have
no problem storing the data if I enter through the
table.


-----Original Message-----
Post the SQL of the update query. Also, check the field
size of the field
into which you're trying to put this updated
information.
Is the length set
to 127 characters?

--
Ken Snell
<MS ACCESS MVP>


I am running an update query to update a field from
an
unbound control on the form.

When I execute the query, if my text string in the
text
box is longer than 127 characters (ie 128 characters)
the
query fails with an "Invalid Argument" error message.

Is there any way I can manage this, so I can actually
update more text?


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
K

Ken Snell

Don -

I just got home from work and haven't had chance to look at the database
that you emailed to me. But I will so that I can see if I can shed more
light on your results.

Just for my clarification, can you post the original code and then your new
code? The newsreader seems to have concatenated part of your message and I
can't tell which part is what you were doing and which part is what you're
now doing.

--
Ken Snell
<MS ACCESS MVP>

I think I got it.

I was trying to run the query like this,

DoCmd.OpenQuery strQuery, acNormal, acEdit
and instead I declared a string and wrote the SQL into the
string, and called the string. That solved the problem,
but I still don't know why.

Don
-----Original Message-----
Hmmmm.....

If you would like, zip up an example of the database and email it to me
(remove this is not real from my reply email address) and I'll take a look
to see what I might find.

--
Ken Snell
<MS ACCESS MVP>

I don't thinks so - -I have fields Autonumber (4 bytes),
then text fields of 45, 60, 4, 30, 50, 50, 50, 25
characters, a bit field (yes no) and the memo field. --
which should be (if I calculate correctly) less than 2000
bytes, right?


-----Original Message-----
Is the table's record exceeding 2000 bytes when the
textbox contains that
128th character? Take a look at how many fields there are
in the table, add
up the sizes of the fields, and see.

--
Ken Snell
<MS ACCESS MVP>

I had a similar thought, and just tried it. I created a
second query that just does the text box -- and it gives
me exactly the same results. At the 128th character,
the
query fails and the "Invalid argument" error message is
returned.

-----Original Message-----
First guess.... the total length of the query's update
record is too long
(too many bytes) and your textbox is finding the limit
of
the record.

You could break the query into two separate queries,
and
run one after the
other.

--
Ken Snell
<MS ACCESS MVP>

Here is the SQL query:

UPDATE tblService SET tblService.SupervisingOfficer =
[Forms]![frmUpdateServiceData]![txtNewSupervisor],
tblService.ServiceName = [Forms]!
[frmUpdateServiceData]!
[txtNewServiceName], tblService.ServiceType =
[Forms]!
[frmUpdateServiceData]![txtNewServiceType],
tblService.Attn = [Forms]![frmUpdateServiceData]!
[txtNewAttn], tblService.LiaisonOfficer = [Forms]!
[frmUpdateServiceData]![txtNewLiaison],
tblService.Copy1 =
[Forms]![frmUpdateServiceData]![txtNewCopy1],
tblService.Copy2 = [Forms]![frmUpdateServiceData]!
[txtNewCopy2], tblService.Team = [Forms]!
[frmUpdateServiceData]![txtNewTeam],
tblService.Comments =
[Forms]![frmUpdateServiceData]![txtNewComments],
tblService.SendMail = [Forms]! [frmUpdateServiceData]!
[chkNewSendMail]
WHERE (((tblService.ServiceID)=[Forms]! [frmViewAll]!
[ServiceID]));

The field I am writing back to is a memo field, and I
have
no problem storing the data if I enter through the
table.


-----Original Message-----
Post the SQL of the update query. Also, check the
field
size of the field
into which you're trying to put this updated
information.
Is the length set
to 127 characters?

--
Ken Snell
<MS ACCESS MVP>


I am running an update query to update a field
from
an
unbound control on the form.

When I execute the query, if my text string in the
text
box is longer than 127 characters (ie 128
characters)
the
query fails with an "Invalid Argument" error
message.

Is there any way I can manage this, so I can
actually
update more text?


.



.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Simple select query with text box argument criteria (127 char limi 4
adding a Carriage Return or Line Feed character to an update query 3
Outlook Desktop (latest) stopped syncing IMAP accpounts from selfowned Mail server. 0
Kameron 1
Access update query 2
127 character limit 0
replace characters in table 6
Delete Specific Characters From Text 2

Top