Queries :: Expression To Output Value As A Number?
Apr 17, 2014
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.
View Replies
ADVERTISEMENT
Apr 26, 2013
I'm trying to sum up 2 expressions.
1st. UsedAF: nz(DSum("AF_lengte+AF_zaagbreedte";"VRMUTSTAF";"VR ID = " & [VR].[VRID]);0)
2nd. UsedRES: nz(DSum("RES_lengte+RES_zaagbreedte";"VRRESSTAF";" VRID = " & [VR].[VRID]);0)
Now with a third expression I like to sum those 2 up.
3rd. Used: [UsedRES]+[UsedAF]
The thing I run into is when 1st has a value of 15 and 2nd a value of 0 the outcome of 3rd is 150 while it should be 15 ofcourse.
When I change the first 2 expressions to Sum (at Totals) the 3rd works properly. Yet the other 2 comes with wrong values.
View 5 Replies
View Related
Mar 23, 2013
Correct syntax/expression for log of a number. The number i am getting in Access is twice as big as the correct number i get in excell
View 3 Replies
View Related
Feb 8, 2006
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
36.7768595041322
38.6666666666667
15.6448202959831
etc
the expression i use is:
Margin: IIf([dbo_tbl_sales_invoice_lines.price]=0,"",([dbo_tbl_sales_invoice_lines.price]-[net_cost])/[dbo_tbl_sales_invoice_lines.price]*100)
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?
View 7 Replies
View Related
Oct 26, 2007
along with data in each row, i want to include the record number in the ouput of a query. is it possible?
once i run the query and view the ouput, i can see the standard access database record counter at the bottom or if i select a row, i can see the row number at the bottom.
how can i include that counter in each row of output from the query?
View 1 Replies
View Related
Nov 12, 2014
I got a easy table with two columns. To make it easy say
1 20
2 40
3 30
4 78
5 60
6 85
7 20
I want to put the number 1 - 7 over a picture and get it to look up the value in the 2nd coloumn and replace the nr with the number in the 2nd column. Is this possible? I would have to manually put in where 1 - 7 is (this will be labeled ontop of a plot plan)...
View 4 Replies
View Related
Jan 16, 2006
Hi all
basically i have implemented something in form that says record 1 of 5 etc
the way this is implemented is done straight into the text box on teh form ...
="Application " & [CurrentRecord] & " of " & IIf([NewRecord],[CurrentRecord],Count(*))
The problem with above implementation is that when adding a new record sometimes i get say record 3 of 2 instead of 3 of 3
somettimes its 3 of 3 but when scrolling back i get 2 of 2
any ideas how i can implement this in a stable manner??
thanks
View 1 Replies
View Related
Feb 14, 2014
I've been working on creating a database for a Physical Therapist that will store patients information and information on past appointments (including the amount of money paid per appointment). I created a form that he can then easily select the patient and their D.O.B. and insurance carrier will show up.
But I need to create a way that when he then selects the treatment received for the appointment that the correct payment amount will show up and be recorded as well. The reason this is an issue is because for each insurance there is a different cost per treatment.
View 3 Replies
View Related
Dec 29, 2004
I have an Access database query with a month (with full names of months in them) column. I need to build an expression that will take
DECEMEBER and make it 12
November and make it 11
October and make it 10 and so on.
I tried this and it worked for 1 month at a time ****** Expr1: IIf([month]="January","1") ****** but how would I format it to make all the months corespond with the right number?
The table value is not a month field it is a text field. so I don't think any of the date variables will work. I hope this is simple one for you access guys out there. I really need some help on this one.
View 1 Replies
View Related
Aug 8, 2012
I am trying to pull a number from a text box. I listed the expression for the box below. [Current Record] is a number and Count(*) is a number. I couldn't figure out what field gave me the total records so I did a count instead. If I want to pull the value from just the count portion, what expression would do that?
Code:
=[CurrentRecord] & " of " & Count(*)
Note the spaces on either side of "of"
View 6 Replies
View Related
Feb 7, 2005
So I have a database of contacts, and it has their birthdays listed as well. What I need to do is calculate the number of Fridays from their birthday up until today's date. How do I do this? Thanks
View 2 Replies
View Related
Dec 12, 2013
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'm not sure how to set up the new field.
View 10 Replies
View Related
Jan 30, 2015
I have created a query and one of the columns of the output displays a check box because this is the data type of the underlying table.
I would like the query to output either Y or N rather than displaying the checkbox.
How would I do this?
View 3 Replies
View Related
Feb 13, 2014
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".
View 5 Replies
View Related
Jan 12, 2014
I have the following expression as part of the recordsource for a report:
Is it possible to put a mask on the output of [phone1]? Ideally something like: (716) 555-5555 x1234
Code : phone1: [ContactNo1] & " " & [ContactNo1_ext]
View 10 Replies
View Related
Dec 15, 2004
Hi,
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
???
View 2 Replies
View Related
Jun 11, 2013
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?
DoCmd.OutputTo acOutputQuery, "Export Banking", "Excel97-Excel2003Workbook(*.xls)", "DGC Banking.xls", True, "", 0, acExportQualityPrint
View 5 Replies
View Related
Mar 4, 2015
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.
View 2 Replies
View Related
Jul 1, 2015
How could i get output of a Query in a VBA variable ?? I'm trying as below butIi'm getting error:
Dim output As Integer
vari = DoCmd.OpenQuery("qryCompare", acViewNormal, acReadOnly)
Output of a query is a single MAX value.
View 4 Replies
View Related
Jul 8, 2014
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".
View 12 Replies
View Related
Apr 13, 2015
I have the following code with dcount
If DCount("Username", "[tbl_userinformation]", "[Username] = " & Me![Text146] & " AND [actualdate]=" & Me![Text148] & " ") > 0 Then
but i have the following error ...
runtime erro 3075: syntax error in number in query expression '[username]=f15691b and [actualdate]=13.04.2015'
f15691b and 13.04.2015 are the values those i entered.
View 13 Replies
View Related
Jul 9, 2012
How do I write a Access 2010 Web database expression to give me the number of days between a particular field eg [sold date] and todays date?
Being a Web database I know I am restricted to a smaller list of available expressions - normally part of my expression would include eg date().
View 3 Replies
View Related
Apr 18, 2014
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?
View 1 Replies
View Related
Mar 25, 2013
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",
[Code] .....
View 1 Replies
View Related
Oct 29, 2013
I have two tables in a query joined by a ID field. The problem is in the one table the ID fiels is a text format and in the other table its a number format so the query builder doesnt like that and gives me the Type mismatch in expression error.
There's no way around it though; i need it to be those formats in its respective tables for reasons i wont go into here for simplicity.
View 2 Replies
View Related
Aug 20, 2013
I'm using Access 2007 SP3.
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.
Here is the code I'm using:
DoCmd.OpenReport MyReport, acViewPreview
DoCmd.OutputTo acOutputReport, "", acFormatPDF, "MyReport.pdf"
DoCmd.Close acReport, MyReport
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.
View 5 Replies
View Related
