IIF Result Parameter Problem

May 15, 2007

I have a query which uses several IIF statements in different fields the end field being a simple compare, if one value is greater than another+5% then no="OK" yes="Check"
This is the final IIF

Diff: IIf([InvVal]>[EstPrice]+[EstPrice]*0.05,"Check","OK")

I only want to show those records requiring the "Check" but if I use "Check" in my criteria I am asked for "EstPrice" or "BaseRate" as these are based on other IIf statements. I have tried using a form to feed the parameter but still the same result. I think I should be joining together the different IIFs but am finding this a difficult option so ...

Any sugestions please

Steve

View Replies


ADVERTISEMENT

Parameter Crosstab Query - No Result

Apr 19, 2006

Hi All,

Im trying to create a crosstab query with parameter entry from a form.

I wish to be able to specify a date (using Microsoft DTPicker control) and a shift (using a combo box to the relevant table) to make the selections.

These two selections on the form are the criteria for the cross tab query which returns the downtime totals for each category for each line.

I have defined the parameters for the query by putting which seems to work for most people from what I've read, and has worked for me on previous forms in the same database (although not in parameter crosstabs....this is my 1st parameter crosstab)

However, I still get no result from the query. Without the parameters, the crosstab query works fine.

Help please...I'm really stuck. Here is the sql for the query, can anyone see anything that is obviously wrong with it?

Much Appreciated and thanks in advance to all!

regards

keji

View 1 Replies View Related

Queries :: Conditional Query To Post Result In Field And Filter Result Records?

Mar 5, 2014

I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg

In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.

View 1 Replies View Related

General :: Adding Count To Result Of Query Depending On Month And Result

Aug 18, 2013

I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?

I'm using access 2003.

View 4 Replies View Related

Queries :: If There Is No Result In Query Need To Have Default Result Zero

Oct 12, 2013

I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.

View 5 Replies View Related

If The Result Of A - B < 30 Than This Result Will Flash

May 29, 2007

All:
I'd like to make the result of my text box flash (in my form) if the its value is less than 30..For example. If A - B < 30, then the result whatever it is as long as it is less than 30..Is this possible in Access? thanks in advance.. :

View 1 Replies View Related

Capturing Parameter From Parameter Query

Jul 12, 2005

I have a form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.

View 2 Replies View Related

Parameter Query Asking For Parameter More Than Once!

Nov 9, 2006

Hi,

I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!

Here is my code (abbreviated slightly):

SELECT DISTINCTROW C1.*, C2.*
FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct]
WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);

I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.

So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!

Help! :confused:

P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.

View 2 Replies View Related

How To Get Result

Sep 29, 2005

Hello guys,
I Have a table call Hq_Cases in oracle database,I created same table in access 2000,and I created a SELECT QUERy in access ,now when I run this query it shows me empty table,so I want to know how I can bring values from oracle into my access database.
Any suggestion is appreciable.

Thanks
vsap

View 3 Replies View Related

Problem Result

Apr 26, 2006

Hi,
Im getting an odd result returned & I don't know why.

expression:=[lookup]*[yards]+[drop]:=0.296*30+-11.838
Result:=1.20E-27

If I replace value [drop] with -11.838 then the result is correct (-2.958)
ie:lookup]*[yards]+-11.838

The porblem seem to be when [drop] is neg.

View 1 Replies View Related

Result In To Variable

Sep 20, 2005

how can i get the result from a query in to a variable, i want to use this result in a new query?

View 3 Replies View Related

To Put SQL Result Into A TextBox

Dec 25, 2005

I have a SQL query in a string of a VBA procedure
(e.g. MyQuery = "select sum(Amount) from Investment")

and I want the result to be shown in a TextBox of my form Me.

I know several ways to run the query and to send the result into the TextBox (e.g. with QueryDef and RecordSet). However I guess Access should offer a more direct way to do such a simple operation.

Does anyone has a solution to do it the simplest way ?

Thanks.

View 7 Replies View Related

I Want The Name To Appear Only Once In Query Result

Mar 1, 2006

Hi,

I'm designing this system in which each employee has different area of strength (i.e. Math, Languages,..)

if an employee has 2 or 3 area of strength his name appears in the query more than once. I want his name to appear once.

I tried "group by" but it gave me an error. I think I'm doing it wrong.

Please Help!

CS.

View 7 Replies View Related

Strange Result

Apr 30, 2006

I have a select querey

which contains the expresion
forms!frm_itemslct!text8
It should just pick up the value contained within [Text8] which is a number, and it was doing that earlier this evening. I've since changed something and instead of a number it is now showing a small square :confused: For the life of me I can't work out what it is that is causing this.

View 3 Replies View Related

How To Get Result Of Last 5 Days

Sep 7, 2006

Good day :)

I have table with filed "entrydate" which has records of dates from 1 aug 06 to 10 aug 06. How to see the last 5 days result. like form 6 aug 06 to 10 aug 06

Note: I dont want the date to compare with current date date() because this is history data. also I hope to see the result with auto creteria I dont want to add the date in creteria filed manullly.

any assistant is appreciated.

thank you

rosy

View 7 Replies View Related

Reference Last Result

Dec 4, 2006

I am writing a query to calculate a running value, looking at the result of the line above and adjusting the running value in relation to new criteria. Make sense ?

For example

I have a number of dates which each have a value (a) against them dependant on the value in (a) I want to reward it in different ways and store the result in (b), I then want to look at (b) in the result of the above line in acessing how I will reward the next record.

Basically I want to read the value of the result of the line above when calculating the next line ? So it is basically cumulative through the query.

ps just found this forum so sorry for postiong a question first but any guidance appreciated.

View 2 Replies View Related

[Ask] SQL To Produce This Result

Jul 4, 2007

Hi everyone,

I really need help with transforming this table/data:

Project Date(dd/mm/yy) Value
----------------------------------
A...........1/1/2007...............100
B...........2/2/2007...............200
C...........3/2/2007...............300
D...........4/5/2007...............400
E...........5/5/2007...............500
F...........6/5/2007...............600
G...........7/6/2007...............700

into the following data format (13 columns in total, with 7 rows):

Project Jan Feb Mar Apr May Jun Jul ... Dec
-------------------------------------------------------------------
A............100.................................. ..........................................
B.....................200......................... ..........................................
C.....................300......................... ...........................................
D...............................................40 0.........................................
E...............................................50 0.........................................
F...............................................60 0.........................................
G................................................. .........700...............................

Assuming today is January 1st 2007, the 2nd column must starts with current month.

If someone can post me the SQL, or give me some tips or direct me to an article on how to achieve the results above..it'll be most appreciated.

Thanks in advance..

View 1 Replies View Related

Use Search Qry Result For Something Else

Jul 27, 2007

hi :D
here is my question:

i have a table (tblmembers) with member data (name, last name) etc.
i found how to run a search qry with parameters to ask for name, last name.

i have another table (tblrelationships) with 3 fields
2 for member id's and 1 for the type of relationship they have

i need to run the search query for the member by name (from tblmembers) and use his member id (from the member table) to add it to a new record in the relationship table (tblrelationships).

when i run the search qry, i only get as the result the fields with the member information..
thanks

View 7 Replies View Related

HELP!!! Result Too Long

Oct 22, 2004

Consider the following...

Select id, A & “ “ & B as Res
From tblMain


A is type text
B is type memo

Result cuts cut off at 255 Characters….

How can I get the entire result – More than 255 characters??

Thanks URL

View 1 Replies View Related

Sorting The Result

Apr 17, 2007

Can anyone tell me how to sort the result in this query
Code:SQL="Select Tableno From Refrence where Refrence='" & refer & "'"
Ok i want to sort tableno in such a way that it should contain the bigger first
like if there is 34,1 in the result so 34 should be first ad 1 should be last.

View 5 Replies View Related

ADO Query Result Set

Sep 5, 2011

I am trying to create a VB script to automate a mailing based on several query result sets from access. I have gotten to the stage that the output is correct but have a problem with the 5th and 6th record set query as they only return one record (When in fact there should be at least two for each).

I don't really understand why this is happeneing as the SQL is exactly the same as in the 2nd record set - which works perfectly. Also I've tested the SQL directly in an access query & there are no errors in the formatting that I can see... correct number of records returned.

Code:
Public emailaddress, ccaddress, Subject, body1 As String
Public baserow, toprow, countnumberofrows, emails As Integer
Public tempdir, projectlistdir, WBPATH As String
Option Compare Database
Option Explicit

[code]....

View 5 Replies View Related

How To Rank Data Result

Jul 7, 2005

I have a table which has got the following fields:
Name and
Points

I want to create a third fieldw with the name of rank which should give automatic results of rank that is the one with the maximum points should have '1' written in his rank column. Similarly, all rank fields should be filled accordingly.
I used the help, it said i should apply the syntax.
I do not know how and where to apply that syntax
Your help is appreciated
[HassaaN]

View 1 Replies View Related

Odd Averages Result From Query

Jun 15, 2007

I am trying to calculate the average patients age from 2671 records using this SQL:

SELECT tbl_Customer_Details.DOB, CalcAge([DOB]) AS Age, DAvg("[Age]","qryAvgAge") AS Average
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.DOB, CalcAge([DOB]);

why am I getting the result:

68.1131066106

I would have thought that it would have been 68 a whole number, has anyone got any suggestions why this should be.

thanks

View 3 Replies View Related

Keep Result Of Calculation In Table

Oct 18, 2004

Hi all.
I have a TableA with 3 fields: FieldA, FieldB, Result. A Form1 based on these fields. User will put data in FieldA, FieldB and calculate in Result. Problem is how to keep result of calculation in TableA?
Thanks

View 4 Replies View Related

New Field Result From Concatination

May 30, 2007

:confused: This seems like a very basic task, but I am confounded. I am trying to display results in a separate field from the concatination of 15 text fields in the same table. This result will eventually display as a string on a web (intranet) page.

I got the concat to work in a query, but getting this same result in the table is eluding me. I am working in Access 2003.

Thanks in advance for any advice anyone can give me.

View 2 Replies View Related

Copy Result Into The Table

Sep 15, 2007

Hi all, how can I have the result which is shown as "DateEnd" on the form available on the table. right now if I open the table "tblShift" the fields are blank!
Thanks

View 3 Replies View Related







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