I had a post last week with this same problem. Last time I was using a reserved word but I have ruled it out with this database because I changed every field name and I still get the error. I think it may have to do with the combo boxes I am using, but not really sure why I am getting the error. Any help would be great. The sql statement is:
I ran a very similar code with near-identical SQL, with only the fields and tables changed, and it had worked before. However, after changing the fields and tables, I repeatedly get error 3134 for "syntax". The code was
Code: Private Sub UpdateI_Click() Dim PerName As String, strsubb As String, Popup As Role, Strsub As String PerName = InputBox("What is their name?") strsubb = "INSERT INTO Company-personID (CompanyNo, Name) VALUES ('" & Me.CompanyNo & "', '" & PerName & "');" DoCmd.RunSQL strsubb
[Code] ....
You can ignore the userform and inputbox if they're not important in this, but I figured they might just be. When I run "Strsub" and "strsubb" by themselves, they both come up with errors, so it's not one or the other. There are two tables in this. A table of companies, which has its form open, and a child table of companies and their people.
I wanted strsubb to put in a record where a person is added, their name based on the inputbox, and their associated company to be marked as the one that is in the field "companyno".
Strsub is meant to set the same person's roles to be whatever was clicked on the userform. If it's important, the roles are in a lookup wizard with multiple choices.
I am trying to create an INSERT statement from a form to put unbound fields in a table. The challenge that I am a getting is that I am getting a
Run-time error '3075' Syntax error in date in query expression '#'
What is really perplexing and perhaps something that may guide in identifying the culprit is that I have an identifcal form that uses the identical code and it works.
Here is the code below:
Dim strSQL As String Dim strCriteria As String strSQL = "" strSQL = strSQL & " INSERT INTO [tblTicket]"
I run a Pass through query against a SQL-Server from Access 2010.I have a field TimeWorked which has values like 1899-12-30 03:30:00.000, for 3:30 hours and a field PaymentPerHour with values like 10.50 (for EUR 10,50).The result schould be 36,75 (36.75).
I tried to calculate like this: Round(convert(varchar,[TimeWorked],104)*1440*[PaymentPerHour ]/60,2)AS TtlPayment
In an Access query it works with the connected table in the accb, but not against the server.I get a lot different errors when trying to convert into int, datetime, etc. but could not solve my problem.
I have a schedule table which contains a field for the start date of the schedule and another field that contains the start time of the schedule. I want to combind these two fields in a sql statement so that I can query for a range of schedules based on the date and time. The below sql statement works fine in a query, however, when I tried to use it in a opening statement in ADODB, it does not work. ADODB somehow does not like the combinding of the date and time fields. Can someone point me to the right direction in achieving the same result.
mysql = "SELECT tbl_Customer_Site_Schedule.*, CDate([startdate_sch] & " " & [starttime_sch]) AS StartShift FROM tbl_Customer_Site_Schedule WHERE (((CDate([startdate_sch] & " " & [starttime_sch]))>=#11/5/2007 20:0:0# And (CDate([startdate_sch] & " " & [starttime_sch]))<=#11/7/2007 20:0:0#)) ORDER BY CDate([startdate_sch] & " " & [starttime_sch]);"
I am trying to write an expression that will result in a date in the format (mm/dd/yyy). I want to display the curent day if it is before 11:00 am based on the system time, and the next day if it is after 11:00 am. Below is the expression that I currently am working with... but it is not working.
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.
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?
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))"
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
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
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"))
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
Code: If Nz(DCount("*", "[Tblupdate]")) = 0 Then ' The count is zero i = 1 Else i = DMax("ID", "tblupdate") End If Dim ssql As String Dim j, k As String j = "P" & i k = Environ("username") ssql = "Insert into tblUpdate(Update_ID,Date,Username) values('" & j & "',#" & Format(Date, "dd/mm/yyyy") & "#,'" & k & "')" CurrentDb.Execute ssql, dbFailOnError
I'm trying to derive the month in digits from a date/time stamp field. Any month between 10 and 12, I need the first 2 digits but anything <=9 is only 1 digit.
MONTH: IIf(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],2)="10" Or "11" Or "12", (Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],2),(Left([LOAD TO SKU LEVEL DETAIL]![PICK_PLAN_TS],1))))
Anybody see anything wrong the syntax for the below query. I'm trying set up this query to pass a string to 'struser' for multiple users. I'm getting 'Syntax error in FROM clause'. Thanks ! ! !
Dim strSQL As String Dim struser As String struser = "MIKE" Set db = CurrentDb() Set qdf = db.QueryDefs("qryUser") strSQL = "SELECT Salable_Figures.Loan_Originator_Code," & _ "Salable_Figures.Loan_Originator_Name," & _ "Salable_Figures.Lastname_Sort," & _ "Salable_Figures.Team, Salable_Figures.Plan," & _ "Salable_Figures.Monthly_SCountOfLoan_Program_Code AS Monthly_SCount, " & _ "Salable_Figures.Monthly_SSumOfNote_Amount," & _ "Salable_Figures.Salable_Monthly_Goal," & _ "Salable_Figures.YTD_SCountOfLoan_Program_Code AS Yearly_SCount," & _ "Salable_Figures.YTD_SSumOfNote_Amount AS Yearly_SSumOfNote_Amount, " & _ "Salable_Figures.YTD_SSumOfNote_Amount AS Yearly_SSumOfNote_Amount," & _ "Salable_Figures.Yearly_Salable AS Yearly_Salable_Goal " & _ "FROM Salable_YTD_Figures INNER JOIN Salable_Figures" & _ "ON Salable_YTD_Figures.Loan_Originator_Code = Salable_Figures.Loan_Originator_Code " & _ "WHERE Salable_Figures.Loan_Originator_Code)=" & struser & _ "';'" ' Apply the new SQL statement to the query qdf.SQL = strSQL ' Open the query DoCmd.OpenQuery "qryUser"
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]);
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);
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.
