Modules & VBA :: Using Between And Negative Numbers
Sep 15, 2013Code:
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.
ADVERTISEMENT
Modules & VBA :: Negative Numbers In Calculations
Nov 18, 2014Ok 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?
Changing Negative Numbers To Zero
Jan 3, 2005Howdy 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
Importing Negative Numbers
Jan 23, 2008Good 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
Formatting Negative Numbers
Apr 19, 2011I 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)
Making Negative Numbers Positive
Feb 4, 2006I 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??
Separating Positive And Negative Numbers
Jan 24, 2008I 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!
Queries :: Rounding Negative Numbers Up To 0
Mar 13, 2015I 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 RelatedYes/No Fields Bring Back Negative Numbers
Mar 20, 2007I 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
Negative Numbers In Query Are Not Sortin Correctly.
Sep 26, 2007I 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.
Reports :: Negative Numbers Show As Positive
Aug 28, 2013I 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 RelatedReports :: Preventing Negative Numbers In Report
Mar 7, 2014I 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?
Tables :: Showing Negative Numbers In Brackets Without Selecting Currency Format
Jul 7, 2014Is 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
Query For 1 Or 0 In One Field According To Positive Or Negative Numbers In Another Field
Jan 13, 2015I 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
Modules & VBA :: Automate Line Numbers And PO Numbers
Aug 24, 2014I 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
Modules & VBA :: How To Consider Numbers As Indexes
Jul 15, 2015I 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?
Modules & VBA :: Converting Text To Numbers
Sep 19, 2014I 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 RelatedModules & VBA :: Calculate Numbers In Query
Mar 1, 2015I 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
Modules & VBA :: Pull Numbers From String And Get Max Value?
Jan 3, 2014I 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]....
Modules & VBA :: Finding All Numbers In A Range?
Jul 16, 2014I 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.
Modules & VBA :: Converting Dates Into Week Numbers
Oct 2, 2013I am trying to write some VBA to convert a date into a week number.
My work colleagues do not not what system is used to define the week numbers. So I have gone through all their records for a few years and deciphered this pattern:
I need the code to define that there are 52 weeks in a year. The last sunday of Dec is the beginning of Week 1 for the following year. Except when it is a leap year where Week 53 exists. otherwise it only goes up to week 52.
Start day for the week is Sunday. End day is the Saturday.
Some examples to check against:
22nd December 2013 = Week 52 2013
29th December 2013 = Week 1 for 2014
----
21st December 2014 = Week 52 for 2014
28th December 2014 = Week 1 for 2015
----
20th December 2015 = Week 52 for 2015
27th December 2015 = Week 1 for 2016
----
25th December 2016 = Week 53 for 2016
1st Jan 2017 = Week 1 for 2017
-----
Tried various methods already
iso 8601, wrong week start day
intWeek = DatePart("ww", datDate, vbSunday, vbFirstFourDays) Was right for 2013 but wrong for later dates.
Modules & VBA :: String Into Numbers - Using Array For Lookup
Jan 15, 2014I have to decode a string into numbers and to avoid to find out the values for 47 options by select case I though about an array.
I want to decode
Number Letter
10 A
11 B
12 C
13 D
14 E
15 F
16 G
17 H
18 I
19 J
20 K
...
For example the string "ADEG" would give as result
10 13 14 16
So I would have to loop through the string and "decode" each letter into a number.
As I have still problems to understand array, need to define the dimension of the array, it has fix 47 entries to decode
Dim myarray (47,2) as variant
mayarray=(10,"A",11,"B",...)
Correct?
Modules & VBA :: Auto Numbers When Priority Change
Jul 3, 2014I have a very basic application to manage the tasks. In the main form "Tasks" one of the field is the priority (Order by) and I have this example:
Task Priority
B 1
A 2
C 3
E 4
D 5
If the user change one priority, I need a code to automatically change the others too respecting the new priority order. For example if the user change the priority 3 to 1 for the task C, the other numbers have to change to become:
Task Priority
C 1
B 2
A 3
E 4
D 5
There is a way to do this?
Modules & VBA :: Declare Variable With NOT Rounding Numbers
Oct 20, 2014I need to declare a variable (max_nog_leveren ) but that variable may not be rouded or only after 5 digits so as example 15,xxxxx
I am now using it as a "Integer" but that is not working very well..
Code:
Dim iRet As Integer
Dim strPrompt As String
Dim strTitle As String
Dim max_nog_leveren As Integer
Dim strQuery As String
Dim nettohoeveelheid_toevoegen As String
[Code] ....
Modules & VBA :: Calculate Total And Convert Numbers To Words
Apr 7, 2014I have a form that have textbox that calculate total and then i want to convert numbers to words. Iv got the code with function currencytotext but when i set the code source of another textbox to:
=currencytotext([inv_total])
I have an error. I saw this on internet but i think bcz I am using access 2013 may be the syntax is different a bit.
Modules & VBA :: Database For Quotations - Modifying Sequential Numbers
May 29, 2015I have a database for quotations. The database automatically generates a new quotation number every time a new quote is started. This works great, and I am very happy. My next task is to allow the employee to pull up a quote that has already been generated and edit it. I would like for the new quotation process to be followed step by step, but with all of the information already filled in.
This will allow for any edits that need to be made, and keep from having to re-enter a lot of data. I want one thing to change, which is the QuotationNumber. It is currently formatted by "yyyymmdd-01" for the first quote generated on that day. I want the edited quote to have a QuotationNumber formatted by "yyyymmdd-01a". For every edit that letter change going through the alphabet in order. How would this new QuotationNumber code differ from that of the Other?
QuotationNumberCode.PNG