Insert Into Syntax Error

Apr 2, 2008

Hi

This may be a very silly question but I have the following code which is meant to take data from textboxes, checkboxes etc and insert it into a new record on a table using the INSERT INTO statment. However I am getting a Syntax error in my INSERT INTO statement which i cannot figure out.

Please Help!!?

Private Sub cmdSaveRecord_Click()

Dim SQL As String
Dim Today As String
Dim Ref As String
Dim HK As String
Dim Site As String
Dim Equip As String
Dim Serial As String
Dim Invoice As String
Dim Client As String
Dim HKRef As String
Dim Tested As String
Dim Completed As String
Dim chkHKr As String
Dim Repaired As String
Dim Spares As String
Dim CompDate As String
Dim Exp As String

Today = txtDate
Ref = txtRef
HK = txtHK
Site = txtSite
Equip = txtEquipment
Serial = txtSerial
Invoice = txtInvoice
If cboClient.Column(0) <> Null Then Client = cboClient.Column(0) Else Client = ""
HKRef = txtHKRef
Tested = chkTested.Value
Completed = chkCompleted.Value
chkHKr = chkHK.Value
Repaired = chkRepaired.Value
Spares = chkSpares.Value
CompDate = cldComp.Value
Exp = cldExp.Value


SQL = "INSERT INTO ServiceReport (Date, CallReferenceNo, HongKongFaultNo, Client, Site, Equipment, SerialNo, Tested, Repaired, Spares, HK, ExpectedDate, Completed, InvoiceNo, CompletedDate VALUES (Today, Ref, HK, Site, Equip, Serial, Invoice, Client, HKRef, Tested, Completed, chkHKr, Repaired, Spares, CompDate, Exp)"

DoCmd.RunSQL SQL

View Replies


ADVERTISEMENT

INSERT INTO Syntax Error ???

Oct 30, 2005

Hello All,
I'm am writing an App in Java connecting to an MS Access database. I am now getting a syntax error on the following insert into statement:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
source = "jdbc:odbc:DKOperations";
connection = DriverManager.getConnection(source);
Statement stmt = connection.createStatement();
String CustInfoTable = "CUSTOMER_INFORMATION";
stmt.executeUpdate("INSERT INTO " + CustInfoTable + " CUSTOMER_FIRST_NAME VALUES " + CustFirstName);

This is the error:
An SQLException occurred: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

I cannot figure out what the syntax error is. Anyone have an insight on this for me? Most likely something easy that I am missing.


Thanks!

View 1 Replies View Related

Syntax Error-during Insert

Mar 22, 2007

This is a simple code for inserting values to database.But I'm getting syntax error in the insert statement.I'm attaching the code for reference.Do help me out
Regards
Ransha

Dim mail,Login,password,conn,strSql
Login=Request.Form("name")
password=Request.Form("password")
mail=Request.Form("email")
'Creating connection Object
Set conn=Server.CreateObject("ADODB.Connection")
conn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:/example/login.mdb"
strSql = "INSERT INTO Names (UserName, Password, Email) VALUES ('"&Login&"','"&password&"','"&mail&"')"
conn.Execute(strSql)
conn.Close
Set conn=nothing

View 3 Replies View Related

Syntax Error Insert Into

Oct 7, 2007

Can anyone tell me what is wrong with this statement. I get the error "Syntax error in INSERT INTO statement". I am the worst kind of user as I did not write this database and am trying to learn on the fly. Any help is greatly appreciated.
This macro is supposed to insert an amount into an "Activity" record based on the Co_Default_Rate field setup in the CompanyInfo table.

INSERT INTO [FORMS]![Activity]![Amount] SELECT (CompanyInfo.Co_Default_Rate)
FROM CompanyInfo
WHERE CompanyInfo.Co_Number = [FORMS]![Installs]![Installs_Co_Number];

View 1 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

Syntax-error In INSERT INTO-expression

Apr 13, 2005

Hello.

I've managed to create an access-database and (fill it with some data), retrieve data from it with asp, but
when I try to insert or update data with asp, I get the same error:

"Syntax-error in Insert expression" or "Syntax-error in update expression"

Do I have to "chmod" the database to write to it, or what?

(I usually work with sql-databases, and the code/syntax I believe is not wrong,as it works on another database)
Conn.Execute("INSERT INTO tour(date,venue,city,link) VALUES('" & datum & "','" & venue & "','" & city & "','" & link & "')")

Thank you in advanced for answering my query.

//Joakim

View 5 Replies View Related

Insert Error Due To Apostrophe - Syntax Needed

Jul 20, 2006

hi,
i am getting an error in my coding for a INSERT statement becasue the text of the field contain an apostrophe.

that is my code is

DoCmd.RunSQL "INSERT INTO tbl_StartEndDates_OVERHEAD ( Last_Name)" _ & "VALUES ('" & Forms!OverheadEmployeeDates!lblLast_Name & "');"

but the text in Forms!OverheadEmployeeDates!lblLast_Name is "D'Amereo".

I know that this works for all other Last_Names without an apostrophe in it

does any one know the correct coding for this?

thansk
tuk

View 3 Replies View Related

Modules & VBA :: INSERT INTO Runtime / Syntax Error?

Jun 11, 2013

Why I get a runtime 3134 error on this piece of code.

They are all text values

Code:

Dim strUserName As String
strUserName = Forms!FrmPrimaryData.FrmPrimaryDataInstallsSubFrm.Form.txtMacAddress
Dim strIDValue As String
strIDValue = Nz(DMax("[ID]", "radreply"), 0) + 1
Dim strAttribute As String

[Code] ....

View 6 Replies View Related

Modules & VBA :: Syntax Error In INSERT INTO Statement

Jan 22, 2014

I am having a problem with below and getting a run-time error 3134

Code:
LastOrderNumber = DMax("Order", "Model_types")
NewOrderNumber = CLng(LastOrderNumber + 1)

CurrentDb.Execute "INSERT INTO Model_types (Order) " _
& "VALUES (" & NewOrderNumber & ")"

The field 'Order' in Model_types is a Long Integer.

View 4 Replies View Related

General :: Syntax Error In INSERT INTO Statement

Jul 31, 2012

I'm trying to run the following query

INSERT INTO Enrolled_Students (Last Name, First Name, Address, town/city, county, postcode, phone number, date of birth, age)
SELECT Last Name, First Name, Address, Town/City, County, Postcode, Phone Number, Date of Birth, Age
From Candidate Details
Where IsNumeric (Student ID);

and i'm receiving the error stated above

what im doing wrong?

View 12 Replies View Related

Modules & VBA :: Syntax Error With INSERT Statement And Subform

Mar 13, 2015

I am getting a syntax error on my SQL statement.

On a form I have a sub form containing the field txtGuestID - whose control source is GuestID.

On the main form I have a button that fires the code below.

I am sure I am not referring to the control txtGuestID correctly.

Code:
Private Sub cmdInbound_Transport_Click()
Dim iProductID As Integer
Dim sSQL As String
On Error GoTo cmdInbound_Transport_Err

[Code] ....

View 6 Replies View Related

Insert Into Syntax Eror?

Aug 24, 2007

Uhhh...For the life of me I can't understand why this doesn't work:----------insert into IDProjectData(IDCFirstName, IDCLastName, IDCDepartment, EmpNo)values (SELECT P_FNAME, P_LNAME, P_LEVEL2, P_EMPNOFROM HRPERSNLWHERE HRPERSNL.P_TERMDATE Is Null);----------basically all I want is the four columns returned from the sub query inserted into the IDProjectData. I keep getting a syntax error in query expression ("SELECT P_FNAME" but I don't see the problem - not to mention the sub query runs just fine by itself. Pulling my hair out...

View 5 Replies View Related

INSERT Syntax Issue

Dec 20, 2007

Hey, I'm having trouble for some reason with this query. It's telling me it can't find the object "Deletions" which is a valid table. Directly above this I do another operation with the same table referenced the same exact way. Is this a syntax thing?'this worksDim qryImportRecords As StringqryImportRecords = "INSERT INTO [Deletions] IN '" & _ strPathToCurrentDatabase & _ "' SELECT * FROM [table2$] WHERE NOT IsNull([table2$].field1)"Cn.Execute qryImportDeletionRecords'this doesn't work... ?!?Dim qryTestAndTransfer As StringqryTestAndTransfer = "INSERT INTO tempTable SELECT * FROM [Deletions]"Cn.Execute qryTestAndTransferI am going to add on some WHERE restrictions at the end here, but I took those off hoping it would solve my problem. But it didn't. What's wrong with this query - shouldn't it be putting everything from one table into the other as-is?! I've also tried this as a DoCmd.RunSQL, but still, same issue...

View 5 Replies View Related

What Is The Syntax Of Create Table And Insert Statement For Access 2000 Db.

Jun 26, 2005

What is the syntax of create table and insert statement for access 2000 db.

I want to paste the create table and insert statement to access 2000 "sql view window".
Therefore i want the correct syntax and format for these statements.
The reason that i want do this is that I created an application that generates create
table and insert statements for access db in text file and this way i want to test my application if it
generated the statement correctly.

View 3 Replies View Related

Syntax Error

Mar 2, 2007

On my form I have 2 radio buttons rdoAll and rdoSpecific. If rdoAll is true then it prints a report. THis part works fine. However if rdoSpecific is true then I make visable combo box to look up an ID. Then when I click the button I want the same form to open but with just the info pertaining to the ID selected. Here is my code:

Private Sub cmdLotHistory_Click()

Dim stDocName As String, stSelection As String

stDocName = "rptLotHistory"

If Me.rdoAll = True Then
DoCmd.OpenReport "rptLotHistory", acViewPreview
End If

If Me.cboLotLU.Value > 0 Then
stSelection = "[LotID] =" & Me![cboLotLU]
End If

DoCmd.OpenReport stDocName, acViewPreview, , stSelection

End Sub

When I choose the ID and click the button I get the message

Run-Time error '3075':

Syntax error (missing operator) in query expression '(LotID
=020806B1585)'.

the 020806B1585 is my ID number.

Debug hightlights the
DoCmd.OpenReport stDocName, acViewPreview, , stSelection

but I'm guessing my real problem lies in the
stSelection = "[LotID] =" & Me![cboLotLU]


Can anyone point me in the right direction?

Thanks,
Rick

View 2 Replies View Related

Syntax Error

Sep 12, 2005

I have a function that builds a filter and it get a syntex error. It has three components and I must be missing something in combining them into the filter. SpecID and ReviewID are numbers. Selected is a checkbox and 'Yes' is a string. Can anyone see the source of the Syntax Error?

Private Function PlanFilter()
Dim strFilter1 As String, strFilter2 As String, strFilter3 As String
strFilter1 = "[SpecID] = " & [Forms]![frmMainEntry]![SpecID]
strFilter2 = "[ReviewID] = " & [Forms]![frmMainEntry].Form!fctlReviewRequests!ReviewID
strFilter3 = "[Selected] = 'Yes'"
gstrFilter = strFilter1 & " And " & strFilter2 & " And " & strFilter3 & ";"
Debug.Print gstrFilter
End Function

Thanks,
PC

View 3 Replies View Related

Syntax Error

Jun 20, 2007

I'm running a VBA query in excel trying to import the field from a table, simple so I thought, any idea on why i'm getting syntax error codes on this part?

Application.StatusBar = "Retrieving Depot Names"
sSQL = "SELECT tbldepot.DepotName 'Depot' " & vbCr & _
"FROM tbldepot, " & vbCr & _
"GROUP BY tbldepot.DepotName "
getSQLintoRange sSQL, Cells(1, iCol), True, True
iCol = iCol + 2


same thing on

'-= DFE =-
'Some manual DFEs have auth code but left at created
'AAP children have the DFE, not parent
Application.StatusBar = "Retrieving DFE Values"
sSQL = "SELECT tbldepot.DepotName 'Depot', " & vbCr & _
" SUM (di.Qty * di.Rate) 'DFEVal' " & vbCr & _
"FROM tblA537 a, tblA537DFE d, tblA537DFEItem di, tblDepot " & vbCr & _
"WHERE tblDepot.DepotID = a.DepotID " & vbCr & _
"AND a.OrderNumber = d.OrderNumber AND d.DFEID = di.DFEID " & vbCr & _
"AND NOT d.Authorisation IS NULL AND d.DFEStatus<>4 " & vbCr & _
"GROUP BY tblDepot.DepotName " & vbCr & _
"ORDER tblDepot.DepotName "
getSQLintoRange sSQL, Cells(1, iCol), True, True
iCol = iCol + 2

View 5 Replies View Related

Any Error With This Syntax?

Jun 26, 2007

Hi All,

As I am really 'Green' in this line, can anyone tell me what wrong with my syntax?

Thanks in advance.


INSERT INTO Depreciation (AssetID,DepreciationAmount,DepreciationDate)
SELECT Assets.AssetID,
CASE WHEN Assets.AssetID FROM Assets not in (SELECT Depreciation.AssetID FROM Depreciation) THEN Month(Assets.DateAcquired)*SLN(Assets.BookValue,As sets.SalvageValue,Assets.DepreciableLife)/12
ELSE SLN(Assets.BookValue,Assets.SalvageValue,Assets.De preciableLife)/12
END,
Format(Me!DepnRunDT, "0")
FROM Status INNER JOIN Assets ON Status.StatusID=Assets.StatusID
WHERE ((Assets.StatusID)=1);

View 1 Replies View Related

Syntax Error

Sep 20, 2007

I am trying to create a query using the SQL view, i get a missing operator error while running this sql

SELECT tblPDInv_Temp.CustId, tblPDInv_Temp.CustName, tblPDInv_Temp.OnHold, tblPDInv_Temp.InvID, tblPDInv_Temp.InvAmt, " & _
"tblPDInv_Temp.AmtPaid, tblPDInv_Temp.DueDate, [InvAmt]-[AmtPaid] AS AmtDue, tblPDInv_Temp.InvStat " & _
"FROM tblPDInv_Temp " & _
"WHERE ((([InvAmt] - [AmtPaid]) <> 0) And ((Date() - [DueDate]) > 0)) " & _
"ORDER BY tblPDInv_Temp.CustName, tblPDInv_Temp.CustID, tblPDInv_Temp.InvID;"

Thanks

View 2 Replies View Related

Syntax Error

May 5, 2005

I use the following code in the After Update Event of the field Lastname to check for a possible duplicate record:

If DCount("*", "[tblPeople]", "[LastName]= '" & Me.Lastname & "' And [PODate] = #" & Me.PODate & "#") Then
......

And it works well.
In case an existing record is found, I want to jump to it.
I tried using the following code, but it gives a syntax error

Me.RecordsetClone.FindFirst "[Lastname] = '" & Me![LastName] & "' And [PODate] = #" & Me.PoDate & "#"
Me.Bookmark = Me.RecordsetClone.Bookmark

Can't figure out what is wrong here.
Any help will be appreciated.

View 4 Replies View Related

Syntax Error?!

Mar 9, 2005

can someone help me here? i'm trying to get a few selected employee from two tables, namely EmployeeParticulars and ProjectAllocation. EmployeeParticulars contained all the employees in the company whereas ProjectAllocation has two fields (PID - the project and EmpID - the employee who is allocated). One project consist of many employees.


Ok, let's move on to my problem now. I'm trying to get those employees who are not involved in a current project. i tried this

Code: Select Emp.Name from EmployeeParticulars Emp, ProjectAllocation Pro where Emp.EmpID <> Pro.EmpID

but it doesnt work. instead in returns me all the employees in the EmployeeParticulars table and on top of that, each employee appears 21 times!(this 21 i believe is from the 21 employees for this particular project) So, it actually runs 21*27(no of employees) = 546 times!

i was supposed to use != but i realise it doesnt not work in MS Access so i used this <> instead. however it does not give me what i want. Can someone assist me please.

I hope I've made my question comprehensive.

View 3 Replies View Related

Syntax Error?

Mar 19, 2005

insert into date (ReqNum, date) values( 1 , '2005-03-20' )

I'm getting a syntax error with that. Does anyone know why?

View 1 Replies View Related

Syntax Error

Jul 6, 2005

Figured it out thanks.

View 1 Replies View Related

Need Help With Syntax Error

Aug 8, 2006

with my code I am having a syntax problem and I am new to asp so I am a little unsure of what needs to be changed.

Please help...

My error Message:
Code:Microsoft VBScript compilation error '800a03ea'Syntax error/addpicks2.asp, line 32values ('" & _username & "', '" & game1 & "', '" & game2 & "', '" & game3 & "', '" & game4 & "','" & game5 & "','" & game6 & "','" & game7 & "','" & game8 & "','" & game9 & "','" & game10 & "','" & game11 & "','" & game12 & "','" & game13 & "','" & game14 & "','" & game15 & "','" & game16 & "', #" & now() & "#)"--------^


My page code:
Code:<%' Declaring variablesDim username, game1, game2, game3, game4, game5, game6, game7, game8, game9, game10, game11, game12, game13, game14, game15, game16, data_source, con, sql_insert' A Function to check if some field entered by user is emptyFunction ChkString(string)If string = "" Then string = " "ChkString = Replace(string, "'", "''")End Function' Receiving values from Formusername = ChkString(Request.Form("username"))game1 = ChkString(Request.Form("game1"))game2 = ChkString(Request.Form("game2"))game3 = ChkString(Request.Form("game3"))game4 = ChkString(Request.Form("game4"))game5 = ChkString(Request.Form("game5"))game6 = ChkString(Request.Form("game6"))game7 = ChkString(Request.Form("game7"))game8 = ChkString(Request.Form("game8"))game9 = ChkString(Request.Form("game9"))game10 = ChkString(Request.Form("game10"))game11 = ChkString(Request.Form("game11"))game12 = ChkString(Request.Form("game12"))game13 = ChkString(Request.Form("game13"))game14 = ChkString(Request.Form("game14"))game15 = ChkString(Request.Form("game15"))game16 = ChkString(Request.Form("game16"))data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ Server.MapPath("access_db/picks.mdb")sql_insert = "insert into week1 (username, game1, game2, game3, game4, game5, game6, game7, game8, game9, game10, game11, game12, game13, game14, game15, game16, entered_on)"values ('" & _username & "', '" & game1 & "', '" & game2 & "', '" & game3 & "', '" & game4 & "','" & game5 & "','" & game6 & "','" & game7 & "','" & game8 & "','" & game9 & "','" & game10 & "','" & game11 & "','" & game12 & "','" & game13 & "','" & game14 & "','" & game15 & "','" & game16 & "', #" & now() & "#)"' Creating Connection Object and opening the databaseSet con = Server.CreateObject("ADODB.Connection")con.Open data_sourcecon.Execute sql_insert' Done. Close the connectioncon.CloseSet con = NothingResponse.Redirect("showweek1.asp")%>

Any help is appreciated, Thanks!

-Anthony

View 2 Replies View Related

Help On NotInList SQL Syntax Error Please..

May 13, 2007

Hi All, I'am using the following code to update a table (tbl_CDT) on a NotInList event of a cboBox (CDT) I keep getting a SQL syntax error message, can anyone help resolve the code please

Private Sub CDT_NotInList(NewData As String, Response As Integer)

On Error GoTo CDT_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The CDT " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add to the list now?" _
, vbQuestion + vbYesNo, "DentureBase")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tbl_CDT's([CDT_Name]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new CDT has been added to the list." _
, vbInformation, "DentureBase"
Response = acDataErrAdded
Else
MsgBox "Please choose a CDT from the list." _
, vbInformation, "DentureBase"
Response = acDataErrContinue
End If
CDT_NotInList_Exit:
Exit Sub
CDT_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume CDT_NotInList_Exit


End Sub
Thanks very much

View 9 Replies View Related

Spot The Syntax Error

Jun 27, 2007

Hi there!

My first post here, and I'm hoping some kind soul will be able to help me (as you can tell, I'm a kinda needy type!!) :o

I've got two completely separate databases, for both of which I need to be able to print a single form at a time. No problem there, a report and a bit of code solved that - but only for one of them! This is the successful one:

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim stDocName As String

stDocName = "Issues"
DoCmd.OpenReport stDocName, acNormal, , "Issue_No = Forms![Concessions]!Issue_No"

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

For my other database, I simply cut & paste the code, then changed the relevant document fields, as below:

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim stDocName As String

stDocName = "Change Proposal Print"
DoCmd.OpenReport stDocName, acNormal, , "Issue No = Forms![Change Proposals]!Issue No"

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

But when I try to run this, all I get every time is: "Syntax error (missing operator) in query expression '(Issue No = Forms![Change Proposals]!Issue No)'".

I can't for the life of me figure why one works but not the other. I've checked and treble-checked that I've got exactly the right document names. The report is "Change Proposal Print"
The form is "Change Proposals"
The form field criterion is "Issue No" (without an underscore in this one.)

What operator could possibly be missing in the second one, but not the first? Am I being very dense? Can anyone out there help? (Am I asking too many questions in a whiny voice?) :confused:

Cheers,

mike b :D

View 4 Replies View Related







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