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 Replies


ADVERTISEMENT

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

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

Modules & VBA :: Syntax Error In Sql Statement

Dec 9, 2014

sort this error out:

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

View 2 Replies View Related

Syntax Error In 'FROM' Statement For StrSQL String

Feb 7, 2006

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"

View 1 Replies View Related

Syntax Error -- Case Statement In Query

Jan 16, 2008

Hi,

I received syntax error for the following statement

SELECT
CASE [File Type]
WHEN 'Security' THEN 'SEC'
WHEN 'Admin' THEN 'ADM'
END AS FT
FROM [tbl_Core Non-Core]


please advise

thanks

Alice

View 3 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 :: 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

Forms :: Like Command In IF Then Statement - Syntax Error

Jun 24, 2015

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

View 3 Replies View Related

Queries :: Syntax Error Using IF In SELECT Statement

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

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

Queries :: Syntax Error (missing Operator) On IIF Statement

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

Create Table Statement Syntax Error In Access 2000

Aug 7, 2005

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)
)

View 1 Replies View Related

Create Table Statement Syntax Error In Access 2000

Aug 8, 2005

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))

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

Queries :: IIF Statement - Syntax Error (comma) In Query Expression

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

Queries :: Average IF Statement-syntax Error (missing Operator) In Query Expression

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

Forms :: Syntax For Nested Aggregate Functions?

Feb 4, 2014

I have a problem with a nested arrangement of Right, DLookup and DMax functions.

The function is for a default value in a text control

=Right(DLookUp("[SampleNumber]","tbldat14A_MasterSampleList","[SampleID]=" & DMax("[SampleID]","tbldat14A_MasterSampleList"))+1000001,6)

I am trying to retrieve the last record from a field SampleNumber which is alphanumeric (e.g. "AK005434") and then add a 1 to it as the next SampleNumber. I had previously used a default value in the txtSampleNumber control as

="AK" & Right(1000000+DMax("[Clip]","[tbldat14A_MasterSampleList]")+1,6)

where [Clip] was a field I have calculated in the table to chop off the preceding characters. Adding 1,000,000 and taking the 6 right hand values and concatenating with "AK" gave me the answer, though it is a but primitive.

This all worked until the SampleNumber value got out of order and blocks of SampleNumber values came in that were then followed by blocks of numbers with lower values (say AK005001-AK005050 followed by AK002001-AK002050).

Now I figure if I just recall the latest entry by DLookup and criteria of DMax on the SampleID (Autonumber Primary key) I could get at the value. I have done this to some success using default values in a series of unbound controls like

=DLookUp("[SampleNumber]","tbldat14A_MasterSampleList","[SampleID]=" & DMax("[SampleID]","tbldat14A_MasterSampleList"))

to get the SampleNumber I require, then a Right function to trim in another unbound box and then use that last unbound box as the default value for the txtSampleNumber control that is the entry for the data table. However, the unbound control box is only valid for the first record and does not update. So, I added a macro that closes the form and reopens it. This all works but is a bit agricultural. I would like to do a single nested function to the default value of the txtSampleNumber control box. Is it possible to nest Right, DLoopkup and DMax into one statement?

View 14 Replies View Related

Forms :: Syntax To Refer To A Control On Nested Subform In Access 2013

Mar 20, 2014

I have searched to find the correct syntax to refer to a combo box control on a nested subform. All the examples I've found Access 2013 will not recognize or find the appropriate control.

I have a parent form called IncidentDetails. On that form I have a control called ctrlLogDetail. Within that control is a form called sfrm_LogDetail. On sfrm_LogDetail, I have a control called ctrlType which houses a form called sfrmType. On sfrmType, I have a combo box called cboType. I need to be able to place the cboType choice into a query to filter records for another combo box on that same form. the query works appropriately when I have sfrmType open, however as soon as I try to call the query from the IncidentDetails form, Access cannot find the cboType control.

I've tried multiple variations of the syntax to call to cboType that I've found online. I found a very useful reference from BTA Development: however the syntax there will NOT work in Access 2013.

What is the appropriate syntax would be to get to my control within my 3 deep nested subform? I'm working Access 2013 and won't have a choice regarding Access versions.

View 5 Replies View Related

Complex Nested If Statement

Jan 10, 2007

Hello,

I have been working on a query for a few hours now to create a new field based on data in other fields in my table. Here is the code I am using:

MBkt: IIf([Table14].[OWNERGRP_1]<>"",IIf([Table14].[OWNERGRP_1]="ADVO",IIf([Table14].[PRICINGMKT]="SHAREDEX","SEZ"),IIf([Table14].[PRICINGMKT]="REP","REP"),IIf([Table14].[PRICINGMKT]="RIP","REP"),IIf([Table14].[COV_FREQ]="Weekly PCD","PCD"),IIf([Table14].[COV_FREQ]="Weekly","ADVO WKLY","ADVO MULTI-WEEKS"),IIf([Table14].[OWNERGRP_1]="ANNE",IIf([Table14].[OWNER_1]="MMSI/MAIL MARKETING/ANNE","ANNE MMSI","ANNE"),"SOLO"))

Unfortunately, I am unable to get this to work. Any help that anyone can provide is greatly appreciated.

Thanks,

-Brian

View 6 Replies View Related

Nested Iif Statement! Not Sure Why This Query Will Not Run...help!

Aug 12, 2007

I'm wanting to run an update query but first I want to see the results by using a select query to make sure that I'm getting the correct results.

See the following query statement:

SELECT ([Goodrec - All] INNER JOIN WH_ACCTCOMMON ON [Goodrec - All].ACCTNO = WH_ACCTCOMMON.ACCTNBR) INNER JOIN [Trial-GL-Reference] ON WH_ACCTCOMMON.CURRMIACCTTYPCD = [Trial-GL-Reference].LOANTYPE SET [Goodrec - All].GLCODE = [TrialTotals].[glcode], [Goodrec - All].CURRBAL = IIf(Not IsNull([chgoffamt]) And Not IsNull([partsold]),CCur([grossbal])-CCur([chgoffamt])-CCur([partsold]),IIf(Not IsNull([CHGOFFAMT]),CCur([grossbal])-CCur([CHGOFFAMT]),IIf(Not IsNull([partsold]),CCur([grossbal])-CCur([partsold]),[grossbal]))), [Goodrec - All].PCTOWNED = IIf(Not IsNull([origamt]) And Not IsNull([partsold]), CCur([origamt])-CCur([partsold]/CCur([origamt]), IIf(IsNull([partsold]), [grossbal]));


This is the error that I receive each time I attempt to run it and I'm not sure what I'm missing.

Error message:

MICROSOFT OFFICE ACCESS
Syntax error(missing operator) in query expression '([Goorec-ALL] Inner Join WH_ACCTCOMMON ON [Goodrec-All].ACCTNO=WH_ACCTCOMMON.ACCTNBR) inner join [TRIAL-GL-Reference].LOANTYPE SET [Goodrec - All].GLCODE = [TrialTotals].[glcode]'


Any help with this issue would be greatly appreciated.

Thanks.

Govmate!

View 3 Replies View Related

Nested SQL Statement In A Function?

Apr 18, 2008

I have a table of dates that store all the Sundays of a year, as that is what all of our records are based on (how many items an employee sold on the week ending on such and such a date). The table is called WeekEndDates and the column is called WeekEndDate. I want to automate adding a new week, so I have a command button that has the following VB code:

DoCmd.RunSQL ("INSERT INTO WeekEndDates (WeekEndDate) VALUES (DATEADD('D',7, SELECT Max(WeekEndDate) FROM WeekEndDates)")

Any help would be appreciated, I don't know of another way to do this. I'm not sure if this is a SQL question or a VB question, so sorry if this is in the wrong spot.

View 1 Replies View Related

Queries :: Nested IIF Statement In Date Calculation

Jul 30, 2015

I am trying to calculate the time between two dates where one date field might be blank or not. Where the field is blank I want to use the current date to perform the calculation. So far I have the following but I keep receiving an error message saying that the expression has a function with too many arguments. Is there a simpler solution to this?

IIF([LastOfEnd_Date] IS NULL,(DateDiff(w,[LastOfDischarge_Date],NOW()))/4, IIF(Not isnull([LastOfEnd_Date], Abs(DateDiff("w",[LastOfDischarge_Date].[LastOfEnd_Date]))/4

View 5 Replies View Related

Queries :: Make A Nested If Then Statement In A Query Field

Apr 27, 2014

I'm trying to make a nested if then statement in a query field, and I can't figure out why I can't get my formula to work:

Volume: IIf([MethodCode]="K",[total]*12.54*0.026873,IIf([MethodCode]="S",([length]*[width]*[depth])/2,IIf([MethodCode]="M" And [Location]="SH",[total]*5.08*0.026873,IIf([MethodCode]="M" And [Location]="C",[total]*18.58*0.026873," "))))

I keep getting the "data type mismatch in criteria expression" error. If I separate out all the individual if then statements individually, they work. But if I connect them all as a nested if then it doesn't work.

View 6 Replies View Related







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