Queries :: Calculated Field - Return Zero If Negative Result (Access 2007)
Oct 10, 2014
I have a query that returns several calculated fields. One of them is simply derived by simple summation of the others. If this calculated field returns a negative number, I need it to show as a zero.
The only way I know how to do this is by an IIF statement :
Code:
SELECT [fld1], [fld2], [fl3], .....
IIF(([fld1]-[fld2]-fld[3])<0,0,([fld1]-[fld2]-fld[3])) AS fld4
FROM...
(The above doesn't suggest that [fld1], [fld2] etc are calculated fields - I just wrote it like that for succintness - they calculate fine, there's no issue with them...)
Is there a more efficient way of doing this? I find IIF's a bit tardy, possibly because they evaluate for both True & False eventualities, regardless of the condition, and this query is going to run against a fairly large dataset so any performance lag is going to be exacerbated.
View Replies
ADVERTISEMENT
Aug 6, 2005
G'Day all, I am new to the forum family so please excuse any ignorance I may have in asking questions the right way.
The following at times depending on certain values creates a negative value.
WDCCFrm: [qry Areas]![Formation Value]-([qry Areas]![Formation Value]*((100/[qry Areas]![Formation_DesignLife])/100))*(DateDiff("yyyy",[BLOCK_DATA]![YEAR],Date()))
Due to the data requirements a negative value is not acceptable, it needs to be zero. The above is part of an Asset Managemnt Program and if the WDCC (Written Down Current Cost) is 0 then the item is written off. How do I change the negative value to a zero.
Any comments or suggestions would be appreciated.
Regards
Alrae1
View 3 Replies
View Related
Nov 11, 2012
I am completing a report. I've inserted a calculated field in the report. I'm using Access 2010. Here is an example:
=([AccessTotalsOpen]-[AccessTotalsClose])/[AccessTotalsOpen]
I get the correct answer but cannot find a way to Display/FORMAT a negative number with any of the options that I have available to me?.
Any method to format a calculated field in a report?.
I tried this example but could not get it to work. =Format( FIELD ), " 0; (0)";
View 3 Replies
View Related
Aug 29, 2013
Why this SQL will return only one query maximum?
DateLimiter: (SELECT Date_Retro_Fees_Rate FROM tblRetroFees AS Alias WHERE Date_Retro_Fees_Rate = (SELECT FIRST(Date_Retro_Fees_Rate) FROM tblRetroFees AS Alias2 WHERE Alias2.Date_Retro_Fees_Rate > tblRetroFees.Date_Retro_Fees_Rate AND Alias2.Id_Product = tblRetroFees.Id_Product) AND Alias.Id_Product = tblRetroFees.Id_Product)
A picture of the table is included.
View 1 Replies
View Related
Jul 10, 2015
Basically in the query I need a Collumn to only output the greatest value that is given within the sub query. If that makes sense?
Eg. Tenancy start date must be the highest date against the date the property was added back to the system - for post development properties.
At present the it's returning previous tenancies from pre development.
View 3 Replies
View Related
May 2, 2013
Any way to build something into a sub-query that says 'if no records are found, return '0' or some other string'?
Otherwise is there a way to make a master query ignore sub-queries if they return no records?
Allow me to explain in more detail: I have a series of sub-queries, most of these take the sum of several fields from a number of different tables, and I have a main query which combines all of these, to be used as the basis of a summary report.
These queries aren't a problem, but I have a few other essential queries which take the modal (most common....) entry for fields which aren't numerical. So I can't use the sum function.
Now, if all the sub-queries are working then so does the main query, however if one of them fails to find a result, then none of them show up in the main query. I don't know why.
The issue is that depending on the date range selected, some of the tables targeted by the sub-queries don't have any records at all, so when they are run they return nothing. The sum queries can handle this since they just return 0, but those searching for modal records just find nothing (not 0's and not null fields, just blank across all rows).
Here's an example of my sql statement for the modal queries.
Code:
SELECT TOP 1 Trends.Trend AS ModeTboxTalk, "1" AS [Key]
FROM Trends INNER JOIN [Toolbox Talks] ON Trends.TrendID = [Toolbox Talks].TrendID
GROUP BY Trends.Trend, [Toolbox Talks].TrendID, [Toolbox Talks].TalkDate
HAVING ((([Toolbox Talks].TalkDate)<=[Forms]![WeeklyReportSelect]![WeekBox] And ([Toolbox Talks].TalkDate)>[Forms]![WeeklyReportSelect]![WeekBox]-7) AND (([Toolbox Talks].SiteID)=[Forms]![WeeklyReportSelect]![SiteBox]))
ORDER BY Count([Toolbox Talks].TrendID) DESC;
- FYI the weekly select form is where users select the week and site they want to report against. So it would be really peachy if I could tell the above to say something like 'no trend this week' if indeed there were no records.
View 6 Replies
View Related
Jun 15, 2015
I cannot get my query to return result when I enter the month of "June" as search criteria. June is listed in the table and query, every other month is returned except June.
I have attached two example for your reference.
View 9 Replies
View Related
Sep 11, 2013
I have several queries that perform an audit on data. I want to create a form that has two buttons, multiple labels and textboxes. When the first button is pressed, it runs all queries and returns a count of the records within each query into the corresponding textbox. Then I want a a button that will clear all textboxes. I don't want to see the queries, I just want them to run in the background and the count results to be populated in the textboxes.
So for example, it would return:
Check1: 3
Check2: 6
Check3: 2
Check4: 0
View 3 Replies
View Related
Mar 19, 2014
I am trying to update a field with the result of what is in the field plus the contents of a unbound form field.
So if crm_order_det.Qty contained the number 5 and the form field (labelled qty) contained 7 I would like the field crm_order_det.Qty to end up with 12.
The code I am using
sql = " Update crm_order_det SET [Qty] = (" & _
frm!Qty + crm_order_det.Qty & ") where crm_order_det.part = '" & frm![SearchResults] & _
"' and order_id = " & frm![order_id] & ";"
DoCmd.RunSQL sql
However I get the following error
Run-time error '424': Object required.
View 3 Replies
View Related
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
Apr 28, 2013
I'm stuck in Access 2003 - the group I am working with is unwilling to upgrade due to costs, and I've inherited this database (or I should say, a glorified spreadsheet), so unfortunately I am stuck with the tools that I have to use.
So I have Table A:MemberID (autonumber, Primary Key)
NameLast (Text)
NameFirst (Text)
TLBeginner (Yes/No) - Training level
TLIntermediate (Yes/No) - Training level
TLContinuing (Yes/No) - Training level
TLAdvanced (Yes/No) - Training level
I have created Query1 with the above table and added in a calculated field which essentially takes the training levels and converts it to a decimal number (basing it off a binary number of the 4-bits/Yes-no fields) with the calculated field below:
TrLevelTxtID: IIf([TLFundamental]=True,1,0)*1 + IIf([TLIntermediate]=True,1,0)*2 + IIf([TLContinuing]=True,1,0)*4 + IIf([TLAdvanced]=True,1,0)*8
So the calculated field works great - Depending what training levels are selected or not selected, I get a range between 0 through 15.
Table B: I want a list of "friendly name" for the different training levels.
TLID (Number, No duplicates)
TLFriendlyText (Text)
So what I want to be able to do, is to have Query1, that will pull the training level text (TLFriendlyText) within the query based on the calculated field (TrLevelTxtID). The catch is, I can't seem to create a join between the two tables with the calculated field.
Ideally, I want the final query to pull, [NameFirst], [NameLast] from TableA and [TrLevelTxtID] (from TableB) based on the queries calculated field [TrLevelTxtID].
How best to achieve this with the Access 2003 limitations. Also, this query will only be used for reporting/mail merging so there are no update requirements or concerns.
View 4 Replies
View Related
Aug 25, 2006
Please help.
How do i convert a negative value to zero in a Form?
:confused:
View 5 Replies
View Related
Jun 18, 2015
I am trying to get sum "+" and "-" Values in same Field. I created new Expression Builder in Query and i wrote there :
A: Sum([tablename]<0)
of course it did not work.
How shall i update my expression to work this thing?
View 8 Replies
View Related
May 27, 2014
Is it possible to run a SQL command to update a field within a table with random numbers?
More specifically - random long integers linking back to an ID (autonumber) field in another table?
Background to this is, I have multiple static data tables related to each other by long integer identifiers (autonumbers)
The structure is fine but I haven't been provided with the actual data yet - but for development purposes, I need to work on other functionality which requires that this data be present.
So I want to fill my table with dummy data such that I can go off and work on the remaining functionality, but then just go back and clear it all out once I get the actual data.
I have one 'main' static table, which links back to other tables, which I have already populated with dummy static (i.e. company names, locations etc) Now I want to go into my main table and populate those fields in each record with a random ID. I don't mind doing this field-by-field (there's only a handful) but I've a lot of records in there (~1000) so I'd rather not do this record-by-record.
View 6 Replies
View Related
Jul 10, 2013
The time difference in access query works very fine in 18:00 (Short Time) format , but when i export to excel it came out with "########" and the value is -0.14679132479
How to solve this problem , i keep trying but it doesn't work ....
View 14 Replies
View Related
May 8, 2015
I have two functions both should have the same results but they do not.
Public Function DefaultGreeting() As String
On Error Resume Next
DefaultGreeting = "Dear " & [Forms]![frm_contacts]![Dear] & ":"
End Function
Public Function DefaultBodyText() As String
On Error Resume Next
DefaultBodyText = [Forms]![frm_e_mailing]![mess_text] / this equals this just pulled from a table "Dear " & [Forms]![frm_contacts]![Dear] & ":"
End Function
View 5 Replies
View Related
Jun 10, 2013
Trying to run a query using criteria to populate the query by looking at information from a field on a form, if from is closed I need that criteria to look at the table and return all date in table.
View 14 Replies
View Related
Aug 8, 2012
I have a table that has 3 columns: Unique number, Date, and Results
I want the user to enter the unique number and date into the form. Then the "results" column/field will autopopulate a 0 or 1. I want it to populate a 0 85% of the time and a 1 15% of the time. This should be cumulative (meaning not every entry has a 15% chance of being 1). Is this possible?
View 1 Replies
View Related
May 3, 2013
How can list the contents of one field columns in one row in a report.
Example:
ID: 1
A1
A2
A3
A4
Should be: ID1 - A1, A2, A2, A3, A4
View 3 Replies
View Related
Jan 29, 2008
Hi there !
I display some buttons on my Form. I should do an SQL request to know how many and what the button should display. It works fine with a RecordSet.
Now I want to move the code that do the stuff to a function. I read that I can't return a pointer to a RecordSet and I should used a variant with a getrows.
So I try this :
*here is my function
Function get_nom_operation(ByVal cnn As ADODB.connection) As Variant
Dim requetteSQL As String
Dim rst As New ADODB.Recordset
requetteSQL = "SELECT libelle " _
& "FROM operation;" _
rst.Open requetteSQL, cnn
get_nom_operation = rst.GetRows
End Function
* and here is the code that call the function
Dim res As Variant
Set res = get_nom_operation(conn)
Dim i As Integer
'i = 1
For i = LBound(res) To UBound(res)
Set Obj = Me.Controls.Add("forms.CommandButton.1")
With Obj
.Name = "monButton" & i
.Object.Caption = res(0,i)
.Left = 14
.Top = 25 * i
.Width = 60
.Height = 20
End With
'ajout de l'objet dans la classe
Set Ge = New gere_event
Set Ge.CButton = Obj
Collect.Add Ge
i = i + 1
Next
But it doesn' work, and I don't know why...
The error doesn't show where the code is stoped, but only show the call to display this form.
someone could show me some way to find the solution please ?
Thanks a lot.
View 1 Replies
View Related
Sep 24, 2013
I have a query which returns a calculated percent. I have ordered that in descending order, and now want to see the top 50. So (In Access 2010) I entered 50 into 'Return'. But it returns all of the records!
Is this because pct is a calculated field? How can I correct this? The SQL seems to be correct.
Code:
SELECT TOP 50 HeciFail1.POHECI, HeciFail1.POQTY, HeciFail1.FAILQTY, IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) AS PCT
FROM HeciFail1
ORDER BY IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) DESC;
View 1 Replies
View Related
Jan 24, 2014
I have two queries, one base upon the other. I would like to combine them (If Possible) into one query so I can embed them into a form or report. I have tried without success at finding the answer on the forum as well as searching the web.
The table lists employee numbers and dates they worked. I need a count of how many employees worked each year, based upon the paycheck date, not the actual date worked. Pay check dates are two weeks apart. An extreme example, is the first pay check date of 2010 was on 1/1/2010, but all the days worked were in 2009, this would have to be included in 2010 not 2009(See the query for further date calculation understanding). Anyway, the date calculations are not the issue here.
I only have one table, so if I am not mistaken, I can't use the WHERE (SELECT... JOIN) feature. I also was unsuccessful at using SQL DISTINCT.
I am running ACCESS 2010 Tables are ACCESS 2007.
OS is Windows 7 Ultimate.
I have included a same database with the queries. qryEmployeesAnually2 is the results I am trying to achieve.
View 7 Replies
View Related
Dec 5, 2013
I've got these expressions in a query to extract parts of an mp3 file full path from a field and just let the name of the song.
exp: Mid([imported];[exp3]-[exp2];Len([imported]))
exp2: InStr([imported];"")-4
exp3: InStrRev([imported];"")
exp4: Left([exp];Len([exp])-4)
"exp4" is the clean name of the song.
Now how can I update my "song name" field (which is empty) to be same as "exp4" . Is it an update query? If so how can i do it?
View 2 Replies
View Related
Feb 20, 2012
I am new to Access 2007 and I am having trouble in creating a form. It is simply a data storage table which would be fed in by users. That it.
I need to create a user friendly form so that :
When the user selects : Career_Goal field as "None", the next field called: Years, should be automatically be populated as 0.
In any other case, they should be able to select the years from the dropdown list.
How do I create this if else conditions? Do I use expression builder, if yes then how?
View 1 Replies
View Related
Jul 23, 2015
I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:
10Jul15: Sales
and then adding Criteria like:
EndDate = #10/07/2015#
It doesnt work.
View 3 Replies
View Related
Jan 21, 2014
My boss has a form based on a rather large table with a lot of records/fields and she wants to be able to have a field where she can enter something and it will seach every record in the table and return the results in a table. How do I do this?
View 9 Replies
View Related
