Modules & VBA :: Negative Numbers In Calculations

Nov 18, 2014

Ok i know negative numbers can be a bugger in calculations:

Code:
Function FEV1pred(height As Double, ClientID As Double) As Double

Dim a As Double
Dim Age As Integer
Dim DOB As Date
Dim TestDate As Date
Age = DateDiff("yyyy", DOB, TestDate) + CInt(Format(DOB, "mmdd") > Format(TestDate, "mmdd"))
'In this case the clients Age is 39
a = -0.0244

'Now for the fun stuff...

FEV1pred = a
'returns -0.0244 so i know the dim a holds the neg number
FEV1pred = (a * Age)
'returns 1.708 (which is incorrect, how this calculation is reached.)
FEV1pred = (a * 39)
'returns -0.9516 (which is correct)
End Function

So something about the dimming of Age seems to be doing something to the equation but i can't work out what?

View Replies


ADVERTISEMENT

Modules & VBA :: Using Between And Negative Numbers

Sep 15, 2013

Code:
If Me.Text250 = BETWEEN -15 And 15 Then
Me.Label253.Visible = False
Else
Me.Label253.Visible = True

End If

Even when text250 =8 label253 is visible.

View 8 Replies View Related

Changing Negative Numbers To Zero

Jan 3, 2005

Howdy Folks,
I have an equation that will often return negative numbers in an update query.
I need to be able to change any negative to zero, and update the table with that zero.

So far, all I've been able to do is ignore negatives with only positive numbers updating.


Thanks

View 1 Replies View Related

Importing Negative Numbers

Jan 23, 2008

Good morning,

We have some programmers creating text files from our mainframe. We will then import these text files into Microsoft Access. We have some negative numbers in the text files that the programmers have formatted this way for example, $100.00-. When I import the file into Access, it ignores the negative sign. I told our Lead Analyst that the programmers should format the negative numbers this way, -$100.00 in order for it to import correctly. She doesn't believe me and thinks I'm not importing the file correctly.

Does anyone have an literature on this so that I can show her so that we can move forward? Or can anyone confirm that what I'm saying is correct, it needs to be formatted with the negative sign in front of the number, not behind it.

Thank you

View 1 Replies View Related

Formatting Negative Numbers

Apr 19, 2011

I try to modify an access 2010 template from the MS website.The problem is that the template as is programmed does not accept as input negative prices. I need to use negative prices for offering discounts on my invoices. In my country the discounts are only displayed as products with negative prices/values. We do not use % discounts.

i try to create an products with negative price and to use it as an discount on the invoice. Both the products input form and the invoice input form give me the same error : "please specify an price list that is not negative" or "please specify non zero positive prices" ( sometimes it allow me to enter but when the software try to validate the form/invoice i just entered it give me those error/messages)

I changed all the field from currency in general numbers Where do I have to change in order to remove those formatting/condition..? The template has a lot of macros.. I an not able to change those macros if there are some formatting conditions in them. If it is allowed I will post the link to the free template i use to be more conclusive..Or I can give/ upload the template I use.
the link to my data base ( dropbox)

View 3 Replies View Related

Making Negative Numbers Positive

Feb 4, 2006

I have a query where I have some negative numbers (f.e. -403,26) which I like to make a positive (i.e. 403,26).

How do I do this??

View 3 Replies View Related

Separating Positive And Negative Numbers

Jan 24, 2008

I have a field on my report that contains positive and negative numbers. I'm trying to find a way to Sum the column twice for two different fields (one for positive sum and the other negative) To make this easy let me give a small example with explanation of the issue I am having. 14 rows of data containing positive and negative numbers in a field called [Rooms]:

1) -1
2) 1
3) 1
4) 1
5) 1
6) 1
7) 1
8) 1
9) 1
10) 5
11) 1
12) -5
13) 1
14) 1

The positive numbers represent confirmations, the negative numbers represent cancellations. I need 2 fields to separate these. The first field's control source is Sum([Rooms]). No problem there. Access does the math and comes up with "8" confirmations which is correct. Then in the second (Cancellation field) I try something like Sum([Rooms]<0) to get the negative numbers which should add up to -7 (-1+-1+-5), but instead I get -3 because it's not summing the values, it's really returning the number of rows that has a negative number. Is there any way this can be done at the ControlSource Property of a report field? Or if I could do a "Where" clause in a ControlSource I could acheive it another way, but I don't that is possible. Is this just something I'm missing with the way I'm stucturing the ControlSource or is it not possible. Or alternatively, is there a way to separate it in the query and have a separate query field for positive and negative going into the report. I can do one or the other in the query but I can't seem to do both without showing values of each type in both query fields.

Thanks for your help!

View 3 Replies View Related

Queries :: Rounding Negative Numbers Up To 0

Mar 13, 2015

I can not have any negitive numbers show up in my field so I need to round up and negatives to "0" ...

View 10 Replies View Related

Yes/No Fields Bring Back Negative Numbers

Mar 20, 2007

I created a database for a survey. There are a lot of number fields and a few yes/no fields. I did a query to give an average for the number fields and a sum of the yes for the yes/no fields. I get the right number for the yes/no fields, but the number is negative. What do I need to do so that the number is positive?

Thanks

Robyn

View 7 Replies View Related

Negative Numbers In Query Are Not Sortin Correctly.

Sep 26, 2007

I have a table with data that is similar to this:
HCP1 DATE (Fields)
-.1 7/19/2007
-.2 6/14/2007
0 9/9/2007
1.2 4/23/2006
-.3 4/4/2007
2.4 3/7/2007
0 2/5/2006
1.1 2/6/2007

Etc. There are 0's, negative numbers and postive numbers. Some with at least one decimal point. I am trying to sort the data by the highest number in HCP1 field. When I run the query in Access it looks great. But when run through ASP on the server it is only giving me the negative numbers, no 0's no positve numbers. It would return -.1 in this sample data as the first record.

Anyone know what might be going wrong on the server side of things. The query statement looks the same on both sides. (server and access)

Thanks for any help.

View 5 Replies View Related

Reports :: Negative Numbers Show As Positive

Aug 28, 2013

I am trying to have a number that is negative hold its negative value but show as positive when it is displayed on a report.

View 6 Replies View Related

Reports :: Preventing Negative Numbers In Report

Mar 7, 2014

I have a report where I have equipment tested every 90 days. I have a field called TEST DATE formatted using the medium date format

(07-Mar-14). I have another field called RETEST DATE where my query adds 90 days and displays the Retest date as (07-Jun-14).

I have a unbound box with a heading called "Day(s) passed retest date.

In the Control Source I am using =Datediff("d"[Retest Date],Now()). In this scenario the result is -90.

Is there a way for this -90 to stay at 0 until the 91st day where it will count up from there?

View 11 Replies View Related

Tables :: Showing Negative Numbers In Brackets Without Selecting Currency Format

Jul 7, 2014

Is there a way to show negative numbers in brackets without selecting the Currency format?

I want to show (75,000.00) and not -75,000.00

View 2 Replies View Related

Query For 1 Or 0 In One Field According To Positive Or Negative Numbers In Another Field

Jan 13, 2015

I need to create a query in Microsoft Access to enter 1 or 0 in a field based on whether numbers in another field is positive or negative. Here are the details.

1 entered in a "late" field if "TotalHoursAheadOrBehindScedule" field has a positive number
0 entered in a "late" field if "TotalHoursAheadOrBehindScedule" field has a negative number

View 2 Replies View Related

Modules & VBA :: Automate Line Numbers And PO Numbers

Aug 24, 2014

I have 2 fields that I would like to automate if possible

One field is called "p/o number" and another field called "line no"

These fields are part of an ordering database

Let say I have 200 items to purchase form 10 suppliers

And form example 20 items from each supplier

What I do at present is put the order number on each line item and the line number

example

p/o number line no

1 1
1 2
1 3

2 1
2 2
2 3
2 4

What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.

Example

1 1
2 2
3 3

2 1
2 2
2 3

View 5 Replies View Related

Calculations On Main Form Do Not Reflect Calculations On Subform

Sep 28, 2015

I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:

=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]

#Error results when the doc is put into a form mode.

Now when I input each value in the equation above separately.I still get no entry.

