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))"
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
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);
Can I use the Like command in an If Then statement? I'm getting a syntax error
If [Primary1Name] Like 'PO*' Then MsgBox "You cannot use a post office box address. Use a residence address.", vbOKOnly, "Incorrect Address Information" DoCmd.GoToControl "Primary1Address1" Else End If
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?
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?
Hi guys i tried to run this create table statement and each time i get syntax error. I pasted the code in sql view windows of access 2000 and pressed the run code and i get this error massage saying there is syntax error. Could any one help me write correct create table statement that does not give me this error.I know u might tell me why u do not create table in design view or .. but i want to do this since i want learn this method as well.thanks
CREATE TABLE PLAYERS (PLAYERNO SMALLINT NOT NULL CHECK (PLAYERNO >0), NAME CHAR(25) NOT NULL , INITIALS CHAR(5) NOT NULL , BIRTH_DATE DATETIME, SEX CHAR(1) NOT NULL , JOINED SMALLINT CHECK (JOINED >=1980), STREET CHAR(15) NOT NULL , HOUSENO CHAR(4), POSTCODE CHAR(6), TOWN CHAR(10) NOT NULL , PHONENO CHAR(10), LEAGUENO CHAR(4), PRIMARY KEY (PLAYERNO) )
Hi guys i tried to run this create table statement and each time i get syntax error. I pasted the code in sql view windows of access 2000 and pressed the run code and i get this error massage saying there is syntax error. Could any one help me write correct create table statement that does not give me this error.I know u might tell me why u do not create table in design view or .. but i want to do this since i want learn this method as well.thanks
Code:CREATE TABLE PLAYERS(PLAYERNO SMALLINT NOT NULL CHECK (PLAYERNO >0),NAME CHAR(25) NOT NULL ,INITIALS CHAR(5) NOT NULL ,BIRTH_DATE DATETIME,SEX CHAR(1) NOT NULL ,JOINED SMALLINT CHECK (JOINED >=1980),STREET CHAR(15) NOT NULL ,HOUSENO CHAR(4),POSTCODE CHAR(6),TOWN CHAR(10) NOT NULL ,PHONENO CHAR(10),LEAGUENO CHAR(4),PRIMARY KEY (PLAYERNO))
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
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?
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 ..."
I have the following statement entered in the query criteria section.
SELECT MAX(EDRMPeriod) FROM EDRAppC WHERE EDRMPeriod Between DateAdd("yyyy",-1,DateSerial(Year(EDRMPeriod),Month(EDRMPeriod)+1, 1)) AND DateSerial(Year(EDRMPeriod),Month(EDRMPeriod)+1,1) GROUP BY EDRCHPlan
I am getting a syntax error. This statement runs fine and produces the results I want in Queryman. Thanks for any help. k
I need to get this syntax right. I have something similar that worked before to open a report but now I am using the same code structure on opening a form and I can't get it.
[prikey] is an autonumber and that has given me trouble before with the syntax. [EstimateFlagCleared] and [WarrantyFlagCleared] are Yes/No fields.
Dim maxFlag As String Dim flagCriteriaWarranty As String Dim flagCriteriaEstimate As String
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.
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
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
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?
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);
