IIF Problems - Multiple Expressions

Mar 28, 2006

Could someone please point out to me what's wrong with this query?

Sum(IIf([ProgCode] LIKE "004*" Or "0058",1,0))

I get a syntax error when I try to run it. If I take out the "OR '0058'" part, the query will evalute properly. However once I try to add in the OR statement it stops working. I have also tried "OR LIKE '0058'" and recieved the same error. In addition to that, I have also tried the equals operator instead of the LIKE operator.

Thanks

View Replies


ADVERTISEMENT

Queries :: Run Multiple Expressions On The Same Data Field In A Single Query?

May 11, 2014

I am trying, and failing quite spectacularly to create a single query that will contain multiple expressions on the same field.

Please see attached jpg for example.

SQL: SELECT Count(StockData.status) AS FaultyPCs
FROM StockData
HAVING (([StockData].[Status] In ("Waiting Repair") And [StockData].[DeviceType] In ("Thick PC")));

What I want to be able to do is then add additional expressions to filter and count in the same way for "Thin PC" "Thin Laptops" "Thick Laptops" and so on.

But when I add one of these expressions to the next column in the builder, it seems to apply itself to the expression to it's left, causing that one to now give a result of 0.

Is there a way of separating one from another or do I have to create a separate query for each expression and then use another query to pull them all together (as I have done in another situations - but this will involve creating probably 20 separate queries.

View 7 Replies View Related

Expressions

Mar 4, 2008

I have a table that has questions that require Yes/No answers, simple done, however now for the tricky (or at least for me, VV new to Access) I need one of the fields to add up all the Yes and asign a value so for example:
Q1 Q2 Q3 Q4 Q5 Result
Y N Y Y N 3
How do I do this, I asume it is down to the expression for 'Field' Result but what should that expression be and where do I put it?

View 7 Replies View Related

Expressions

Jul 29, 2006

Help me what is wrong with this expression:eek: :

=IIF( [eBay Method] ="Auction",
IIF( [Start up Price] <=.99,.2,
IIF( [Start up Price] < = 9.99,.35,
IIF( [Start up Price] < =24.99,.6,
IIF( [Start up Price] < =49.99,1.2,
IIF( [Start up Price] < =199.99,2.4,
IIF( [Start up Price] < =499.99,3.6,
IIF( [Start up Price] >499.99,4.8," "
))))))),
IIF( [eBay Method] ="Buy it Now",
IIF( [Start up Price] <=9.99,.05,
IIF( [Start up Price] <=24.99,.1,
IIF( [Start up Price] <=49.99,.2,
IIF( [Start up Price] <=49.99,.25," "
))))))

View 4 Replies View Related

Unsafe Expressions

Jul 13, 2005

I'm using Windows 2K SP4 and have just installed Office 2003. Everytime I open Access I get a message asking if I want to block unsafe expressions (presumably macros). How can I turn this off?

BTW I've downloaded all of the security updates, which are recommended by MS.

View 7 Replies View Related

Unsafe Expressions...........??

Apr 5, 2007

Hi,

Can anyone tell me why it is that when i have written a DB on one machine using Access 2003 and then subsequently put that DB on to another machine i get an error relating to unsafe expressions?

why does this happen and is there a away in which can stop this error message from appearing?

I am trying to implement the DB i have written at work but it does not look good when i get a constant error message appearing when ever i open the DB.

Thank you in advace for your help

Regards
Bev

View 3 Replies View Related

Question Regarding Expressions

Feb 29, 2008

1)I have a list of stock opening prices and closing prices for March. i need an expression or function that will calculate which company's stock price changed most during the month (difference between opening and closing price). 2)In the stock table, I have dates given to me in the following format mm/dd/yyyy. Lets say I want it only to display the date so it only shows the month but not the year of the day. What function will give me this result?Thanks

View 1 Replies View Related

Questions About Possible Expressions

Jan 8, 2007

I have a complicated question here. I am currently building a db for career development in my workplace. The main fields that i am having some issues with is the following. I have three fields labelled as the following:

1)Last Review date

2)Next review date ( i have this entered in as an auto generated date-- will add one year from the last review date)

3)Status

Now here is my question:

I want my status field to auto generate as well-- say if the last review date was in 2005 and the next review date was dec.12/06-- then the status would currently be overdue.
Is there any expression to use to auto generate the status field to say whether it is overdue or completed? or is there any easier way to do this?

View 5 Replies View Related

Counts And Expressions

Jul 8, 2005

I’m starting to wonder how I ever made anything work, before I found this place.

I built 2 queries and put them in a query

They share a common field

The join type is all record from Query1 and Matching from Query2

Next I added a count field for each

Then I added a calculated field that subtracts the count from Query1 to Query2.

So far it all works

When I add >0 to the criteria for the calculated field a dialog box pops up asking for the values of the fields expressed the calculated field.

View 2 Replies View Related

Text Expressions

Jul 19, 2006

i have a master table with various lookup feilds. i want to create a querie that creates a text expression feild called description and it is based on the info provided in the lookup feild. problem is when i create this querie from the location master it provides the id's from the lookup colums and not their text.

any solution

View 4 Replies View Related

Help Need For Expressions Builder

Aug 1, 2006

Hi experts

I have some problems consists of using Conditions.
I have 4 tables in Access:
tbl1_School names, addresses, ID_Number etc..
tbl2_ School features with Number of Clesses, Number of Students, total Area etc.
tbl3_teaching stuff with names, occcupations, Ages ets. and
tbl4_nonteaching personal with names, occupations. ages, etc.

References is ID_Number of schools
I make Query with relations, but do know how to build Expressions Builder for this:
Question 1.
a)If school have between 300&500 Students there are ig.18 teachers,
b)if schools have between 500&800 Students there are ig.25 teachers and until 1500 Students there are ig. 50 teachers.
Question 2.
c)if school have area until 400 m^2 there are needed ig. 8 nonteaching personel,
d)if school have from 400 till 600 m^2 there are needed ig. 15 nonteaching personel.

Numbers I specified like examples ig. were prescribed bye regulation.

If someone want to help me how can I use Expression Builder or VBA Code it would be appreciated.

It means I should use Condition Commands.

Thank You for help.

View 6 Replies View Related

Using Expressions As Criteria

Jul 17, 2007

Is there anyway to use an if statemet to define the criteria for a query?

as in

if forms!RunReport!cboname <> "" then
query all names that equal forms!RunReport!cboname
end if

also is there any way to use a dlookup function (or another similar function)
to define several criteria for my query

ex.
i have a table, "modules", with a list of modules, each classified as "digital" or "analog" and i want to view all records containing analog modules from a different table, "info"

thanks for any help in advance

View 4 Replies View Related

Expressions In A Query

Feb 29, 2008

Hi all!

I'm new to this forum and could use some help on a simple Access app. I have attached the relations for the db. I need to collect some data to a subform as:
tblFunctionCodes.FunctionCode,
tblAccounts.AccountNumber,
Monday (hours),
Tuesday (hours),
Wednesday (Hours),
Thursday (hours),
Friday (hours),
Saturday (hours),
Sunday (hours)
Where I have the tblEmployees.EmployeeID, tblDates.DateWeek and tblDates.DateYear as variables.
The weekdays need to be an expression with my current design, which seems to work ok from Monday to Saturday, but when I throw in Sunday Access says the query is too complicated.

I have this query to get the data, but with no expressions (can't get it to work):

SELECT tblFunctionCodes.FunctionCode, tblAccounts.AccountNumber, tblRegistrations.Hours, tblDates.DateWeekDay
FROM tblFunctionCodes INNER JOIN (tblEmployees INNER JOIN (tblDates INNER JOIN (tblAccounts INNER JOIN tblRegistrations ON tblAccounts.AccountID=tblRegistrations.AccountID) ON tblDates.DateID=tblRegistrations.Date) ON tblEmployees.EmployeeID=tblRegistrations.EmployeeI D) ON tblFunctionCodes.FunctionCodeID=tblRegistrations.F unctionCodeID
WHERE (((tblRegistrations.EmployeeID)=[intEmployeeID]) AND ((tblDates.DateWeek)=[intWeek]) AND ((tblDates.DateYear)=[intYear]));


I've been trying to use that as a foundation for other queries but I just can't get it to work. Does any of your guys have any ideas? Maybe an INTERSECT query??

Anyway I'm out of ideas and very pressed to makes this work. Any ideas are welcome!

Thanks in advance, jon.

View 10 Replies View Related

Help With Expressions On Forms

Jul 6, 2005

Hi all, I'm writing a database basically as a favour to someone. I'm a sql analyst/programmer so my access and vba skills are a little limited and I hope someone can help me with a problem I have.

Part of one of my forms is a flow chart style set of Yes/No questions and the user answers each question. For each question I have created a (hidden) text box which calculates a score (5 for no, 10 for yes) which I have done with a IIF expression in the Control Source of the text box. These are then totalled to give an overall score.

This score is then used to calculate a grade (A or B) and a percentage score using the same IIF method in the control source.

What I want to do though, is use this grade and percentage and write it to the individual record as data in the table - to use in reports and such like.
Is there any way I can do this? I've tried a few bits of VBA but to no avail. Is there a way that this can be done? Or have I approached this in entirely the wrong way??!!? :confused:

Any help would be greatly appreciated!

View 2 Replies View Related

Expressions In Forms

Nov 24, 2005

Hi,

Is there a way on Access that when a total is calculated using an expression on a form that it could update the table itself with that total?

Thanks for any help!


Roly

View 1 Replies View Related

Using Switch Functions In Expressions.

Oct 10, 2005

i'm trying to use a switch function in a query but i'm unfamiliar with the format a bit. here is what i'm trying to do...

switch([Radios]![Warranty] and [Radios]![HousingWarranty]=true,[Radios]![Change front housing]*(25/60*28), [[Radios]![Warranty] And [Radios]![PortableHousingWarranty]=true], [Radios]![MaterialsUsedPrice1])

basically first statement is condition second is execution. I need to add more but i'm getting an error from this much...if I was doing this in vb.net I would just do if, ifelse, ifelse, ifelse...but...

Thanks,

View 2 Replies View Related

Unsafe Expressions A2K2

Jan 9, 2007

I have recently deployed an application at work [work pcs all running A2K2], built with A2K3 on home computer.

I split the database housing the backend on our server, named server, and using frontends on users pcs.

I am running a VB script that takes information from another linked db on server (so everything would be housed together and not on users pc) this script runs a query converts to xml, opens FTP and uploads the query along with .pdfs to the internet.

I can get it to work from home, no problem. I can get it to work from my pc at work.

But when I try on other pcs it can not find the db (error msg) or opened by another user in exclusive mode, yes I explored this option I dont think this it. 1) Cause the frontend can see the backend and everything works from there. 2) That pc is the only one with an open frontend. 3) When I try opening the actual db from the networked shared folder I get an unsafe expressions alert and can not open. The VB needs to open pull from the 2nd db. Both the backend and 2nd db I get this message.

Can anyone tell me how I can change this, the msg box says its out of trusted network. So I copied and actually opened on local pc, I could open it. Move it back to server and no such luck.

Thanks for any posts, I am having to get this working before I load for other branch offices.

I have googled and searched forum, seems everything is geared towards "sandbox".

View 4 Replies View Related

Looking Up Calculation Expressions In Table

Feb 22, 2006

First off, I am not sure if this belongs in the queries forum but it seems most of the question is geared towards a table so I have posted it here.

At the moment I have a query that contains several fields that use nested IIf statements to determine which set of rules to apply. I was wondering if it is possible to somehow set the IIf variables up in a table and then for just to lookup the table in the query.

For example, one of my simpler IIf's looks like this...

Cost:
IIf([AType]='Skill',
IIf([StartDate]<#01/08/2004#,
IIf([AOP]>0,
([BasicRate]-[Frame])/[AOP]/100*[Proportion]),
([BasicRate]-[Frame])/[OP]/100*[Proportion]),
([BasicRate]-[Frame])/[OP]/100*[Proportion])


So I was thinking could I setup a table to show:


FieldName AType StartDate AOP Calculation
Cost Skill <#01/08/2004# >0 ([BasicRate]-[Frame])/[AOP]/100*[Proportion])
Cost <>Skill Is Not Null Null ([BasicRate]-[Frame])/[OP]/100*[Proportion])


and then somehow perform the calculation rather than just look it up.

The main reason is it would be so much easier to see what is being calculated rather than try and weed my way through nested IIf statements.

Can anyone shed any light on this or maybe even a better way to accomplish what I am after?

View 1 Replies View Related

Query Calculation Expressions

Nov 28, 2005

Is there a way or what is the expression used if I want to do a running total in a query under a field called "Amount Donated". In addition, I would like to filter by selection using the DonorID as the primary key to show the total.

Thanks.

View 1 Replies View Related

Date Expressions For Use In Query

Feb 6, 2006

I'm trying to create a query which will retrieve holidays for a specific week, so it would allows me to enter WeekDesc (e.g. week 1) and Line (e.g. Line 1) and then retrieve some information relating to holidays. I have gotten this far already.

However, I've just realised however that for example, if I enter Week 1 and Line 1, it will only retrieve those records for which (in the Holidays Table) have Week Description as Week 1 (this other fields in the table are HolidayID (pk), PersonID (pk), StartDate (pk), EndDate and Approved (checkbox). However if the holiday runs for over a week and runs into Week 2, if i run the query for Week 2 and Line 1, it will not retrieve that holiday.

View 8 Replies View Related

Problem With Using Expressions With Strings

Mar 2, 2006

Im relatively new using queries.

I'm confused. I developed a subset of data in the same general format as our main database. When running select and update queries on this subset, I am able to use Left, Right and Len expressions such as "Right([Year],2)" and "Right([Latitude],Len([Latitude])-3) to update and generate new fields of edited data. However when I use the same expressions on the same tables and fields in the main database, I get a reply such as Function is not available in expressions in query expression 'Right([Year],2)'. A coworker says he has encountered the same problem. Has anyone else encountered this and found an interpretation/solution????

View 2 Replies View Related

Using Expressions In An Update Query

Mar 22, 2007

I want to be able to update stock levels by clicking a button on a form. The button executes code to run the updatequery. Details of Items are listed on a sales form. The fields include item code and quantity sold. Details of the items are stored in a table called 'Items' - fields include item code and quantity in stock. I want the clicking of the button to compare the item code in the form with the item codes in the Items table, and then reduce the quantity in stock by the quantity sold in the form.

Any help given would be much appreciated.

thanks

View 1 Replies View Related

How To Use Expressions With Missing Values

Jun 11, 2007

I use a left join to join table A with table B. Table B has less matching records , so in the resulting table the field [RESULT] which comes from the table B has empty values.

If I use an expression for a new field PROCENT that involves [RESULT] I got #ERROR in all records with missing [RESULT] value. I tried various IF statements , but even IsError () function produces #ERROR.

It seems that ACCESS 2002 fails to detect missing values and gives an error for any operation. Is there any workaround for this problem?



Example
PROD_PERCENT: IIf([RESULT]>0,[RESULT]/[NETTO],0)

View 5 Replies View Related

Parameters/expressions In Queries

Mar 17, 2008

Hello again,

I have a query which has a parameter called [Enter Date as DD/MM/YYYY].
This filters out records from my table which match those entered by the user.
However, when the query is run, Access is asking the user to enter the criteria twice. The first time it is labelled Expr1, the second time it is Enter Date as DD/MM/YYYY. There is obviously something wrong in my query, can anybody point me in the right direction here?

Thanks in advance;)

swifty

View 4 Replies View Related

Hi! I Need Help With Summing Fields And Expressions

Dec 18, 2006

Hi! I'm trying to create a query using option groups, i have two options the first one is STOCK and the other is PD.
If i select stock the the query i want to create to introduce it on a form is:
I have 5 fields
-denominación
-reserva
-almacen
-cantidad
-stmax
If i select stock
then the query is
If (reserva +almacen)<cantidad then on a sub form have to appear the table with all the rows where (reserva +almacen)<cantidad.
but if i select PD only have to select on stmax the rows where value=PD
PLEASE i need help with this....

View 1 Replies View Related

Date And Time Expressions

Feb 27, 2005

hi all, Im working on a simple timesheet to keep track of time worked, but ran into two problems.

1.) The expression i'm using works fine for some times, and others not so good (get negative number).

2.) It only figures the hours but not the mins. (it rounds them up).

Does anyone one have a sampale DB or know a expression dealing with figuring out time differences.
If you need more explaintion on what i'm trying to do I've attached a sample DB.


Thanks,
Rich M.

View 1 Replies View Related







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