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?
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.
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.
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?
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.
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 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 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!
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?
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..
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
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.
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),"")
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
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?
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!!!
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.
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 ?
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
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:
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
