I am attempting to update a table in Access/VBA using SQL. Here is the statement:
MySQL = "UPDATE ParsingTable SET ParsingTable.[Tier1] = intTier1, ParsingTable.[Tier2] = intTier2, ParsingTable.[Tier3] = intTier3, ParsingTable.[Tier4] = intTier4, ParsingTable.[Tier5] = intTier5 WHERE (((ParsingTable.doc_label) = strReference));"
DoCmd.SetWarnings False
DoCmd.RunSQL MySQL
DoCmd.SetWarnings True
When I execute the SQL Update it asks me for "data input" for each of the variables intTier1 through intTier2 as well as strReference. All of the intTier1-5 variables are Dim as Integer and have values in them. strReference is DIM as String and has a value in it.
Why is the module asking for input values for all of my variables?
Thx
Mark
strReference = A.1.1.3 (for example).
However, I did the following and it worked:
MySQL = "UPDATE ParsingTable SET ParsingTable.Tier1 = " & intTier1 & ", ParsingTable.Tier2 = " & intTier2 & ", ParsingTable.Tier3 = " & intTier3 & ", ParsingTable.Tier4 = " & intTier4 & ", ParsingTable.Tier5 = " & intTier5 & " WHERE ParsingTable.doc_label = '" & strReference & "';"
It seems that because strReference is a text string wrapping " & strReference & " within ' ' is required. At any rate ti worked.
Thanks for looking at this.
Mark
6 1517 -
MySQL = "UPDATE ParsingTable SET ParsingTable.[Tier1] =" & intTier1 & ", ParsingTable.[Tier2] =" & intTier2 & ", ParsingTable.[Tier3] = " & intTier3 & ", ParsingTable.[Tier4] = " & intTier4 & ", ParsingTable.[Tier5] =" & intTier5 & " WHERE (((ParsingTable.doc_label) = strReference));"
-
Good Luck
@vb5prgrmr
First, thank you for the help.
Second, for the variable strReference am I to assume that it also requires "& strReference &"?
When I left it as in your example I still recieved a prompt for input. When I used the "& strReference &" format I am receiving a error indicating I have a problem with '.' '!' or '()". Not sure where to go from here. Can you help?
Thx
Mark
what does strReference actual equal?
strReference = A.1.1.3 (for example).
However, I did the following and it worked:
MySQL = "UPDATE ParsingTable SET ParsingTable.Tier1 = " & intTier1 & ", ParsingTable.Tier2 = " & intTier2 & ", ParsingTable.Tier3 = " & intTier3 & ", ParsingTable.Tier4 = " & intTier4 & ", ParsingTable.Tier5 = " & intTier5 & " WHERE ParsingTable.doc_label = '" & strReference & "';"
It seems that because strReference is a text string wrapping " & strReference & " within ' ' is required. At any rate ti worked.
Thanks for looking at this.
Mark
Yes, the ' ' are required, but that has to do with the conversion to SQL. If you were only use the variable within VBA you'd be okay without them.
@dsatino
Thanks again. I am off and running.
Mark
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Dave |
last post by:
I have 2 tables, one with names, and another with addresses, joined by their
CIVICID number (unique to the ADDRESSINFO table) in Oracle.
I need to update a field in the NAMEINFO table for a...
|
by: Lauren Quantrell |
last post by:
In VBA, I constructed the following to update all records in
tblmyTable with each records in tblmyTableTEMP having the same
UniqueID:
UPDATE
tblMyTable RIGHT JOIN tblMyTableTEMP ON...
|
by: VK |
last post by:
<http://www.jibbering.com/faq/#FAQ3_2>
The parts where update, replacement
or add-on is needed are in <update> tag.
3.2 What online resources are available?
Javascript FAQ sites, please...
|
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums...
|
by: Shapper |
last post by:
Hello,
I have created 3 functions to insert, update and delete an Access
database record. The Insert and the Delete code are working fine.
The update is not. I checked and my database has all...
|
by: jaYPee |
last post by:
I have search a lot of thread in google newsgroup and read a lot of
articles but still i don't know how to update the dataset that has 3
tables.
my 3 tables looks like the 3 tables from...
|
by: PAUL |
last post by:
Hello,
I have 2 tables with a relationship set up in the dataset with vb
..net. I add a new record to the parent table then edit an existing child
record to have the new parent ID. However when I...
|
by: Zorpiedoman |
last post by:
I keep getting a concurrency exception the second time I make a change and
attempt to update a dataadapter. It appears this is by design, so there must
be something I can do to avoid it.
...
|
by: Stephen Plotnick |
last post by:
I'm very new to VB.NET 2003
Here is what I have accomplished:
MainSelectForm - Selects an item
In a public class I pass a DataViewRow to
ItemInformation1 Form
ItemInformation2 Form
|
by: Michel Esber |
last post by:
Hi all,
DB2 V8 LUW FP 15
There is a table T (ID varchar (24), ABC timestamp). ID is PK.
Our application needs to frequently update T with a new value for ABC.
update T set ABC=? where ID...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |