IIF With 2 Criteria
Nov 25, 2005
Hi,
I need to have Sum of amount in a text box temporarily placed on the form using IIF statement. The Sum would be calculated if 2 of the columns in table satisfy the condition.
The column in table on which I need to put condition has following data type field.
1. SchemeNo= Numeric
2. SchemeEnd = Yes/No
How can I put 2 conditions with IIF statement?
I tried following way, but it produces an Error. Even I tried to put SchemeEnd = False in the following statement. But didn’t work.
=IIF([TxtDisplaySchemeNo]>0,[TxtSchemeOffer]-Nz(DSum("InvAmt","T_AdvPaySalesInvFooter","SchemeNo= TxtDisplaySchemeNo And SchemeEnd = 0"),0),"")
Anyone have an idea about this?
With kind regards,
Ashfaque
View Replies
ADVERTISEMENT
Oct 21, 2006
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
Any help would gratefully be appreciated. Thanks
View 5 Replies
View Related
Sep 15, 2005
I created a form that allows the user to choose the criteria that they want to see on a report using =Forms!formname!controlname in my query. It works great but I want to also allow the user to choose nothing and return all records instead of limiting them to choose just one type of record. Is this possible? Before I created the form my query had the [Enter parameter] on one criteria line and [Enter parameter] Is Null on the next criteria line and that was working great for my use but I need to create a simple form for other users.
View 1 Replies
View Related
Aug 20, 2007
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
StoreSubjectSalesSales %
1516Fiction56431.5-24.15%
1516Audio Unabridged1650.8-231.04%
1516History / Military History10081.1-29.99%
1516Role Playing / Graphic Novels14773.9-20.27%
1516Mystery13152.6-19.84%
1516Audio Abridged1785.9-141.84%
1516SciFi / Fantasy27535.3-7.93%
1516Juv Audio/Video1580.6-100.13%
1516Biography8103.6-15.89%
1516Sports7910.8-15.64%
1516Current Affairs / Law8141.9-14.34%
1516Reference7183-16.22%
1516Juv Non-Bk4585.9-25.02%
1516Science / Tech2961.4-33.98%
1516Movies / TV / Music / Dance3395.3-29.46%
1872Fiction307344.3-7.49%
1872Business134307.5-13.48%
1872Psych / Self Improvement100650.4-10.05%
1872Audio Unabridged29165.9-27.32%
1872Cookbooks57463.3-13.56%
1872Computers59235.7-12.37%
1872Regional59883.4-11.22%
1872Health & Fitness64713.8-10.29%
1872Maps19358.4-27.66%
1872Current Affairs / Law47927.1-11.08%
1872Travel Foreign42583.7-12.27%
1872Religion / Bibles80255.6-6.07%
1872SciFi / Fantasy67641.4-6.49%
1872Study Aids / Notes38299-11.24%
1872Games41745.1-9.79%
View 2 Replies
View Related
Sep 2, 2005
Hi all,
I was wondering if someone would be able to point me in the right direction for my problem. Basically i have a table which shows all of the products sales within a given period, my user currently has to pick out the sales manually of those sales which have been sold out.
His criteria is those products which have been sold out more than one time, been sold out for more than six hours and those sold out for more than 24 hours. Im struggling how to show the more that six hours and more than 24 hours in my criteria.
can anyone help? thanks in advance
View 1 Replies
View Related
Jul 5, 2005
I am trying to setup a query that will generate a report based on options selected on a form. The fields I am using in the table do not have data for every record. If the criteria in the query is left blank, I would like all records listed regardless of whether there is a value in that field. If a value is selected for the criteria, of course I would like only those records shown. I am testing this only on one field at present. Here is my criteria in the query:
Like "*" & [forms]![frmDischargeRptSelector]![cmbResType] & "*"
With the above criteria, if I do not select a value for the parameter, I will get all fields that have a value in them, but not the blank(null) fields. If I leave the Like "*" out of the criteria, then I get no records if I do not select anything for a parameter. If I add an or Null to this statement, I get all null records even if I select a value for the parameter.
I hope this makes sense.
View 12 Replies
View Related
Nov 14, 2005
Hello All,
I have already read some of the IFF threads on this forum and tried multiple solutions that have been provided on this site, but somehow Access keeps coming up with the ''#error''.
The following syntax was plugged into SQL view
SELECT IIf([Group by Fund + Date].[Date]='31/12/1994','1','0') AS D
FROM [Group by Fund + Date]
I have no clue why it doesnt work.
I appreciate any help!
Thanks
Luuk
View 3 Replies
View Related
Mar 14, 2006
i was just seeing if anyone could help me with a query where i need the state for employees living in delaware or who were hired after 12/31/92..thanks!
View 1 Replies
View Related
Jun 20, 2006
I am having a problem doing the IIF statment. Is there anyway that I can avoid the else part of the IIF statment from being executed.
I am genterating a report based on what a user selects on a combo box from a form. There are about five options to pick from so this is what I want
IIf([Forms]![ReportForm]![TypeofConcern] = "option1", complaints.option1 = true, -1)
Else IFF IIf([Forms]![ReportForm]![TypeofConcern] = "option2", complaints.option2 = true, -1)
Else IIf([Forms]![ReportForm]![TypeofConcern] = "option3", complaints.option3 = true, -1) ................and so on.
I know this isnt valid code, but anyone have an idea how i can do this.
Thanks
View 1 Replies
View Related
Sep 6, 2006
I used a Form to allow user select a group, then a report will be created for this group. I set up [Forms]![frmReportSelector]![cmb_ReportType] in report query. There are other variables such as project status(Not Started, Completed, etc) and completionDate.
Now I need to do one more thing, if "In Progress" group is selected, for the completed projects, I only want to show those finished within past two weeks. I have calcuated the DateDiff. But where and how to write this condition in query?
Thank you for your help.
View 3 Replies
View Related
Jan 19, 2007
Hello, I understand that when using the "Like" command in a query - you should make use of wildcards to obtain the required data, but what happens if the required data is user inputted?
ie I currently use in the criteria box....
forms!frmselectclient!txtsearch.text
but I effectively want...
Like forms!fromselectclient!txtsearch.text, however I can't get this to work.
I'm guessing that this is something simple. Any ideas?
View 6 Replies
View Related
Jan 31, 2007
Hi all..
i have made a query that works the way i want if i put my criteria in like e.g. "101". In that case i will get everything i need for worker 101.
The probelm is, if im gona do this for all the workers(200 of them) i have to make 200 queries.. and i'm pretty sure i can make a function some how for this.
can i link the criteria to a field for instance? So i can put a worker in the field, and then click a button to run the reoprt which will get its values from the query. I'v tried this but get all sorts of errors up..
Would appreciate some help or ideas on this!
Cheers,
Takstein
View 2 Replies
View Related
Apr 21, 2008
hi
i have created a database for attendance register and need to create report for it
before produce report it should ask you the date like say from when to when
example of it you would put from 10/10/2008 till 20/10/2008
my problem is in query ,in criteria i can put "[enter date]"but iw would only ask for date once.
how would i define that ask me a starting and ending date?
thanks
View 5 Replies
View Related
Mar 2, 2005
Hope someone can help.
I have a form feed by a query with 100+ records. The query has two field criterias which is requeried from two combo boxes on the form. I would like by default for the form to show all records and only filter the records after a selction in the combo boxes is made.
EG. If the combo box values (both boxes) are NULL, then all records will be displayed.
Any thoughts appreciated. Thanks in advance.
Regards
W
View 4 Replies
View Related
Mar 8, 2005
Is it possible to enter criteria in a LIKE function?
Something like:
Where customer LIKE "*[enter criteria]*"
I know this doesn't work...
Any way to get this working?
Thx in advance.
View 3 Replies
View Related
Jun 27, 2006
Hello,
I created a qeury to display people in my databse with certain data.
I am getting lots of duplicate names, is there some type of criteria i can enter to delete duplicate names (Or sometimes more then just a duplicate)?
Thanks
View 2 Replies
View Related
May 16, 2005
i have a six digit roling number, (100565)
i have a totals query trying to add the amount of entries are in this query, however i need a criteria for it to look for a six digit number
unless i have this criteria it returns 1 for 100565, and 1 for 100566 and so on
any ideas searching db now
View 1 Replies
View Related
Aug 10, 2005
HI all,
i have a really annoying proble where the solution is probably staring me straight in the face. Basically i want to produce a report where the user is prompted to enter months to be looked at within a certain period and also the years, in my query i am using:
Between [First month] And [Last month]
and
Between [First year] And [to end year]
when i do this the query just asks for the months and doesnt go on to ask for the years...help?! :D
View 1 Replies
View Related
Sep 6, 2005
HI i was wondering if anyone could help. I am doing a database for a fuels company. They want to know when fuel has been dry for over 24 hours. There is a 'dry time' field which is actually calculated off the runout date and the delivery date. I thought i would simply have to put >24:00 in the dry time field, but this does not work. The field type has been set to text, and so im assuming this is why this does not work. There are over 8000 records so i can't obviously change these manually. I am wondering if there is anyway around this?
View 14 Replies
View Related
Sep 27, 2005
Hey Guys,
What i want to be able to do, is that when a user clicks on a report, they are asked to enter a month and only the records with that month will be displayed!! What expression can i enter in the citeria of the query, i did try =like"month" but that means creating a query for every month of the year!!!
Thanks in advance guys!!
View 3 Replies
View Related
Feb 8, 2006
Hi all,
This is just a quick question, for one of my fields the users enter a number, this could me a minus or positive number. I have been asked to produce a report which shows the total of the minus number and the total of the positives. I have set up a query for the minus number and have looked at various ways to distinguish the negative number from the positive. I tried <0 but never worked. Microsoft help gave an example of a name column and sed to use Like "*w*", to show all the names that begin with w. I tried to adapt it for my numbers ie Like "*-*", but it doesnt work, without this separation i can't total up the negative and positive number seperately.
Thanks for in advance for any replys.
View 3 Replies
View Related
Nov 21, 2006
Is there a way to have a Criteria set to promt a user to select a item from a dropdown list.
So when the prompt is made to ask for the Item Name it will have a dropdown list that the user can choose from
View 4 Replies
View Related
Apr 26, 2007
I require to select records to print based on a date range selection
When I use BETWEEN I dont get the start date or end date records
I only displays the records in between which I suppose sounds logical.
How can I get my report to include all the records I select.
Could this be anything to do with the format of the dates being used ?
Thanks
:confused:
View 1 Replies
View Related
May 10, 2007
Hi,
I hope someone will be able to help me with this one, I have a simple access query set-up which references to a table containing information about processed orders (From a ficticious company). I am trying to set up a query that will display all order placed within the last week. I have edited the criteria on the date filed to
Between Now() And Now()-"7"
Now this sort of works in that it filters the data, but it doesn't filter it by the day but by the year. i.e. Any order played within the last 7 years rather than the last 7 days. Any help much appreciated.
Sorry for being such a n00b,
Thanks for looking!
cheers
xRes
View 2 Replies
View Related
Jun 1, 2005
Hi there
I have a table of which I need to count the instances of one of the fields.
The trouble is that I need to return two different counts based on two different criteria.
I don't really want to duplicate the table just to be able to achieve this, and I wanted to be able to construct it in a way that I can use in my ASP page which interrogates the database.
Of course everything I've tried applies the two criterias at once to the results. Stuck on this one, any help much appreciated. :confused:
View 13 Replies
View Related
Jun 7, 2005
I have a report that is driven by comboboxes, when one selects what they want to see. The report is driven by :
Dim csra As String
Dim acsr As String
csra = cmbacust
acsr = "[CSR] = " & Chr(34) & csra & Chr(34)
DoCmd.OpenReport "rptIBOR", acViewPreview, "qrycsr", acsr
The problem is the where condition, you see, this query is drawn off of 2 joined tables, if I leave off the condition, it will report all of the records.
here is the stored query:
SELECT tblCustCodes.CSR, InternalBORIssues.Date, InternalBORIssues.CustomerCode, InternalBORIssues.WorkOrderNum, InternalBORIssues.Title, InternalBORIssues.ErrorOrigination, InternalBORIssues.Issue, InternalBORIssues.Catagory, InternalBORIssues.Initials
FROM tblCustCodes INNER JOIN InternalBORIssues ON tblCustCodes.Cust = InternalBORIssues.CustomerCode;
So the trained eye can see that the feild that I need is in the tblCustCodes table. When I run the above report with this condition, it is asking me for the parameter for "CSR", which should have been stored as a varible "acsr". To the best of my logic, I figure I need to tell the condition which table to pull from, but cannot figure out how.
BTW, if I go into the stored query and manualy enter a name in the criteria box, it works as expected!
Any help or suggestions appreciated, thanks in advance.
Jeff
View 2 Replies
View Related
