This is probably an easy question, but I can't figure out how to get a table to show numbers in 2 decimals points when they are inputted with 2-5 decimal points. I changed the DATA TYPE to Number and DECIMAL PLACES to 2, but when I do that, it just rounds it to a whole number. Please help, thanks!
View RepliesHi, 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.
FORMAT(CountOfStudent Attended,'.00')
If I ad this after the SELECT statement my query will not work. What am I doing wrong?
Thanks!
~D
This works
SELECT [Attendance for Avg].CRN, Avg([Attendance for Avg].[CountOfStudent Attended])
AS [AvgOfCountOfStudent Attended]
FROM [Attendance for Avg]
GROUP BY [Attendance for Avg].CRN;
Made an access data base for our company policies and procedures.
Fields are:
Section
Section Title
Policy
Explanation
Related Documents - (hyperlink field)
Running a query on the table for the report the delivers the policy.
the Section Field is Rich Text - this is the only way I can figure out how to display decimal point divisions like, 1.1.0, or 6.5.3 (for relevant sections).
Query won't return anything when I set a parameter on Section Feild and I suspect it's because of the Rich Text issue. I've tried smart tag but no avail.
And the next thing I want to do is have an index that gives me Section and Section Title with a go to. How best to achieve that???
I have a problem when storing a calculated field with two decimal points. If I set the field to double, fixed, 2 decimal points. For the field it will show the calculations as:
21.364 as 21.36
21.563 as 21.56
21.272 as 21.27
Which is how I want it to be, however when I run a summary of the field rather than showing 64.19 it calculates as 64.20, I know that this is the correct figure but is there any way I can get it to show me the 64.19?
I have a Table with a Field set to Number, Single, Fixed, 2 Decimal points in which I enter Hours (ie 11.25) then at some point I want to extract those new entries to create a Text file transfer.
I have a Macro which extracts those new entries from the main Table and copies/appends them to a new Table which contains only the new data I need to create the File to upload into a Payroll system (using TransferText option).
It all works well EXCEPT, the File it creates insists on showing 8 decimal points and I just cannot get it to show 2 only.I have tried using a calculated field, setting the secondary Table field to Text.why or where these 8 decimals are coming from.
Can you help we have a data with a list of jobs that scores points, I need to build a query or scirpt that will add 5 points every 30 day fro the date the job was submitted this will help old jobs come the top of the list.
View 1 Replies View RelatedHi there,
I am not sure the best way to do this,
I have a field with values that look like: 1.69553366385509 or 0.0061705541157596
I want these values to look like: 1.6955 or 0.0062 (4 decimal places [rounded up if possible])
Thanks for your help with this
Sue
I have a query that is pulling from a single table of expenses the sum of expenses for each quarter. I am using only three fields: the quarter, expense type and amount paid. I am using - Qtr: DatePart("q",[PostDate]) - to obtain the quarters, grouping by quarter and then by expense type. However, the record returned for the 4th quarter shows this result: "3.0026" It should not have four digits after the decimal.
Results for the other three quarters in the same query return correctly with two digits after the decimal point. The query below was suggested to me in order to make the sum returned have only two digits after the decimal:
AmtPaid: Sum((SELECT FORMAT(PymntAMOUNT, 2) FROM tbl_expenses))
The result of this was a curious information box that said: "At most one record can be returned by this subquery." The query returned nothing. I have checked the formatting in both the table and the query itself and the format is "Standard" for both. So, I can't figure out what is producing this 4 digit problem, and why it is only in one record while all the others have two digits after the decimal.
i want my data to have only 2 decimal places. So i change the property of the table to only allow 2 decimal places and i did the same to the report and they are still all over the place....for example a field would say 8.12233444 and another 1.17.....what do you think is the problem?
View 3 Replies View RelatedI'm setting up a table that has several percentage fields, for which I want to display 3 decimal places in datasheet view (and later in forms and reports). I've tried to set up the fields in a variety of ways, but either I don't get a percent format, or I get 3 decimals with 2 zeros at the end. For example, 73.913% shows up as 73.900%. This, of course, causes problems in calculations, so that for a total percent field I'm getting things like 99.900% instead of 100.000%. I've tried various combinations of formatting properties for these fields (Field Size, Format, Decimal Places, and Scale) but I have yet to hit on the right one to simply display the correct value in the format of a percent.
View 8 Replies View RelatedI have an Access 2010 table which shows Latitude and Longitude. However instead of it being in a decimal format it is displayed as 4000000000. I need to add a decimal point so that it looks like 40.00000000. If I configure the Field Properties to Decimal with a scale of eight and decimal places set to eight I can manually convert all of the data to the correct format. But I would like to have this happen automatically from a query.
View 4 Replies View RelatedI have about ten append queries to group various financial data to one main table.
I have used the round function (iff (Round(Nz([FIN_data]),2)) in the queries to round the original data into 2 decimal places but there is still one or two lines exceeding 2 decimal places.
What is the better approach to have only 2 decimal places for all append data?
I have a very annoying problem with my break points that I can not figure out how to fix. I have a database that when I place a break points on code it will not break the code and go into debug. I have created a very basic form with one button and the following code:
Option Compare Database
Private Sub Command0_Click()
Dim str As String
str = "this is a test"
MsgBox (str)
End Sub
The message box will pop up but the program will not stop on either on the str... line or the msgbox... line. I'm sure some how or another I turned off the debugger but I do not know how to turn it back on.
Thank you for any help.
one more thread of mine today
I have this table:
-> tblTest
Code:
test_id | test_Name | test_Date | test_value
------------------------------------
1 | Company A | 01.01.2010 | 0,90
2 | Company A | 02.01.2010 | 0,95
3 | Company A | 03.01.2010 | 1,10
4 | Company A | 05.01.2010 | 1,05
5 | Company B | 01.01.2010 | 0,9
6 | Company B | 02.01.2010 | 1,20
7 | Company B | 03.01.2010 | 1,15
8 | Company B | 05.01.2010 | 0,95
9 | Company B | 07.01.2010 | 0,80
The important level is 1,00
Now I will, for each company, check:
- Is the value of ID-1 >1 AND the value of the ID-2 <1, THAN give me a 1
- Is the value of ID-1 <1 AND the value of the ID-2 >1, THAN give me a -1
In this example the result should look like this:
Code:
test_id | test_Name | test_Date | test_value | test_Code_result
-------------------------------------------------------------
1 | Company A | 01.01.2010 | 0,90 |
2 | Company A | 02.01.2010 | 0,95 |
3 | Company A | 03.01.2010 | 1,10 |
4 | Company A | 05.01.2010 | 1,05 | 1
5 | Company B | 01.01.2010 | 0,90 |
6 | Company B | 02.01.2010 | 1,20 |
7 | Company B | 03.01.2010 | 1,15 | 1
8 | Company B | 05.01.2010 | 0,95 |
9 | Company B | 07.01.2010 | 0,80 | -1
Probably I have to carry out this in vba.but i dont know how I can calculate across the datapoints (rows).
I have a textbox called txtcomments on my form. It is bound to a table field defined as Memo. Now i normally would enter text and produce a report from the comments in my table.
I want to be able to enter text like this in my form, so that my report can be formatted like this: All in one textbox (memo table field).
Like this:
*************in my txtbox************************
Accomplishments
Etc
Etc
Etc
Current / Upcoming Work
Etc
Etc
Etc
Risks / Challenges
Etc
Etc
Etc
***************end of txtbox***********
Is this possible? If not, how do you think i can achieve this? I am open to suggestions, please help :D
My form has a Memo field that stores "To Do" descriptions. My user wants to be able to add bullet points next to their entries. There could be multiple To Do's in a single field.
Any help is appreciated.
So I have a simple table with Date, Points Earned, Points Used and Client ID number. Id like to tally the points for each client ID, after each time they either use or earn points. How do I do this?
View 14 Replies View RelatedI want the user to be able to see the source/reference of (many of) the different data points in my database, so I need to store sources/references in the database itself. I want to have all references in one single/common table (list of references). Each reference may be the source of many data points, in different columns in a given table, and in different tables. Is this possible, and can it be done in an elegant way?
View 2 Replies View RelatedI have a report that is purely for points gained from products. Each product is split into 3 (e.g. Investment A, B + C) and there is a indicator that means the points gained can be added up (e.g. I for Investment and S for Savings).
I have made the report which is many unbound fields. There is a summary section for all points added together with fields Total Amount, Monthly Target, Quarterly Amount and Quarterly Target.
Then the report is split into each of the 4 products (Each of these have different amounts of points. Number indicates it):
Investments (10), Savings (15), Pensions (15) and Protection of Life Savings (20).
Each of these have 4 fields - Total Amount, Target Amount, Quarterly Amount and Quarterly Target.
I need a way to generate these point scores using the Product_Points field. I already have Quarterly Target and Target Amount because there is a set field called Points_Annual_Target.
EDIT: I also don't want it to come up with the 'insert parameters' so all the info is one page.
I wrote a basic query that allows 1 field to search another, and if there is a match, it spits it out, however, I am getting 20,000 matches. I want to add another level of query to reduce the 20,000 matches down to 1 or 2 or none...
So I consolidated a government list, publicly available, into 1 field, and created a table that I can use as my query against the large government list:
SELECT [Consolidated Denied Party Report].*, [Consolidated Denied Party Report].[31]
FROM [Consolidated Denied Party Report]
WHERE ((([Consolidated Denied Party Report].[31]) Like "*" & [Please Enter Your Search Term] & "*"));
this allows me to search for a word, like, create, and it gives me every single result however it also gives me hits to words like PROcreate, which is fine on one hand because it shows possible false flags, but it would be nice to also be able to query down a level.
I now want to have the query look at the word "create" but also look at another column that is say the country... Germany.. and if I have text in the second column, only give me a result if the word create and germany are in the same field.. if create and Italy were in the same field, it would not be a hit.
hi i want to create a loyalty points scheme though a database,
general rulse apply 1 point per £1,
when the customer spens some money the staff then go to the machine and input the relevent data.
then a statment will be printed out for that customer with the points to date, and thier purchase the amount of point collect with the new total.
To search the full name and postcode would be adiquit as the printed statment could be lost and so could the customer id get confused by the customer.
for the report the layout would look like this:
eg
customer ID
cathy smith
e-mail,
address,
postal code,
02/12/2007 points to date 200
05/01/2008 puchase made £30.00
05/01/2008 new points total 230
if they use points it would look like
02/12/2007 points to date 200
05/01/2008 puchase made £30.00
05/01/2008 points use 125
05/01/2008new points total 100
point can be enterd manually and removed manually as customers want to save like a saving scheme
please help me with is as i have no idea where to start and if it posible, and limited knoledge of access
thanks in advance
I am trying to create an attendance database, our company introducing the point system attendance,
Called Off (CO) 2 points
Left Early (LE) 1 Point
Tardy (Tar) 1 point
Employee can reduce point if they have perfect attendance for 90 days from the last day of violation. For example, an employee absent on 01/01/2015, he will received 2 points, the credit will giving on 04/01/2015, if there is no violation, but if he absent again on 03/31/2015 not only he will received 2 more points his 90 days will start from 03/31/2015, now he will eligible to get credit on 06/29/2015 and so on.So far I have created 3 tables and 1 query.
Tables
Employees: Id, Last Name, First Name
Points: Id, Description Points
Attendance:Id, Date, Employee ID, Points ID
Query
Date
Employee ID
Last Name
First Name
Point Description
Points
how and which formula to use which calculate the points based on above example.
I am a newbie, so please forgive me for such an easy question, but I am stumped. I attached a text file that shows the data I am working with. What I need to do is take each product (labeled Prod) and do a weekly sum on the quantities and compare against a set number to see if the quantity is lower or higher. For instance, I need to take column 12, regardless of value and compare it against set number. If the quantity is less, then I need to add the value of column 12 to column 13 and compare the summed value against set number. Again, if the sum is less than set number, I then need to take the value of column 14 and add it to the summed value of the previous step (sum of 12 & 13), then compare this new sum to set number. This process keeps taking place until I reach a summed value that is greater than set number. Once that happens I need to identify the column that sent me over the set value and hold that data. For instance, if column 33's (out of 52) summed value takes me over the set number, I want to know that it was column 33, so I can run further calculations against that value. The column header's are week numbers and I need to identify order points based on lead times and when I will run out of material. Is this beyond queries? I think so, but if it is, I don't know how to exactly begin the code in VBA either. I think I would use an If then Else stucture with a counter switch set from 1 to 52, unless comparison exits function, but not certain. HELP?????:confused:
View 3 Replies View RelatedDoes your Tools Reference have an MS Office 15.0 Object Library?
Just had to share this strange occurrence in the VB Code Module:
I use breakpoints all the time, was just using them yesterday.
Noticed that some events seemed to act differently this morning. The front-end was approaching 88 MB (after compress) so it is time for a change anyway.
Created a new blank Access dB (Access 2010) and imported all objects from my last backup revision. Reset the home page and tools References. Matched the settings side by side. As usual, the database shrank from 88 MB to 60 MB. The import all objects will usually compress things about this much.
From the screen shot, put breakpoints in previous code, added a new Lost-focus event and added a message box. None of the breakpoints work. The message box fires on the lost_focus and all of the code in the Click event works fine. But, not a single break point would work.
Totally powered down and restarted the Windows 7 32 bit Enterprise workstation. Still no change.
Took the screen shot of Tool-References from the code module of a two week old archive version. Used it to evaluate my new imported object database.To my surprise, the Microsoft Office 14.0 Object library is now Microsoft Office 15.0 Object Library.Also put all the objects back in the same order. Saved and closed the new imported object database.Now, the breakpoints work just fine.
I had failed to reference the Office 15 Object Library and the VBA Extensibility 5.Office 14 Object Library is nowhere to be found except on my 2 week old backup archive.
Is this a Microsoft auto update? Is this what drives the breakpoint in the code module?
I have broken up by master database, table, into several tables. They all share the same ID values since they came from the master table. So each table that I split off has a unique, matching, identifier, ID, in both the master table and the new table. Plus a previous matching identifier listed immediately below.
Example: Permit the master or main table and Builder a supporting table. There are five tables that I split off and all have an identifier similar to the two listed below.
Permit->ID
Permit->BLD
Builder->ID
Builder->BLD
All my tables have the same ID. But each table also has a identification numbers such as builder BLD, Subdivision SB and inspector INS>
Permit has the BLD, SB and INS
Builder has only one occurrence for each builder in the Permit table. As in the past it was identified by BLD number and now also ID. the use of BLD is from when I used visual dBase. The big difference here is that I have two ways to identify one is ID any other is by BLD, etc. My first thought is to identified by the BLD. I don't know why, but I guess I fear using a single "ID" to identify my various split off tables unique value or BLD.
I am new to this forum and Access 2000.
When creating a table I want to use a field to record hours worked to the tenth of an hour. When I enter 6.7 and tab to the next field the hours record as 7. I am using field size as integer, decimal as 1 and format as general number. I have tried numerous other combinations, but whatever decimal I enter rounds to show no decimal in the table. I will use this field in calculations.
I suspect the solution is simple, but I am not having any luck. I'd appreciate some help.
Gary4