Sql Update Statement Error

Aug 25, 2006

im trying to update to the database with the following sql statements:

FUNCTION fixQuotes( theString )
fixQuotes = REPLACE( theString, "'", "''" )
END FUNCTION

sql #1
sqlstring = "update Asset set AssetID='"&fixQuotes( assetid )&"', AssetLabel='"&fixQuotes( assetlabel )&"', WarrantStart='"&fixQuotes( warrantystart )&"', Engrave='"&fixQuotes( engrave )&"', Status='"&fixQuotes( status1 )&"', Make='"&fixQuotes( make )&"', Model='"&fixQuotes( model )&"', AssetCategory='"&fixQuotes( assetcategory )&"' where AssetID='"&assetid&"' "

sql #2
sqlstring = "update Asset set " &_
"AssetID=' " & fixQuotes( assetid ) & "'," &_
"AssetLabel=' " & fixQuotes( assetlabel ) & "'," &_
"WarrantStart=' " & fixQuotes( warrantystart ) & "'," &_
"Engrave=' " & fixQuotes( engrave ) & "'," &_
"Status=' " & fixQuotes( status1 ) & "'," &_
"Make=' " & fixQuotes( make ) & "'," &_
"Model=' " & fixQuotes( model ) & "'," &_
"AssetCategory=' " & fixQuotes( assetcategory ) & "' where " &_
"AssetID=" & AssetID

ive tried both @ different times but they dont work. the adodb connection's open

View Replies


ADVERTISEMENT

Error In UPDATE Statement

Aug 24, 2005

Hi everyone,
I keep getting this error whenever i try to run the ASPcode. Can anyone please help me

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.

I have two tables data1 and data2 in the same access database (data.mdb)

here is the query

strDBPath = Server.MapPath("data.mdb")
Set cnnSimple = Server.CreateObject("ADODB.Connection")
cnnSimple.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";"

Set rstSimple = cnnSimple.Execute("SELECT sponser2 FROM data2")

Do While Not rstSimple.EOF
spo=rstSimple.Fields("sponser2").Value
set tamt = cnnsimple.Execute("SELECT SUM(amount1) FROM data1 WHERE sponser1='"&spo&"' ")
Set rstSimple1 = cnnSimple.Execute("UPDATE data2 SET amount2= "&tamt(0)&" WHERE sponser2='"&spo&"' ")
rstSimple.MoveNext
Loop

View 2 Replies View Related

Syntax Error In UPDATE Statement..

Jun 27, 2005

Hello,

Hopefully this is an easy one! but for the life of me i can't see what im doing wrong , help is appreciated.

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/diary_editE.asp, line 272

SQL = "UPDATE diary SET dte =" & Request.Form("dte") & ", " &_
" eTime= '"& ChkStr(Request.Form("tim")) & "', " & _
" eEnd= '"& ChkStr(Request.Form("endt")) & "', " & _
" text_field = '" & ChkStr(Request.Form("title")) & "', " & _
" eLocation = '" & ChkStr(Request.Form("locat")) & "', " & _
" eContact = '" & ChkStr(Request.Form("conta")) & "', " & _
" eSpeak = '" & ChkStr(Request.Form("speak")) & "', " & _
" eAudiin = '" & ChkStr(Request.Form("audi")) & "', " & _
" eAudiex = '" & ChkStr(Request.Form("aude")) & "', " & _
" eVisitors = '" & ChkStr(Request.Form("evisit")) & "', " & _
" ePromo = '" & ChkStr(Request.Form("prom")) & "', " & _
" details = '" & ChkStr(Request.Form("details")) & "', category = " & Request.Form("cat") & _
" WHERE id = " & Request.Form("ID")


my_conn.Execute SQL

If anymore info is required please tell me, but basically i can insert into the database no problems , but when it comes to updating what is in there i recieve the above error

Thanks in advance

View 3 Replies View Related

Error 3464 With An UPDATE Query Statement

Jun 1, 2005

Hi everyone,

I'm trying to execute an update query by using the DoCmd.RunSQL. Here is my statement:

DoCmd.RunSQL "UPDATE T_Facturation SET TotalQuantiteDiesel = TotalQuantiteDiesel + " & Me.TxtQuantiteDiesel.Value & ", " & _
"TotalInst = TotalInst + " & Me.TxtInst.Value & ", TotalDemo = TotalDemo + " & Me.TxtDemo.Value & ", " & _
"TotalCoutDiesel = TotalCoutDiesel + " & Me.TxtCoutDiesel.Value & ", TotalLocationHQ = TotalLocationHQ + " & Me.TxtLocationHQ.Value & ", " & _
"TotalTempsHQ = TotalTempsHQ + " & Me.TxtTempHQ.Value & ", TotalLocation = TotalLocation + " & Me.TxtLocation.Value & ", " & _
"TotalTransfo = TotalTransfo + " & Me.TxtTransfo.Value & ", TotalAutreDep = TotalAutreDep + " & Me.TxtAutresDep.Value & ", " & _
"TotalCable = TotalCable + " & Me.TxtCable.Value & ", TotalElectricien = TotalElectricien + " & Me.TxtElectricien.Value & ", " & _
"TotalTempExt = TotalTempExt + " & Me.TxtTempsExt.Value & ", " & _
"TotalTransportGeneratrice = TotalTransportGeneratrice + " & Me.TxtTransportGeneratrice.Value & ", " & _
"TotalTransportTransfo = TotalTransportTransfo + " & Me.TxtTransportTransfo.Value & " WHERE NoAutoInstallation = '" & Me.TxtNoAutoInstallation.Value & "';"

My problem is that when I execute this code, I got the error:
Run-time error '3464': Data type mismatch in criteria expression.

I tried to change my query statement, but I cannot find my error. I'm using Access 2003. I'm really lost.

Can someone help me please!

Thanks a lot

View 2 Replies View Related

Queries :: Syntax Error In Update Statement

Nov 27, 2014

I have 2 tables called MakeTable1 and DBO_TBL_Activity

Im trying to update MakeTable1 with the values from TBL_Activity when both activity.StartDate and maketable1.Dates match but also acticity.IDStaff and Maketable1.ID Match

Below is the SQL i have so far

Code:

UPDATE [MakeTable1].[Detailsa] SET [dbo_tbl_activity].[details]

WHERE [MakeTable1.Dates)=[dbo_tbl_activity].[StartDate] AND [MakeTable1].[id]=[dbo_tbl_activity].[idstaff]);

The error is :syntax error in update statement

View 2 Replies View Related

Modules & VBA :: Update Query End Of Statement Error

Aug 10, 2015

I'm trying to write an UPDATE line in VBA, but whenever I put the table name I get an "Expected End of Statement" error.This is line currently;

Code:
strSQL = UPDATE "MASTER PLANNER" SET [SET 2] = TRUE WHERE "[ID] = " & Me.PlannerID

It doesn't matter whether I change the table name to square brackets, quotes or nothing at all, it always gives me the expected end of statement error.

View 2 Replies View Related

Queries :: Update Statement With Null Dates - Getting Syntax Error

Jul 5, 2013

My issue is that I am trying to update a date field. When I do the date field may have a date or may be a null. When I try to pass in a NULL date with no quotes, I get a syntax error. When I have single quotes in the statement and a null value is passed in, I get an invalid use of date.

Dim DENIEDDATE1 As Date
If (Not IsDate(rs.Fields("DENIED_DATE"))) Then
DENIEDDATE1 = Null
Else
DENIEDDATE1 = "'" & rs.Fields("DENIED_DATE") & "'"
End If

update table1 set table1.denieddate = " & denieddate1 & " 'get Update syntax error with this statement
update table1 set table1.denieddate = '" & denieddate1 & "' 'fails due to invalid use of null

View 8 Replies View Related

Modules & VBA :: Update And Amend Records In A Table - Error Trying To Execute SQL Statement

Jun 18, 2013

I'm trying to create a function to update and amend records in a table.

The update part works and updates existing records with new data but I'm getting an error with the insert part.

Run time error 3078
The Microsoft Office Access database engine cannot find the input table or query 'FALSE'. Make sure it exists and that its name is spelled correctly.

Nothing called 'FALSE' so not sure what that means?

Code:

sSQL = "INSERT INTO Pupil_tb (PupilID,Class,PupilName,etc ) " _
= "SELECT PupilImport_tb.PupilID, Class, PupilName, etc FROM PupilImport_tb " _
& "LEFT JOIN Pupil_tb " _
& "ON Pupil_tb.PupilID=PupilImport_tb.PupilID " _
& "WHERE Pupil_tb.PupilID Is Null "

CurrentDb.Execute sSQL, dbFailOnError

View 5 Replies View Related

"INSERT/UPDATE" Statement Error

Sep 19, 2004

I try to do an INSERT statement throught my ASP to add records to my Access DB. However I get an error when I execute this. The code in ASP is -

Code: strSQL = "INSERT INTO tbl_psm (LocID, Week, Month, Year, Rating, Remark, Action, SubmittedDateTime) VALUES ('" &cint(intLocid)& "', '" &cint(sWeek)& "', '" &cint(sMonth)& "', '" &cint(sYear)& "', '" &cint(sRating)& "', '" &sRemark& "', '" &sAction& "', '" &formatdatetime(date,vbshortdate)& "');" objConn.Execute strSQL

I get the error page -



Microsoft JET Database Engineerror '80040e14'
Syntax error in INSERT INTO statement. /PeriodicWorkScheduleRatingSubmit.asp, line 65

I tried doing a response.write onto the ASP (to get the actual SQL), got the query, copy-pasted it and ran it on MS Access - it worked fine there. The query is -

Code: INSERT INTO tbl_psm (LocID, Week, Month, Year, Rating, Remark, Action, SubmittedDateTime) VALUES ('50', '2', '9', '2004', '3', 'asd', 'asdasd', '9/20/2004');

I can't understand if it works in Access, who not work through ASP?? Is there somethings wrong somewhere? I cannot see it..

Please help..

View 2 Replies View Related

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?

View 2 Replies View Related

Update Statement

Jan 28, 2008

Hi,

I have the following SQL update statement, but I can't seem to get it working. Would anyone know what I'm doing wrong?


DoCmd.RunSQL ("UPDATE Document_Table " & _
"SET Document_Table.Title = '" & Me.Title & "' " & _
"Document_Table.Date = '" & Me.Date & "' " & _
"Document_Table.Location = '" & Me.Location& "' " & _
"Document_Table.Description = '" & Me.Description & "' " & _
"Document_Table.Entered_Date = '" & Me.Entered_Date & "' " & _
"Document_Table.Approval_Date = '" & Me.Approval_Date & "' " & _
"Document_Table.Available_Date = '" & Me.Available_Date & "' " & _
"Document_Table.Keywords = '" & Me.Keywords & "' " & _
"Document_Table.Document_Number = '" & Me.Document_Number & "' " & _
"Document_Table.File_Size = '" & Me.File_Size & "' " & _
"WHERE Document_Table.Document_ID = '" & Me.Document_ID & "' ;")


Thanks in advance for your help!

View 10 Replies View Related

Help With Update Statement

Jan 5, 2005

I am after some help please.

I have the following as part of an sql statement in visual basic to update a table with a value in a text box in a form:-

DoCmd.RunSQL "Update[CostComponentDetail] set [CostComponentDetailDescription]=(Forms![Edit SKU NCP Cost Data]!TextBox1.1


However when I try to run it, it does not like the full stop in the name of the text box "TextBox1.1".

I have tried putting it in square brackets, normal brackets etc but I am completley stumped.

I really don't want to change the name of the text box as the format affects how the form is populated.

Is there a way of using a control with a full stop in the name or can it just not be done at all?

Any help would be greatly appreciated.

View 3 Replies View Related

Iif Statement After Update Problem

Mar 1, 2007

I am working on a bill of materials database. Once a record is entered, the current total appears. After the record has been updated, I would like to use a iif statement that determines what the tax should be. I already have my iif statement that is working. My problem is that the calculation takes a small amount of time to actually calculate. By the time the calculation is made, the iif statement has already run giving me false data. Just to test the iif statement out, i put it in a command button and it works every time i click it. Any advice on how i can run this iif statement after the calculation is made? I have also tried to put it in the On change event of the field that is calulating and it still won't work.

Thanks
Gregg

View 7 Replies View Related

Update Statement Or Query From VBA

Dec 24, 2004

I am trying to update the fields of a table from comboboxes and textboxes on a form. I don't want all the fields to receive data from the objects on my form directly. Some of the bound controls are not visible and data would be entered into them from visible comboboxes instead. I am not sure exactly how to do that. I tried to devise a query that would take the values from the comboboxes and equate them to those of the hidden controls, but that didn't work. I tried inserting an update command in code but that came up with an error. If some one could help me I would be thankful.

View 7 Replies View Related

Insert And Update In Same Sql Statement

Feb 20, 2008

Is it possible to have an insert and update in the same sql statement using MS Access 2002? I could split it into seperate statements but for code purity I would like to have it in one :cool:

View 2 Replies View Related

Using An If Statement To Condition After Update

Oct 31, 2006

Hi, im very new to visual basic programming...what i am trying to do is implement an If Statment on AfterUpdate for my form:

If record_is_new Then

Dim db As DAO.Database
Dim RS As DAO.Recordset

Set db = CurrentDb()
Set RS = db.OpenRecordset("LTOT_second_oxygen", dbOpenDynaset)
RS.AddNew
RS![HospitalNumber] = Me![general_info.HospitalNumber]
RS.Update
RS.Close
db.Close
Set RS = Nothing
Set db = Nothing
.........more of this kind of code

Else nothing
End If

any ideas on the vb code that would do this?

View 5 Replies View Related

Update Checkbox Value With IF Statement

Aug 5, 2014

What is the correct way to update the checkbox value in a table with an IF statement?

Desired Result:

Check if UserID is not 'ME'
If it is not 'ME' then uncheck the box in the table.

My current faulty code below:

NoUser = DLookup("[UserID]", "[TABLE]") If NoUser <> "ME" Then
[AllowLogin] = 0
End If

View 1 Replies View Related

IIf Statement Giving Error

Nov 3, 2005

SELECT Conversion.[Asset Class], iif([asset class]<4000, "1", "2")
FROM Conversion;

View 2 Replies View Related

Error From Simple Statement

Jun 5, 2007

Okay, now I'm suspecting there's more to this than an overly complicated control source statement....

My summary report contains multiple objects that calculate totals based on a statement in their control source. The report ran fine for a few weeks but all of a sudden it started producing this error:

"The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."

I thought perhaps that 12 lengthy control source statements were simply too much for Access to handle, but then I did some testing and eventually removed all but this control source statement: =Sum([NotifType]=9)

That's about as simple as it gets, but when I try and run the report I still get the error. Ggggrrrrrrrrr....

Can anyone tell me what causes this annoying error and how I can get rid of it? (I already compiled.) What little Access quirk am I running into? I will gladly zip the file and post if necessary.

Thank you.

View 10 Replies View Related

Fix SQL Statement, Returning Error ...

Oct 19, 2007

Hello,
Run-time error ...
No value given one or more required paramete


UPDATE GuitarOptionDetails SET GuitarOptionDetails.OptionCombo = Concatenate("SELECT Option_Item FROM GuitarHeader WHERE GuitarItem & Option_Item =""" & [GuitarOptionDetails].[GuitarItem] & [GuitarOptionDetails].[Option_Item] & """");


basically i need to create an update query to update the OptionCombo field where:
1. OptionCategory=BODY
2. Combine all the BODY OptionItems as one into the OptionCombo field (the Concatenate() function should accomplish this portion)
3. For each InvoiceNumber AND GuitarItem.

NOTE: some invoices will not have any BODY Option_Item, this can place "N" in the OptionCombo BUT only if the invoice does not have a single BODY Option_Item, if it does, it needs to put the combined BODY Option_Item values into the OptionCombo field.

How should I rewrite the SQL to achieve this without getting this error?

View 14 Replies View Related

Syntax Error In IIF Statement

Feb 20, 2008

Hi,

I need help with the following question.

I got the following error when loading up a form:

IIf(Not IsNull([SubJobName]) Or [SubJobName]<>"",[SubJobName],[JobName]) AS Expr1, [SubJobs].Status

Can you have IIF statement in the query expression? because it said "Syntax
error in string in the above query expression".

The full codes i have in the form load is
Private Sub Form_Load()
strSQL = "SELECT [Jobs].JobID, [SubJobs].IndustryNo, [SubJobs].ClientNo, [SubJobs].JobNo, [SubJobs].SubJobNo, IIf(Not IsNull([SubJobName]) Or [SubJobName]<>"",[SubJobName],[JobName]) AS Expr1, [SubJobs].Status"
strSQL = strSQL & " FROM [SubJobs] INNER JOIN [Jobs] ON ([SubJobs].JobNo = [Jobs].JobNo) AND ([SubJobs].ClientNo = [Jobs].ClientNo) AND ([SubJobs].IndustryNo = [Jobs].IndustryNo)"
strSQL = strSQL & " WHERE ((([SubJobs].Status) = -1))"

Me!ListBox_Jobs.RowSource = strSQL

End Sub


Thank you in advance

View 1 Replies View Related

Update On A Query With A Join Statement

May 23, 2005

Hi,

I am trying to do an update on a table with a query which has a join as follows but getting an error:

UPDATE RoleObjects
SET AccessType = 1
FROM RoleObjects
INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID
WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')


I tried to debug the code and found that the following part of the code with the Select clause works fine.

SELECT *
FROM RoleObjects
INNER JOIN Objects On Objects.ObjectID = RoleObjects.ObjectID
WHERE (RoleObjects.RoleID <> 1) AND (MID(Objects.ObjectName, 4, 2) = 'SR')

Do u have any suggestions.. ??

Thanks a lot for your time.

View 1 Replies View Related

Modules & VBA :: How To Use Variables In Update Statement

Apr 10, 2014

What is the syntax for using variables in a VBA update statement? I have the following that I want to use to update a record field.

Code:
Dim thisTbl as String
If Answer = vbYes Then
If MedicationInvNo2 <> "" Then
thisTbl = "tblMyMedData"
Else
thisTbl = "tblMedData"

[code]....

I'm getting run-time error '3144' when the database tries to run the SQL. So I'm assuming my syntax is wrong (specifically in the WHERE clause)?

View 3 Replies View Related

Syntax Error In Insert Into Statement

Feb 1, 2007

What is the syntax error in this Insert Into statement ?

INSERT INTO RawData(RunID,fullName,name,category,type,subType, numberOfLines,virtual,date,namespace) SELECT 257 ,fullName,name,category,type,subType,numberOfLines ,virtual,#1/3/2007#,namespace FROM RawData WHERE namespace ='customer.demo' AND RunID =256

View 2 Replies View Related

Nested If Statement Syntax Error

Aug 29, 2007

Hello,

I'm new to working with Access Iif statements. I'm attempting to change data values in an existing table for column named "Status." Some of the data in this column has the correct value (Active) that needed represented, while the rest is populated with either a "J" which is Active or "T" which is inactive. I needed an update query that will ignore any values that are already "Active," and convert any values that are "J" to Active and values that are "T" to "Non Active." Any help would be greatly appreciated. Below is the function I have written that's full of syntax errors:

IIf(([STATUS] T,"T") = "T", then "Non Active"), IIf([STATUS] J,"J") = "J" then "Active"), else Active = "Active"))

View 11 Replies View Related

Run Time Error 3134 - SQL Statement

Mar 16, 2005

Good day,

I was wondering if I can get some help with my sql statement, b/c I am a run-time error. I have attached my db but the statement is as follows:

SqlStr = "INSERT INTO tblpcomaward (recempid, reclname, recfname, recdate, givempid, givelname, givefname, desc, value, rebill, comments) Values" _
& "('" & Forms!frmpcom!txtempid & "', '" & Forms!frmpcom!txtlastname & "','" & Forms!frmpcom!txtfirstname & "', #" & Forms!frmpcom!txtdategiven & "#," _
& "'" & Forms!frmpcom!txtgiverempid & "', '" & Forms!frmpcom!txtgivelname & "', '" & Forms!frmpcom!txtgivefname & "', '" & Forms!frmpcom!cbodesc & "', '" & Forms!frmpcom!txtvalue & "', '" & Forms!frmpcom!cborebill & "', '" & Forms!frmpcom!txtcomments & "')"


Thanks for any help.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved