Round Up To Next $25 Increment
Need a little help with rounding up. In A2K I have a form with a textbox that displays a security deposit. Security deposit is calculated by rounding the payment to the next $25 increment, hence a payment of 324.53 should have a security deposity of $325.00, but a payment of $325.01 should have a security deposit of $350.00. The code I am using for a datasource for txtSecurity deposit usually works correctly, but for this payment ($324.53) it rounds to $350.00. Here is the code..
txtSecDepRndTo holds the $25 incremental value.
Incidentally, if the payment is $324.49 my code rounds the Security Deposit to $325.00 as it should.
Any help would be greatly appreciated.
10 minutes before christmas day here in Australia and I cannot for the life of me sort this situation out. From what I have seen searching through the board, you are tolerant of Access Idiots such as myself.
So having brownnosed a little, here is the dilema, and it is somewhat involved (by my standards at least).
I am thinking I am missing tables and relationships for those tables. Try as I might I cannot get anything to work as I want.
I have an extensive collection of vinyl records. Close on 3000 10'' 12'' EP LP and so on. I use them as a traditional DJ using turntables.
I use every track whether they be on a 12'' single or an LP in my DJ mixing, hence the need for single track listings.
This then creates the problem.
I have for example, 15 versions of Depeche Modes "Enjoy The Silence" and I use them all.
I cannot seem to get the DB to be able to "autofill" the ARTISTS area. Subsequently I am looking at typing in Depeche Mode some 15 times and the same for the title "Enjoy The Silence". The "MIX" area isnt an issue as they are more often than not different.
Add to that I have perhaps 300 Depeche Mode songs that I will put into the DB. Again I do not wish to type the name every time.
The same stands for the "LABELS" area.
This will happen for other Artists as well. The idea of trying to type in all the artists into table and then the Titles and then the labels seems laborious and somewhat redundant. Again, I have 300+ Pet Shop Boys songs I need to input.
I am certain I am missing something extremely simple and quick. All the other areas I can sort out but these three are causing my head to go adventuring without me. I believe it went to Cuba last time.
My apologies for the verbose post, maybe half the trick is knowing what the question is and how to ask it.
What sum can I use to round down on a report
I am running a query that has this function
MyRoundedValue: Round([Bonus Total],2)
I am trying to round up the value in Bonus Total.
Currently the result shows 5140.602 and I want it to round up and show 5140.61
Is this possible? For the life of me I cannot figure it out..
I have a field within a table entitled "Name Display" This displays names in the format:
I have used the following:
Full Name: Right([Name_Display],Len([Name_Display])-InStr([Name_Display]," ")) & " " & Left([Name_Display],InStr([Name_Display]," ")-1)
To rearrange so format is displayed as forename,surname
However my output with some fields appears as:
SARAH WAILES, with spaces inbetween.
Can anyone suggest a way of removing these spaces?
In a query I have placed functions that work fine. Now I needed to round a currency number to the nearest $10. ex. 224.49 would be 220.00. I used round(xxxxxxx,-2). this gives me an error. positive 2 works fine. What's the deal? thanks for anyone who can help me. :)
Hi, I have this query and I would like to have the avg display with only 2 decimal points. This is my SQL and I think I have to use this code but I'm not sure.
If I ad this after the SELECT statement my query will not work. What am I doing wrong?
SELECT [Attendance for Avg].CRN, Avg([Attendance for Avg].[CountOfStudent Attended])
AS [AvgOfCountOfStudent Attended]
FROM [Attendance for Avg]
GROUP BY [Attendance for Avg].CRN;
I track reports in a database. I have a date field [Approval_Date] in the database table. I want to review the reports after they have been approved for 6 months to see if the actions fixed the problem, so I created a query based on the table and used the code "6-Month Review Date: [Approval_Date]+180". This works great except that our meetings are always on Wednesday. So, is there a way to modify my code so that it calculates the 6-Month Review Date as 180 days after the Approval_Date, but then rounds up to the next Wednesday?
I really appreciate the help.
Please help me with the round function. I want .5 to round to 1.
Here is an example of my data: (18+18+18+20)/4 = 18.5 rounds to 18. I want it to round to 19.
I used the following expression:
RoundACT Composite Score: Round((([Column1]+[Column2]+[Column3]+[Column4])/4),0)
I have values for example like, 0.03, 1.14, 1.28 I'd like to round them to the nearest tenth. So resulting values will be as follows:
0.03 = 0.00
1.14 = 1.10
1.28 = 1.30
How do i achieve this? What's the formula?
I've happily been working with my new switchboard only to find it tells me I am limited to 8 entries. How does one get round this - create switchboards which link to switchboards, different pages of switchboard...I've got a bit lost and would much aprreciate any advice.
Yes i have a round fuction on one of my calulations as it was displaying 32.222229 instead of 32.3
so i did this.
if (varSumXCPIC = "" or isnull(varSumXCPIC)) then
varSumXCPIC = "0.0"
But now when i veiw the page with the value = 0.0 it give me Invalid use of Null: 'Round'
any help on how to get ride of that erro would be great. thanks
When I did that Shaped Analog Clock, I stole the idea from our own Scott. Then I stole another bit of code to "shape" the form.
Hower, the position of the hands relied on values stored in a table, which in turn were retrieved by use of domain functions.
It was buggin' me so, I re-examined my trig functions and went back to the code; employing just the trig functions to garner the time and position of the clock's hands.
And a special thanks to Scott Cybak for his slick code/memory mods.
Once launched, the Access window hides itself and the clock may moved by 'grabbing' the tiny spot above the 12:00 marker.
Click the UA logo in the center to close. You can use the shift key to open it if you want to edit or examine it.
If you just launch it, the clock will open and display your system time.
Tested on Win 2000 on 1.6.gHz P4 and WInXP Pro on 2.4GHz P4 with in Access 97, 2K and XP.
All flavors are in the zip and some notes too. zip = 190K
Other colors (of the clock) are available upon request. Just PM me.
Just a re-coded toy.
How can I get the following statement to round? This is in a txt box on a report
=Avg(IIf(IsNull([Reopened TAT]),[TAT],[Reopened TAT]))
Using vb code.
I have a variable called MyCash.
I'm going to do calculations on it.
If the figure say: 29.97934
i want this rounded so there are only 2 figures after the decimal point.
how do you round in vb code?
I know I have had this problem a few years ago but don't remember how I got around it...
I just need to round up to the next integer up. It will always be positive. the best I can think of at the moment is FIX ing the number then adding 1 to it. this isnt ideal becuase id have to be sure I want adding 1 to an already round number, but it isnt difficult.
Is there a function i am forgetting that will do this for me???
Hope there is
Thanks for your help
I am attempting to use the VB 'round' function in a button on my form.
I am passing a number variable to it and I keep receiving the below error message:
Runtime error: 2465
[Form Name] can't find the field "|" referred to in your expression.
My expression is just this:
dim tester as double
tester = Round(tester, ).
Why do I keep receiving that run time error?
Hi, im researching software use for a project and ive come to find im terrible with access. I know access is the right tool for the job here but after reading all the ebooks, sites and looking for things i think i need a bit of Q and A. I dont want solutiuons here (although i am trying to build one) i just want a nice direct answer to a few of my issues.
1. The task is to book a local swimming pool out to people, some bookings are pre booked and some are booked as special functions like kids parties.
How would i store this info. ATM i have a table for storing apartie, they get the usual stuff but more specific they have to book a slot, morning or evening and are charged £100 for a morning slot and £250 for an evening slot.
Im having trouble breaking this info down
2. When bringing a record up in a form i want to display in the same view all the info of such a booker, so that will be slot, date and amount owing. How would i go about that
3. As there are parties and prebooked things how do you store the latter into the db, or how would i go about creating a db for a whole year and filling the pre booked slots
pre booked slots occur either at the same slot and day every week but some are once a month . . how would i go about storing that
Any help, references would be great, all the samples ive seen so far are just extremely trivial and are normally centered around creating a table, entering some values and thats it. thanks for any help
Compile Error. Sub or function not defined. The highlight is at the word Round
Time_Diff = Round(JOIN_Time - SUB_SELECT_Time, 4)
I have a query which requires a calculated field. This field is avalue * 0.02. This is no problem but what I need is this calculated field to round up to the next integer. Excel has a function that will do this but I cannot find this in Access.
I am working on a PC with Windows 2000 Access.
I am trying to use the round up function for grading in a school setting to work. Sometimes 7.5 rounds up to 8.0 and sometimes it rounds down to 7.0. How can I have 7.0 to 7.4 round down to 7.0 and 7.5-8.0 round up to 8.0?
I'm trying to round up a set of numbers within in an Access Query. I keep getting an invalid function.
My columns are:
I need to round the sum of gross weight/package up to the nearest whole number. If the weight is 21.1 lbs I need it to read 22 lbs.
Can someone please help?
does anybody have an idea, why 0.805 is rounded to 0.8 and not to 0.81 when i use the Round() function?
Thanks for any help!
What's the trick to rounding to two decimal places in access 97? Thanks
Having trouble trying to round up numbers for a column in an expression in a query. can manage to use
round(number,num_digits) but cannot get roundup(number,num_digits) as I keep getting an error message saying it is an undefined function. can someone tell me where i am going wrong.
Is there such a thing as round down in VB? If so, what is the syntax? If not, how can it be simulated?
For example, if I have a value of 2.6 I would like to return a value of 2 rather than 3 (like the Round() function).
I have a small DB that I use to track coupon reimbursements due our company, from any of several vendors. Orignally, I imported the data into Access from a spreadsheet that I had set up in Excel. In this spreadsheet, Excel takes the total number of units (Coupons), divides this number by 6, rounds the result up, then multiplies the result by .37. In this fashion, Excel would return a value of .37 for anything up to 6. On the entry of a seventh unit, another .37 is added, and so on down the line. This allows me to calulate the amount of postage necessary when sending reimbursements to vendors, as I am reimbursed for this. In Excel, I used the ROUNDUP funtion to accomplish this, with the formula being:
I would like to do the same thing from Access to avoid having to enter the same data into a separate spreadsheet. I tried to set the same thing up in Access, and tested it, based on a value of 20 coupons. When I tested it, I received $1.11, which was correct, so it did work...sort've. Based on a total consisting of lower values, however, I am only receiveing "0". It remains "0", for example, until I enter atleast 4 for my number of units, then the total reflects ".37". Also, it is not tacking on the additional postage upon entry of the seventh unit.
As it stands, now, I have my test query set up as follows:
SELECT CPNMFR.[Total Units], CPNMFR.[Manufacturer Name], Round([total units]/6,0)*0.37 AS [Postage Fee]
WHERE (((CPNMFR.[Manufacturer Name])="dave"));
I believe the problem lies in the fact that Access may not be rounding up, though I am not sure.
Any assistance with this matter would be greatly appreciated.
How to assign value to a field based on the value of the same field in the previous record?
Autonumber has its limitations for my purpose. Because, after I start filling up a form, I some times cancel the form filling process, and it affects the continuous numbering.
I guess the issue can be resolved by using some thing like record count facility. i.e. current' record's field value = number of records till previous record + 1.
How to exactly codify or execute the above by vba coding.
The Field Name is "SerialNo.", and Table Name is "StudentData"
I have a database that has a few tables and in one of them (Originators), there is all the info about my loan originators including there comission level. I would like it when they turn in a certain amount of loans there commision level will increase. Is this posible? Here is my DB setup.
Originators- 1 to many-
OriginatorID,FirstName,LastName,OrigSSN, Address,City,State,PostalCode,HomePhone,WorkPhone, CellPhone,FaxNumber,EmailName,BirthDate,Level(This is the commision level, and its in %).
Customers- 1 to many-CustomerID,FirstName,LastName,SSN,HomePhone,MobiPh one,FaxNumber,Address,City,State,Zip,Email,BirthDa te,CreditScore,SpouseFirst,SpouseLast,SpouceSSN,Sp ouceBirthDate,SpouceCreditScore,OriginatorID.
Loans- many to 1-
Lenders- 1 to 1-
When a customer is added to the DB it is done with a form that has OriginatorID(which is autocreated), CustomerID(also auto) and some other of the customer stuff like phone numbers. When this happens I would like to have the originators % level increase, say after every 4 customers entered. Is it possible?
I am importing an Excel file with existing data using the Import wizard to build my table. It has an ID field (CID) which I would like to keep and set to auto increment starting with the next number.
Can this be done ?
Right now I have a form that displays as a datasheet with three fields, Date, Phone Number and Number of Calls. The date is autofilled with the current date, the Phone Number is a ComboBox who's record source is a table with a list of phone numbers and the Number of calls is just a textbox.
My problem here is with the ComboBox, I've go it so that when the form loads it automatically displays the first item in the ComboBox. I want it so that after the user enters the number of calls and goes to the next record the listbox will automatically display the next item. I'm familiar with c++ so I know the theory behind doing this but I'm not too good with VBA in Access yet. Should the code look something like this? What's the correct syntax.
Dim increment As Integer 'Declare integer'
Private Sub Form_Load()
Me.ComboBox = Me.ComboBox.ItemData(0) 'Display first item in ComboBox on form load'
'No problems here'
Private Sub ComboBox_LostFocus()
increment = Me.ComboBox.ItemData() 'save current value from ComboBox'
increment = increment + 1 'increment the integer'
'I think this should work although I don't know that the syntax is right'
'or if the integer increment is declared correctly at the top'
Private Sub ComboBox_GotFocus()
Me.ComboBox = Me.ComboBox.ItemData(increment) 'place value from ItemData in ComboBox'
'Access doesnt seem to like it when I use a variable in ItemData, I guess thats because'
'it always asks for a Long Integer, but I am kinda lost so...'
This is the problem. If I have a field that contains something like this:
How can I make the number portion (0000) increment by one when a check box is checked?
Thanks in advance
Query qselMailingList sort by field DateTimeStamp descending
Form frmMailingList with PK MailingListID (text)
NOTE: Algorhythm will increment through lower case alpha to upper case alpha. You must modify to take a greater value into account.
Private Sub Form_Current()
Dim chrLast As String
Dim chrNext As String
Dim lngMax As Long
If Me.NewRecord Then
On Error Resume Next
chrLast = Nz(DLast("[MailingListID]", "qselMailingList"), "a")
lngMax = Asc(chrLast)
chrNext = Chr(lngMax + 1)
Me!MailingListID = chrNext