Iif Statement With Or Operator ??
Apr 9, 2007
I have an expression that I'm trying to build into a totaling field in a report. There are two types of items being tallied into this total; 1 is a flat amount, the other is a calculation of # of men * # hours * rate. I already have an expression that works for the first level of calculating:
=IIf([Type]="Lawn Maintenance",[LMAmt],[# hours]*[# men]*35)
Note: [LMAmt] holds a flat amount
I wanted to add, after the [Type]="Lawn Maintenance",[LMAmt] an "or" somehow which would basically state or [Type]="Dumping Charge",[Expenses]
Note: [Expenses] is also a flat amount and all other [Type] codes are determined using the calculation below:
if [Type] is not = to "Lawn Maintenance" or = to "Dumping Charge", THEN [#hours]*[# men]*35
Hopefully I explained myself well :confused:
Thanks in advance!
vnorcliffe
View Replies
ADVERTISEMENT
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
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
Feb 6, 2006
Hi
I have been refreshing myself with SQL on Access 2002. All was going well until I got to the BETWEEN...AND operator. Using the Northwind sample database I was writing the query on the employee table to select all the columns between 'Fuller' and 'King' :
SELECT * FROM Employees
WHERE LastName
BETWEEN 'Fuller' AND 'King'
However this only brings back the two rows for 'Fuller' and 'King' whereas I would expect at least 5 rows or more depending on whether access would count 'Fuller' and 'King' as inclusive.
Have had a look on the microsoft office help page but seem to be doing everything correct so I am wondering why it still doesn't work? No doubt simple but its concealed in mud at the moment. Any help apreciated.
cheers
View 13 Replies
View Related
Sep 26, 2005
I am new to this forum, so forgive me if this question has been asked before
I needed exact information about the way the 'like' operator works, so I pressed F1, typed 'like' in the "Answer Wizard" and was told to "rephrase my question". :eek:
The index was slightly more helpful as it obviously contains 2 topics with 'like_operator', but choosing them does not reveal anything. :confused:
Where can I find info about such things?
I also need to use an operator called 'contains' which I have used when working with SAS, but appearently it does not exist in Acccess or it has another name. Has anybody heard about it?
View 2 Replies
View Related
Nov 15, 2005
Hi guys!
It's unknown for me why Access hasn't got MINUS implemented and Unmatched Query Wizard doesn't help me much. So I'm wondered if anyone had to confront the following task:
Table1
Book Agent
----------
b1 a1
b2 a2
b3 a3
Table2
Book Agent
----------
b1 a1
b3 a3
The problem is I have to eliminate duplicates but I need to find unique row, which is Book and Agent. From my example it's row with (b2 a2). I tried using
Select * From Table1
Where (Book, Agent) Not In (Select Book, Agent From Table2)
didn't work
I also tried EXISTS
didn't work
Unmatched Wizard compares only one field, not two.
Any comments welcome, thank you
View 2 Replies
View Related
Jan 10, 2007
I have an invoice-line table with records for every line on invoices. I'm trying to return all the lines associated with certain invoices, excluding all lines with "FREIGHT" in a line item field. I created a query that lists all the invoice numbers for which I want to display invoice lines for. I created a new query with the invoice line table linked to the invoice number query by invoice number (a one to many relationship). There are about 1400 results from this query. By making the criteria of the line item field "FREIGHT" and running the query, I can see that there are 81 "FREIGHT" records that I ultimately want to exclude. However, by typing NOT "FREIGHT," it appears that not only are the "FREIGHT" line item records excluded, but also records with a blank (null)line item field are excluded, and I end up with about 650 results. What's going on here? Why is the NOT "FREIGHT" critera also excluding null records? I don't have any other criteria specified in the query. :confused:
View 5 Replies
View Related
Apr 26, 2007
I have a select query that has filenames listed. I would like to use the Like Operator to select only those filenames with certain bits of string.
For example: In the Criteria area of a query Like "*Defense*" selects all filenames with the word Defense successfully.
However, I would like to select the key word from a combo box and use it in the Like expression.
For example:
Like "* '&Forms![View Selected Plays]![Play Type]&' *"
The code above does not return any results even if the term Defense is selected in the combo box.
I am sure this is a quotes, single quote, *, % type problem, but every combination I try yields no results.
Thanks.
View 2 Replies
View Related
Sep 3, 2005
I want to create a query that extract rows that has a specific word.
i.e.
select * fro table where field like "*ART*"
however it should not retrieve "DART" or "ARTI"
i tried using the following [!a-z] and * like this:
SELECT * from DLs where DLName like "*[!a-z]ART" or DLName like "*[!a-z]ART[!a-z]*" or DLName like "ART[!a-r,s,!t-z]*" or DLName like "ART"
it is very long and complicated - cant i cover it all with one like ?
Also the fact that i also want it to get ARTS (i dont know how to say !a-z expect 's')
Thanx in advance
View 2 Replies
View Related
Jan 11, 2006
Below is the code that concatenates a pathway ('c:asset_captureimages) and a column value (c:asset_captureimages)to populate another column ([Carriageway].[Hotlink])
------------------------------------------------------------
Function Update_Image()
For Each tbl In CurrentDb.TableDefs
If tbl.Name = "Carriageway" Then
strsql1 = "[" & tbl.Name & "]" & ".[hotlink]"
strsql2 = "[" & tbl.Name & "]" & ".[position]"
'**** THE LINE BELOW WORKS****
'CurrentDb.Execute "UPDATE Carriageway SET [Carriageway].[Hotlink] = 'c:asset_captureimages' & [carriageway].[position]"
'**** THE LINE DOESN'T WORK WHEN STRING VALUE REPLACES COLUMN****
CurrentDb.Execute "UPDATE Carriageway SET [Carriageway].[Hotlink] = 'c:asset_captureimages'" & strsql2
End If
Next
End Function
--------------------------------------------------------
The module works perfectly when the column value is hardcoded as [carriageway].[position] but when a string is replaced as strsql2 then the error message "Run Time Error 3075 Syntax Error (Missing Operator) in Query Expression" is generated.
Any ideas gratefully received what seems a simple problem that is causing significant swearing and hair loss.
Thanks
Paul
View 2 Replies
View Related
Jan 28, 2007
well here's my problem, i've been working on this homework for about 3 days i got most of my queries work except for the 2 that use nested or subqueries,
i have 2 tables
http://img251.imageshack.us/img251/4623/untitledko5.png
everytime i run the query i get this
http://img251.imageshack.us/img251/7645/untitled2rq0.png
i want the query to show me the costumers that have not made any orders, basically it should show me ID #6 no order,
when i use
SELECT Costumer.ID, Costumer.fname, Costumer.lname
FROM Costumer INNER JOIN [Order] ON Costumer.ID = Order.tblCostumer_ID
WHERE (((Costumer.ID) Not In ([Order].[ID])));
it returns the above pic of a blank query
this is what my POS book says i should type:
SELECT Costumer.ID, Costumer.fname, Costumer.lname
FROM Costumer
WHERE ID NOT IN (SELECT ID FROM Order);
but everytime i try that i get a syntax error after the NOT IN.
i don't know what else to try if anyone could help me out it would be greatly appriciated.
View 3 Replies
View Related
Sep 4, 2007
Hi All
Can't understand why the following doesn't work when changing the operator.
Using the "<>"
SELECT qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.Shift FROM qryColleagues, tblTempNew WHERE (((qryColleagues.WCN)<>[tblTempNew]![WCN])) GROUP BY qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.Shift ORDER BY qryColleagues.ClgName;
the particaular part is (((qryColleagues.WCN)<>[tblTempNew]![WCN]))
it works if the operator is "=" but not "<>"
The thing is I'm sure this was working fine.
edit: I have just noticed that this works if there is only one record in the tblTempNew table. As soon as I put 2 records in there it does not work.
Any ideas.
Kev
View 7 Replies
View Related
Sep 17, 2007
Hi everyone!
I'm working on creating a small database for the company I work at and just can't figure out the syntax to make this work.
If I create a query that looks for clients Like '*Smith*' , it returns records where the client is John Smith, Smith and Company, Edward Smith & Jones, LLC and that part works great! I did this by typing: Like '*Smith*' in the criteria field for Client in the design view of the query. (Also seems to work whether I use single or double quotes - not sure about the implications of that).
Now, I want to replace the word Smith with whatever a user enters into a text box, I can't get the syntax right! In the design view of the query, I've tried things such as: Like '*forms![Search by Client]![Text0]*' with no luck. Any ideas? Maybe I'm missing some brackets or parenthesis?
View 3 Replies
View Related
Nov 11, 2007
Hello everybody,
I am having trouble with this criteria on one field in a query
<>19 Or <>15 Or <>14 Or <>16 Or <>17 Or <>1 Or <>3
The query is still showing records that contain 14, 3 and 16.
Basically, I don't want to show records containing 19, 15, 14, 16, 17, 1 and 3.
Thanks
View 3 Replies
View Related
Nov 28, 2007
I have 2 tables, an Oracle source table and an Access table. The data in the Oracle table has Item names, however they tend to end with month and date and other misc info. I set up the Access table with the cleaned up versions of the Item names (took out the excess crap) to be able to set up a query that would pull in data from the Oracle table that had Item descriptions Like the descriptions in the Access table. That is working great, however, i would also like to pull in another field from the Access table to categorize these items in to groups. Once I do this, each record shows up 4 times (the number of categories that exist). How do I link these tables to allow me to do this? None of the standard link types work since the fields are not equal but are Like each other.
View 12 Replies
View Related
Jul 3, 2006
I have two tables in the first table there is an outline number and the second table there is a task id (both are text strings, i.e. outline number and task id would be something like 8.15.3.1)
I want a query to return all the results in the first table that are not equal to the second table
I have tried the following but it returns all 54 records
Code:SELECT DISTINCT RawData.TeamLeadNumber, RawData.Category, RawData.OutlineNumber, RawData.OutlineDescription, RawData.Start, RawData.Finish, RawData.Milestone, RawData.PercentComplete, RawData.DeliverableDesc, RawData.ReleasePeriod, RawData.OutlineNumber2, RawData.OutlineDescription2FROM RawData, SharePointDataWHERE (((RawData.OutlineNumber)<>SharePointData.TaskID));
I have tried changing the where clause to have it as
Code:WHERE ((RawData.OutlineNumber)<SharePointData.TaskID) AND ((RawData.OutlineNumber)>SharePointData.TaskID);
but then no data is returned, and I know that of the 54 records 21 of them are the same so I should be seeing 33 records
View 1 Replies
View Related
May 30, 2007
Hi everyone,
I have a problem with a query.
Just for info, I export some table from SQL to access. The same query in SQL work fine but when I try in Access come out this error:
------------------------------------------------------------------
Syntax error (missing operator) in query expression 'tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid'.
------------------------------------------------------------------
The query:
SELECT tbl_Style.STYLECODE, tbl_Info_1.Infodata AS [Fabric Type]
FROM tbl_Style INNER JOIN
tbl_Info ON tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN
tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid
I appreciate your help.
Regards,
Tombino
View 3 Replies
View Related
Jul 3, 2014
I am trying to create a query in VBA to extract data for a report.
I am using the "Like" operator for one of the variable fields.
It works correctly if you use the full value, however when using a wildcard it returns no records. Example below.
MOP - Works returning several records.
M* - returns no records.
I am getting very frustrated - I am sure its something simple.
I hope I have uploaded a screen dump of the SQL statement that is being produced.
View 14 Replies
View Related
Sep 6, 2012
Can I use the or operator in Access 2010
IIf(Mid([WBCode],4,1)="D" or "N"....
I am finding that I can use AND and LIKE, but not seeing the OR
View 1 Replies
View Related
Jan 16, 2006
I have built a function which passes values into the criteria of a saved query. It has worked very well until I recently built a query builder based on this same concept. The user can select from a list of operators "<=", ">=", ">", "<", "=".
Example, the function will pass into the criteria the following:
<150
Unfortunately, I have exhausted all avenues for passing the "<" less than or greater than signs into the criteria. The Access SQL forces an equals sign before the passed in criteria and thus treats the "<150" as the entire criteria.
I have overcome no criteria by defaulting the function value to pass in the wildcard "*" and putting "Like" in the criteria.
Has anyone every handled a "variable" operator in a saved query in Access? :confused:
I am aware that I can change the QueryDef, use temp tables, and do several other longer methods, but the success of this concept will reduce an large amount of coding effort as well as make the software much easier to transfer knowledge on.
This overall method is really a great way to reuse criteria based on a form without hard coding.
Thank you in advance for your help,
Dave
View 3 Replies
View Related
Jun 26, 2006
Hi, can anyone help ? I have 2 tables 1 called orderforminput and another called customerdetails. If i run the following sql statment on the orderforminput table it works just great.
"SELECT orderforminput.* FROM orderforminput " & _
"WHERE orderforminput.[Customer] " & strCustomer & _
strCustomerRefCondition & "orderforminput.[Customer Ref] " & strCustomerRef & _
strDoorNumCondition & "orderforminput.[Door Num] " & strDoorNum & ";"
With this code i am trying to retrieve customer name and address from the second table and conbine the results in a query called invoicequery. I keep getting syntax error in query missin operator in query expression. I am new to access and sql can any one shed some lite PLEASE
"SELECT orderforminput.*, CustomerDetails.* FROM orderforminput INNER JOIN CustomerDetails ON orderforminput.Customer = CustomerDetails.Company" & _
"WHERE orderforminput.[Customer] " & strCustomer & _
strCustomerRefCondition & "orderforminput.[Customer Ref] " & strCustomerRef & _
strDoorNumCondition & "orderforminput.[Door Num] " & strDoorNum & ";"
View 4 Replies
View Related
May 29, 2007
Hi everyone,
I have a problem with a query.
Just for info, I export some table from SQL to access. The same quary in SQL work fine but when I try in Access come out this error:
------------------------------------------------------------------
Syntax error (missing operator) in query expression 'tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid'.
------------------------------------------------------------------
The query:
SELECT tbl_Style.STYLECODE, tbl_Info_1.Infodata AS [Fabric Type]
FROM tbl_Style INNER JOIN
tbl_Info ON tbl_Style.FABRICID = tbl_Info.Infoid INNER JOIN
tbl_Info AS tbl_Info_1 ON tbl_Info.Parent = tbl_Info_1.Infoid
I appreciate your help.
Regards,
Tombino
View 2 Replies
View Related
Aug 1, 2007
Good afternoon,
I have a field that contains semester info ie: F07 Sep.
What I am trying to do is using an if statement to test for which month and returning a section number based on the month. I can make it work using an exact match but am trying to make it work on the partial match "Sep" so I don't have to change it next year as F07 stands for Fall 2007
This is what I've tried and various combinations.
Section1: IIf([tblOnlineCoursesDetails]![Semester]="* Sep","70","")
Having trouble getting this to work.
Any help or suggestions would be appreciated.
Thx
Jerry
View 1 Replies
View Related
Jan 19, 2015
Code:
Dim AppendUsers As String
AppendUsers = "INSERT INTO tblPermissions ( empid )" & _
"SELECT tblEmployee.empid" & _
"FROM tblEmployee LEFT JOIN tblPermissions ON tblEmployee.[empid] = tblPermissions.[empID]" & _
"WHERE (((tblPermissions.empID) Is Null) AND ((tblEmployee.active)=Yes) AND ((tblEmployee.inputname)=Yes));"
If MsgBox("This will search for new users with valid logins and add to the permissions table, do you wish to continue?", vbYesNo, "Append Users") = vbYes Then
DoCmd.RunSQL (AppendUsers)
View 4 Replies
View Related
Jul 21, 2013
I am trying to select specific records based on input in a query. This criteria currently works to select everything with an ID less than 21 or 31 based on the users entry: <IIf([Enter "Y" for List 1]="Y",21,31)
I now need to change the query so that only certain records are selected based on the users entry. As an example, I would like to select 1, 3, 5 and 7 if they enter Y. And I would like to select 2, 4, 6 and 8 if they dont enter a Y.Here is what I have tried:
In IIf([Enter "Y" for List 1]="Y",(3,5,7,9),(2,4,6,8)).With this criteria, I get this error .The In operator you entered requires parentheses.
View 5 Replies
View Related
Aug 1, 2015
I have a Listbox and when i double click on a selected record (Student Name), I receive a syntax error. I am trying to open a form containing the student information.
VBA:
Private Sub List1_DblClick(Cancel As Integer)
DoCmd.OpenForm "NEP", , , "[Student Name] = Forms!NEP!Student Name"
End Sub
View 10 Replies
View Related