For " = sbfCustomerRoomUse" , I get #Error;
and for "= txtTotalPhoneUse", I get #Name.

CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.

View 14 Replies View Related

Modules & VBA :: Time Calculations - Inconsistent Results?

Jul 9, 2014

I work for a bank and have build a few little DB's for differant groups but my current project is a work flow tool for a department. I have it 90% completed and i still have to build in the reporting side. Part of the reporting is caclulating times and this is where i am having problem.

I have some coding built but i am getting inconsistant results, as an example if i am trying to calculate the total amount of time between [start Time], [End Time] then minus any [Stop Time].

Code:
Me.Total_Time = Format(Int(Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM])), "00") & ":" & Format(Int((Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM]) * 3600 - (Int(Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM])) * 3600)) / 60), "00") & ":" & Format(((Workhours(DateAdd("n", Nz([Total_Stop_Time]), [Processor_Date_Stamp]), [Date_Sent_to_RM]) * 3600 Mod 60)), "00")

I have this on a command button, i have this same code for a few other calculations and in some cases it seems to be working i have just changed my start and stop fields along with what stop time to take out in each case. I have attached some parts of my DB, one of the modules, my main table and the form where i am running the coding.

View 4 Replies View Related

Modules & VBA :: Calculations Using Previous Record Calculation

Oct 4, 2013

I have partially done it using PrevRecVal module I found on the web.

I created what I need to do in Access in Excel first, the problem doing this in Access (for me) is I need to refer to the results in the previous record, PrevRecVal worked but I need to finish if possible.

The main report holds client policy data of which InvestAmount is used to start the calculation of the sub report, the data entered to run the calculations on the subreport would be

QtrDate
CurrentUnitPrice

The Excel spreadsheet is attached ....

View 10 Replies View Related

Modules & VBA :: Subform Calculations Based On Parent-form Data?

Jul 17, 2013

I have a form with a subform in a navigation menu. Something like:

Navigation Form
NavigationSubform
ParentForm (header data and some unbound calculation fields)
Subform (Multiple lines tied to query)

I have some fields in the ParentForm (i.e PF1, PF2) that would effect the values on the Subform (i.e. SF1, SF2). Also, there are some user editable values on the subform that will calculate the remaining fields on the subform (still using data from parentform).

My calculations work fine for the changes made on the subform. However I need to be able to calculate all the children lines on the subform when the form loads or a change is made to certain fields on the parentform. My On_Current event only wants to recalculate the first line.

Parent Form
PF1=10
PF2=3
Subform QTY ADJ SF1 SF2
Line 1 5 .05 10.6 112.89
Line 2 8 .14 10.38 105.15
...
Line N
SF1=PF1+(PF2/QTY)
SF2=SF1*(SF1+ADJ)

So if PF1 or PF2 were changed then all the lines is subform would recalc SF1 and SF2. If changes were made to QTY or ADJ, then that line would recalc SF1 and SF2.

View 3 Replies View Related

Modules & VBA :: How To Consider Numbers As Indexes

Jul 15, 2015

I have sheet names stored in a text field.

sample data: (assume the field name as SheetName)1 (considered as an index) MySheet (considered as a Name)
How to handle them both in a single statement:vartype(SampleData!SheetName) returns 'Text' as Datatype for all values.

How to consider the numbers as indexes?

View 3 Replies View Related

Calculations On Calculations--too Much For Access?

Jun 8, 2005

I have an odd problem that has stumped me for several days. I'm working on a form that contains a chart. The chart is based on a query, which is based on another query, which is based on a table. In the top query, I need to put some calculated fields that operate on other fields. But when I try to multiply two fields together, I get Null. I'm pretty confident that the fields I'm operating on are numerical.

The fields of the query should be:

MaxOfEverInSchool
MaxOfSurvivalToGrade
MaxOfEverInSchool

With these being the calculated fields:

MaxOfSurvivalToGradeCum: MaxOfEverInSchool*MaxOfSurvivalToGrade/100
MaxOfCanReadCum: MaxOfEverInSchool*MaxOfSurvivalToGrade*MaxOfCanRea dCum/10000

Any insight would be greatly appreciated--I just can't understand why Access would suddenly refuse to do math.

