Queries :: SQL Syntax Error After Edit
Nov 19, 2014
why the first code is working and after adding a few lines it gives me a syntax error. It highlights the AS ETI. I usually work on the query grid, but I read somewhere there is a limit to the length of expression. So I ended up editing the expression in SQL view, but somehow doesn't work.Working before editing:
Code:
SELECT [Salaries YTD].[Emp#], [ETI Filter].ETI1, Sum([Salaries YTD].DaysWorked) AS SumOfDaysWorked, IIf([SumOfGross] Between 0 And 2000 And [ETI1]=1000,[SumOfGross]*([DaysWorked]/(DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6)*5)*0.5),IIf([SumOfGross] Between 2001 And 4000 And [ETI1]=1000,1000,IIf([SumOfGross] Between 4001 And 6000 And [ETI1]=1000,1000-(0.5*([SumOfGross]-4000))))) AS ETI, DateDiff("ww",[Forms]![PeriodSelector]![FromDate],[Forms]![PeriodSelector]![ToDate],6) AS Weeks, Sum([Salaries YTD].Gross) AS SumOfGross1
[code]...
View Replies
ADVERTISEMENT
Mar 4, 2005
Hi!
I think this is propably an easy thing to do, but nevertheless I have not been able to make it work in MS Access...
(Let's say) I have a table with information about people. Sometimes I need to get a subgroup of the table_people, like a group with a certain age, or who live in a certain area, or who like certain things. And sometimes a need to work with the whole table.
To be able to do this basic filtering I have a form with check boxes, like "check this if you want to filter by a persons hobby" and appropriate fields where to choose a hobby from a list. My problem is: how do I add this "condition" to my query? I've tried eg. using "Like" as a filtering tool and IIF() to add the condition, but this doesn't work (in the criteria row):
Like (IIF( Forms![basic]![Filter_by_hobby] , "[Forms]![basic]![Hobby_from_list]", "*"))
So the idea is that if the box is checked, the condition is true and the first text is chosen, and if no filtering by hobby is required, all people are chosen regardless of their hobbies (Like "*")
How to fix this - is there a syntax error or a profound mistake in my thinking - or how to do the same more elegantly?
Thanks in advance,
garcanrya
View 2 Replies
View Related
Mar 3, 2014
I have a query written in Visual Basic as follows
UA1 = Forms(ParName).Form.NOM
UA2 = Forms(ParName).Form.PRENOM
UA3 = Forms(ParName).Form.CARTE
Forms(ForName).Recordset.FindFirst "[NOM] = '" & UA1 & "'" & " And " & _
"[PRENOM] = '" & UA2 & "'" & " And " & _
"[CARTE] = '" & UAE & "'"
This query works and I can spot the record based on 3 fields and display it. Now I have discovered that we have a person who has an apostrophe in his name like O'Brian. The above code gives a syntax error in this case.
View 3 Replies
View Related
Sep 5, 2014
Trying to parse first two sections of a five section string. Now getting a SYNTAX COMMA ERROR. Th Left((([ArborID],InStr([ArborID],"-")+1,(InStr(InStr ([Arbor ID],"-")+1,[ArborID],"-")-InStr([ArborID],"-"))-1)),10)
View 14 Replies
View Related
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
Jun 15, 2015
I'm trying to replace a null value with $0.00 for the second field in a query. My first try at the SELECT stmt did not contain any solution for a NULL value. The result was that it skipped the record. I need it to show 0.00 because the field is used in another calculated field.
My SQL:
SELECT tblRecovery.CustID, IF((Sum([tblRecovery.RecAmt]) IS NULL, 0.00, (Sum([tblRecovery.RecAmt]))) AS SumOfRecAmt
FROM tblRecovery
GROUP BY tblRecovery.CustID;
This returns : Syntax error (missing operator) in query expression 'IF(( etc.
After clicking "OK", access highlights AS in the statement. I'm not sure how to deal with the NULL value or fix the error?
View 6 Replies
View Related
Apr 15, 2014
I am creating a database to analyze prices. I have 2 tables. One table has a bunch of dates, product names and the prices. There are many of the same dates and many of the same products. I.E. a product could get priced one day and than have a different price the next day.The other table has a bunch of information and dates and product names also. The goal is to add a week to the dates in this table and use that date and product name to look up the corresponding price for that date and product and add it to the record.
I am trying to use Dlookup but evey record is then filled with the same price. I am using this Expr1: DLookUp("Price","Qry_Historic_Price","[Date]=#[End Period]# And primary_id ='[primary_id']")...When I run the query I get a message that says "Syntax error in query expression" than a message box that pops and says "Unknown" with only "ok" to click.
View 4 Replies
View Related
Jul 31, 2013
Access and receive the following message after trying to run a query: Syntax error (comma) in query expression, followed by the formula I wrote on the Query Builder. I use 4 tables to run the query, but only need to trim some stuff from one of them. This table is called BD_lamosa_corregida and have already selected Expression on the Total row in Design View. This is the formula:
parte: Trim(IIf(IIf(IsError(InStr(1,[Parte],"(",1)),"",InStr(1,[Parte],"(",1))="",[Parte],Left([Parte],IIf(IsError(InStr(1,[Parte],"(",1)),"",InStr(1,[Parte],"(",1))-1)))
The formula is trying to trim the left side of an expression (Part description) which may contain a code number in parenthesis or not, it might also have this parenthesis separated by a space or not.
View 1 Replies
View Related
Jan 31, 2014
I'm using the following for a field in a query:
ITINERANT: IIf([Day and Time1] Is Not Null,[Day and Time1],IIf([Day and Time2] Is Not Null,[Day and Time2],IFF([Day and Time 3] Is Not Null,[Day and Time 3],IFF([Day and Time 4] Is Not Null,[Day and Time 4],IFF([Day and Time 5] Is Not Null,[Day and Time 5]," ")))))
I'm getting the error message: Syntax error (missing operator).
I'm not very good at using the IIF statement.
I have 5 concatenated fields that may or may not have info.
I want to display all of the info or if blank; show nothing in the one field.
Also, would I be able to have a return after each of the 5 concatenated fields?Not sure how to do that in this statement?
View 2 Replies
View Related
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
Aug 4, 2015
I'm trying to run a very basic iif statement to correct hourly data for sorting. Basically, a trading day runs from 8am - 8am, so I need to adjust the hours to ensure that 1am on the 15th trading day (really the 16th on the calendar), comes after 9am on the 15th trading day (which will actually be the 15th on the calendar).
Here's what I've used. It's driving me bananas, because it keeps telling me that there's a syntax error (comma) in the query expression, but I can't understand why?
Sort2: IIf([DELIVERY_HOUR]<8,[DELIVERY_HOUR]+24,[DELIVERY_HOUR])
View 14 Replies
View Related
Jun 5, 2014
I am trying to get Average If function to access sql. I have columns Period and Costs_Per_Capita, result should be like like this:
Costs_Per_Capita Period CALCULATED_Period_Avg_Costs
15,505 1 15976.27582
16,368 1 15976.27582
16,037 1 15976.27582
15,995 1 15976.27582
15,000 2 16000
17,000 2 16000
I tried:
SELECT Costs.Costs_Per_Capita, Costs.Period
IIF (Period = 1, (Select AVG(Costs_Per_Capita) From Costs Where Period = 1),
(Select AVG(Costs_Per_Capita) From Costs Where Period = 2)
AS result
FROM Costs;
But get "syntax error (missing operator) in a query expression ..."
View 8 Replies
View Related
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
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
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
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
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
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
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
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
Jul 6, 2005
Figured it out thanks.
View 1 Replies
View Related
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
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
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
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
Nov 28, 2007
When I'm runnig the query, I'm getting an error "syntax error in from clause".
I can't enter to "design" mode to find the error.
View 2 Replies
View Related
