I've created several expressions in a query to test for values in a field (one column for each value for use in a report).
The expressions output the value 1 where the test is true. I planned to sum them to establish how many times the value is true but this isn't working. I am only offered a Count and this returns the number of records. Other fields (numeric) are offering me a sum at the total line and are working as expected.
I assume the expressions are outputting the value one as text rather than as a number but I can't work out how to change this.
I have a simple query to calcualte a profit margin on daily sales lines and I use a quick and dirty expression to calculate the margin in the query so I never need to drill it down further than that level (I don't want to go as far as putting the output into a report as it is only for use when double checking lines for errors which get fixed there and then in the database).
So far so good, however the margin output is a bit awkward to read as I can't seem to format it as a simple percentage. The field properties page doesn't like doing anything with the expression and even typing in a format manually has no effect, so I end up with figures like
Is there any way to format this output to show only 1-2 decimal places and be in a proper number format so I can sort them in ascending order properly?
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?
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.
I have a field in a query that shows amount of hours a site is covered. This field name is Coverage hours and it changes depending on the number of work days in a month. This part is working correctly but some times additional hours may be requested by the customer.
I want to add a another field to this query that will prompt me if their were any additional covered hours. Then I would have the ability to enter a value and the shown output for this field would be the value entered plus the standard coverage hours in the other field.
I am trying to modify the output to only show discontinued items. While the query is in datasheet view, the text is read as "YES" or "NO" under discounted items. So, I tried entering "YES" in criteria while in design view, but keep getting an error message stating, "Data type mismatch in criteria expression".
I am trying view the query output's in different text on the form and I do not want to see them again in query window.How do I hide this.. comments ???
I have a database that enables the banking details for a small club to be generated as an Excel spreadsheet that is then emailed as an attachment to the treasurer. The event procedure below works fine but I have been unable to incorporate the banking date from a control called BankDate to end up with the Excel file being in the form DGC Banking dd-mm-yy - is this possible?
i have got 2 tables i am trying query against. in table 1 i have a list of records which only appear once in the table under 1 field. in table 2 list of records which may have the same record appear under the same field numerous of times with different data in other fields on the table. i have created the reationship between both tables but i am getting multiple records appear in the output of the query where i only want the record to appear once in the output.
I'm building a query using wildcard "like". See the attached file. My question is that why doesn't the query "result" return "aaaaa" for "aaa" is contained within "aaaaa"?I'm basically limiting my records to those found in tbl_site based on a wildcard "like".
Is there a built in function which can be used to create line numbers in a query?
I've written a query to calculate year to date (YTD) points for yachts in a series of races and sorted it in descending order - so yacht 1 is coming first, yacht 2 is coming second etc. I'm looking for a way to add sequential numbering (starting at one and increasing by one for each line) into the query to represent their YTD places. Or this this something that should be left to the report which uses the query?
I am trying to get the output of several nested IIF expressions to show in only one filed in the table created. The output needs to be captured in a table. I am staring with create table to get it to work before I go ahead and use the sql to update the original table (the table the data is from)
I can get each to run fine independently and the whole thing runs too, the issue is it wants to put each expression in a new column, thus it is forcing me to label the expression.
Can I change this to only output in one column or add some more sql to do so?
Here is the SQL (table and filed names have been changed to protect the innocent)
BTW, I cannot change the Table the data comes from, I am stuck with the single table structure (BOO).
************************************************** ************* SELECT TABLE1.ID, IIf([TABLE1]![STAGE]="D", IIf([TABLE1]![PROJ_ACTUAL_D_DATE] Is Not Null,"Tier 1", IIf([TABLE1]![APPROVAL_D]="Y","Tier 2",
Whenever I export reports to PDF, the output appears zoomed and clipped. No extra pages are generated as they would be if I'm going over margins, it's just the report is clipped.
The report looks perfect in preview mode, and it looks perfect when going to an actual printer. However, when using OutputTo to save it as a PDF, this is when the report content is clipped.
I open the report in preview mode first so events are fired that show/hide various objects based on fields in the recordset.
I've tried reinstalling, and I've tried this on two different machines, one running Windows 7 and one running Windows Server 2008...both with the same results.
I'm writing a simple job scheduling database, so I've got resource availability (by week) and job list (by week). I've written a single-column query whose only output is a unique list of weeks with resources available, and another single-column query whose only output is a unique list of weeks with at least one job allocated.
How do I join these so that I can get a single-column list of weeks that have either (or both) of resources and jobs?
So Query1 returns: 25/08/2014 08/09/2014 15/09/2014
while Query2 returns: 01/09/2014 08/09/2014 15/09/2014 22/09/2014
I want query3 to return: 25/08/2014 01/09/2014 08/09/2014 15/09/2014 22/09/2014
Got another question for you all, since i'm on a mission of doing my annual upgrade of the DB.
My sales manager has asked me to implement a system where the database will flag up any customers who haven't used us for 6 months or longer.
I know i will hav to use a query and the Fields "Customer" & "Date", but cant think of the expression i would hav to use inorder for the database to do this operation.
I also want to add a check box so that, we can tick off customers who arent going to be usin us again, so that the query doesnt flag them up every month.
I'm a little new to Access but I'm trying to create an IIf Expression:
iif ( condition, value_if_true, value_if_false )
Mine is: Au_ppb: IIf([Element]="Au" And [Units]="ppb",[Result],Null)
I need to have three criteria's (each looking into a different column). I can get two criterias but not three:
[Element]="Au" And [Units]="ppb"
When I add in the third (And [Priority]="12") it doesn't work .
Also, I need to have about 6 columns with similar IIF statements in the one query in my query and if a row has Null for all then I don't want it visible however if I make the Criteria "Is not Null" on one column or on all it comes up with no results.
Users add transactions via a form after they put in the total of the transaction I want to run a query that gets a running balance of the client's account to make sure that paying this transaction will not over draw the clients account.What would be the best method of approach?
Ok my goal is to have a calculation in a query that includes a bunch of addition, division, and multiplication. (It uses 10 pieces of data) I tryed typing it in the expression builder but it doesn't work. I don't know if I am setting it up wrong or putting it in the wrong spot or what. I've tried some different things and I either get a blank box in that field or I get a syntax error. Also is there any way I can put the info received by the equation into a field in my table? Thanks
Hi, I need some help using functions in queries. I can calculate simple arithmetic in calculated fields, but fail when using functions. For example I have attached a doc file showing a query. One table displays record ids, the other four have data for consecutive dates for each record. I cannot make the query calculate the standard deviation of the data for each record. What expression would I use, I know it is stdevp but when i try and create the expression i get error messages galore.
I have a Table which has a column that contains a Data (last day of the month: 8/31/2013, 9/30/2013) and also contains a Balance field.
What I am trying to accomplish is a query that will provide me with the total for the last month (Sept), and then also provide me with a Percent break down from a whole balance.
I have the following SQL for it:
SELECT ATB.FC, Sum(ATB.Bal) AS SumOfBal, (select sum([Bal]) from ATB) AS Total, Sum([Bal]/[Total]) AS [Percent] FROM ATB GROUP BY ATB.FC, ATB.MMDDYY HAVING (((ATB.MMDDYY)=Date()-Day(Date())));
Right now, when I run the above, the Sum(ATB.Bal) pulls just the month of September, but the Percent is pulling a percentage of each Sept. Value against the whole Balance for the Year. So, when I sum the percent column to get a total of 100% for the month of September, I am ending up with a number less than 20%.
I have query that filters my list in Access. The list can be filtered in different ways(like using a combobox). This is the criteria of the query for the field Title of the table Book:
Code: Like IIf([Forms]![frmName]![cmbSearch].[OnClick];"*" & [Forms]![frmName]![txtSearchBar] & "*"; [Book]![Title])
What I want is when the cmbSearch is clicked, the list must show the Tiltes where the title of the book contains the words of the search bar. When its not clicked on that button, the list must show all the titles.
Here is the SQL of the query:
Code: SELECT Book.ID, Book.Title FROM Book WHERE (((Book.Title) Like IIf([Forms]![frmName]![cmbSearch].[OnClick],"*" & [Forms]![frmName]![txtSearchBar] & "*", [Book]![Title]))
The problem is the list filters whether I click on a different cmdButton or an optionButton. I dont know why [cmbSearch].[OnClick] doesnt work.