View 1 Replies View Related

Modules & VBA :: Converting Text To Numbers

Sep 19, 2014

I have a short text field where numbers such as "15.00", "2233.56", "-300.00" are stored. Now I want to convert the text field to numeric so that I have actually 15.00 or -300.00 stored as a double. I am going to do calculations on these fields. How can I achieve this conversion?

View 4 Replies View Related

Modules & VBA :: Calculate Numbers In Query

Mar 1, 2015

I am looking to calculate numbers in query like this if the field name in query is Unit and the second field name is amount and i want that the amount field to calculate the unit field on this criteria..Suppose if total unit is 393 so the calculation should be like this;

First hundred should be calculated with 100.00 X 5.79 = 579.00
Second 200.00 X 8.11 = 1622.00
Third 93.00 X 12.33 = 1146.69

Total should be displayed in the amount field = 3347.69

View 14 Replies View Related

Modules & VBA :: Pull Numbers From String And Get Max Value?

Jan 3, 2014

I have a series of IDs in an 'articles' table stored as text, e.g.

hb-123456789-e-068
hb-123456789-e-0069
hb-123456789-e-70
hb-123456789-e-00027

and I'm trying to pull the max value of the number after the -e- for a given set of them. In this example, I'd want to return the number 70. I'm then going to use that to create the next ID and populate another field.

The IDs are not used as the primary key. And while the previous IDs used leading zeros inconsistently, new IDs will not have leading zeros.

Here's what I have so far, but it doesn't seem to pull the number after the -e- at all. I think this section here is the problem, even though the same logic works in a query:

Code:
selectedERef = Val(Right(rs![masterArticleID], Len(rs![masterArticleID]) - InStrRev(rs![masterArticleID], " - ")))

Code:
Public Function MaxArticleERef(hbID As Long) As Variant
On Error GoTo err_handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim maxERef As Variant

[code]....

View 3 Replies View Related

Modules & VBA :: Finding All Numbers In A Range?

Jul 16, 2014

I need to create a very simple database that would just store records and produce a couple of reports. I have three tables: one with the roster , one has records of the inventory items people from the roster receive and another one contains types of inventory we have. Everything is very simple except for one part. Every time we make a record of an inventory item given to someone, it requires not only employee id and inventory type from the existing tables. It needs us to enter a serial number of an item. This serial number contains a letter and a number.

Looks something like this - M100. Many people receive a consecutive set of inventory items. For example, from M100 to M150. There is no way to have a separate table with all serial numbers because they constantly change. That is why we need to have two text boxes that would allow us to input a range of serial numbers or just one number. Then the program should separate numbers from letters, evaluate the range, create new records of numbers and then put new numbers and a letter back together into one field in the table where we have all inventory records.

I found the following code online that allows me to find numbers within a range, but it only works for numbers.

Dim varRange As Variant
Dim lngLow As Long
Dim lngHigh As Long
Dim lngCounter As Long
DoCmd.Hourglass True

[code]...

I then found a piece of code that is supposed to separate numbers from letters, but I can't find a way to make it work.

Public Function FindNum(strName As String) As String
Dim strTemp As String
Dim i As Integer
For i = 1 To Len(strName)
strTemp = Mid(strName, i, 1)
If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then
FindNum = Right$(strName, Len(strName) - i)
End If
Next i
End Function

how to alter the code to make it work for my specific situation.

View 6 Replies View Related

Modules & VBA :: Take Data From One Table And Insert Into Another Whilst Doing Calculations On Data

May 2, 2014

I am trying to use SQL to run queries in our access database in order to (hopefully) speed things up. I'm trying to create code that basically takes data from one table and inserts it into another whilst doing calculations on the data.

However I can't get past this:

Code:
Private Sub Test_Click()
Dim strSQL As String

strSQL = "CREATE TABLE [TempRedAmberGreen]" & _
"AS (SELECT " & _
"[ID_CHK] String," & _
"[Red] String," & _
"[Amber] String," & _
"[Green] String)" & _
"FROM [035 - Meter Point HH Data];"
DoCmd.RunSQL strSQL

End Sub

It keeps saying "Run-time error '3292': Syntax error in field definition.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved