I have a table in which i have fields that have numbers with a huge number of digits after the decimal. i.e 10.2356491876545.... when i change the properties of the field to show only two digits. it does display two digits but the number is still stored in its original format. Is there anway i can chop off the unwanted digits forever so that they are not stored in the table anymore.
View RepliesI 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.
Hi,
As far as I know, the MDB's doesn't shrink when you delete
posts. Is there a way to remove all the "deleted" posts in
your database, e.g. a script ?
Any pointers will be greatly appriciated.
Thanks in advance
Rost
We are shortly going to start using tablet PCs over a GPRS bearer circuit for some of our employees to update our core business system from within customers homes. To do this we have purchased a very streamlined version of our core system, designed to work at a reasonable speed even over a normal GSM connection.
The employees that will be using this also use an access database that I've created though. Whilst it isn't crucial that they can use this while they're out and about, it might be useful and it's something I am interested in looking into. In order to make it feasible though, I think I would probably need to dramatically reduce the amount of data transferred between the be and fe due to the high cost of data over GPRS (as well as the speed). So I have a few questions.
1) Am I right in thinking that if I am using ms access as a backend, all the processing is done at the front end? i.e. I run a query on the front end, the whole table is retrieved from the back end and then filtered at the front end?
2) Is there any way to get around this? Or would it require switching the back end to SQL Server or something?
3) Is there any way of measuring how much data (in bytes, kb etc) is being transferred between the front end and back end while running queries, updating records etc.?
I'll probably think of some more questions but this will do for starters. :)
When I create a form in design view and then open it, the form fills the whole area. The Fit To Screen property is set to No, and Auto Resize makes no difference no matter what it is set at.
How can I reduce the form size (so it looks like an Excel form)?
Hi 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 database that uses the SSN as the primary key, but only the last 4 digits of the SSN need to be shown on the reports that are generated. I can't figure out how to use the Left function to extract only the last 4 digits. For example if a SSN were: 123-45-6789 I would want the field to read ***-**-6789, or just 6789 whichever is easiest. I would appreciate any help that could be given.
View 3 Replies View RelatedI need to pull only the last 4 digits of the SSN. How do I do that in my query?
View 1 Replies View RelatedHi there!
I'm bulding this database where the user will have to insert a full phone number. What I would like to know is? if is there any way where Access can pick the first 5 digits of the number to compare with information already imputed in. by example if the user input the number 01942789789, access pick the number 01942 to look for a match? I know I could do with two diferents fields one for the code number and other for the number, but my boss only want one field. Is there anyone that could help me
Thanks
www.joaobeca.co.uk
Open the database – then open the Report: rep_Billing
I have a query that is doing some sort and count functions. The column [TYPE] is sorted then counted. Instead of sorting by the full name can the query sort based off of the first 2 characters?
Example:
On the report the first company is: AMERICAN POSTCARDS
First it separates the colors, which is good.
Then separates the [TYPE] then counts the total amount that color was that type.
However I do not want the full type to be separated out, I want it based on the first 2 characters so that the “N-“ will all stay together as a group.
Final count should say: AMERICAN POSTCARDS
GREEN RA 3
RED N- 14
RED PR 16
RED RA 12
BLUE NC 1
I know I can get this info from the report, but it has to be done at the query level because it is going to be exported into another system at that point. Thanks.
Let me know if I need to make this clearer. Thank you all.
Travis
Hi,
I am writing a very very simple query to identify postal codes from a list of addresses. The table containing the data has a field called POSTAL_CODE.
I need to exclude the last three digits of this postal code so as to only show the first 2,3,4 digits.
Example would be simple by having a query just showing the first few digits like this Left$([POSTAL_CODE],4) for example, however that will not show correctly for SK6 5LP and M2 4DJ and SK12 8LY.
I have either 2, 3, 4 digits I will need. All that is sure is - I do not want to see the last three digits. Identifying the last three is of course simple, but I cannot exclude them. Tried this
Right$([POSTAL_CODE],3) - and calling the field LAST_THREE
and then ([POSTAL_CODE]-[LAST_THREE])
However does not work because these are numbers and letters.
Probably a simple one, can someone please help - perhaps a query that sounds like this : show all but not Right$([POSTAL_CODE],3)
Ta
Hi,
I am writing a very very simple query to identify postal codes from a list of addresses. The table containing the data has a field called POSTAL_CODE.
I need to exclude the last three digits of this postal code so as to only show the first 2,3,4 digits.
Example would be simple by having a query just showing the first few digits like this Left$([POSTAL_CODE],4) for example, however that will not show correctly for SK6 5LP and M2 4DJ and SK12 8LY.
I have either 2, 3, 4 digits I will need. All that is sure is - I do not want to see the last three digits. Identifying the last three is of course simple, but I cannot exclude them. Tried this
Right$([POSTAL_CODE],3) - and calling the field LAST_THREE
and then ([POSTAL_CODE]-[LAST_THREE])
However does not work because these are numbers and letters.
Probably a simple one, can someone please help - perhaps a query that sounds like this : show all but not Right$([POSTAL_CODE],3)
Ta
Hello, I'm trying to use wildcards to filter for records that contain a date somewhere within the string.
I'm using Like "*#.##.##*" which works for dates that have a day of the months >10, but what about those under?
Well, Like "*#.#.##*" only works for those with the day of month <10. Is there a wildcard character for "one or more digits"?
Also, the above example excludes Oct. - Dec. for the same reason.
help!
Thanks,
Gifford
I am having massive problems with data – I have imported data only to find out that Access has not imported all of it as there is a max of 255 fields, and I have loads more!!
I think I can get round this by importing without the delimited or fixed widths into a memo field but this gives me another problem, the the first 12 digits are the ‘Ref_No’
So I have a table called tblCustMemo that I have imported, and I have 3 fields, ID (Primary Key), Ref_No (new and totally empty) and Memo.
How can I put the first 12 characters from the memo field into the Ref_No field?
Then remove the first 13 characters from the memo field (there is a space after the Customers reference number)
I presume I would do this by update queries, but I am currently at a loss!!
I tried this, just to get the first 13 characters then was going to try update query, but this only returned 7 records out of 8687!!
SELECT Custmemo_TEST.ID, Custmemo_TEST.Ref_No, Custmemo_TEST.Memo
FROM Custmemo_TEST
WHERE (((Custmemo_TEST.Memo)=Left([Custmemo_TEST]![Memo],13)));
I hope someone can help me. I have a report that prints out with the customers social security number on it. I need to change it so that only the last four digits of the SSN show, so it would like something like this XXX-XX-1234. I still want it to keep the full SSN in my Table though so we can search for the customers info with the SSN.
Is there a way that I can change it on the report only and not mess up any other forms that I have it on? Any help would be awesome!
i need to total some data, but only if the code contains 4 digits.
by this i mean.
Code --> A101 ----> Occur 35
Code --> A101A ----> Occur 1
Code --> A101B ----> Occur 5
Code --> A101C ----> Occur 3
Code --> A101D ----> Occur 5
Code --> A101E ----> Occur 5
Code --> A101F ----> Occur 5
the total i need to be displayed is 24....
you will see that the first code only has 3 digits in the code...
Can this be done? if so how please
have a query with a field that contains numbers with 3,4,and 5 digits. I want to format it so it displays all as 5 digits by adding 0s to the front of the number. EX:
324
4856
45634
change to:
00324
-4856
45634
Help please. Dont want to do it in VBA either. I would rather build it as an expression field or something.
I'm trying to export from a text file. However one of the fields contains 2 items of data, ie the code and name of a person.
eg 'po2 Paul O Mahony'. What I want to do is query only the code(first 3 digits) from this field. ie 'po2'. Is there a way of doing this within a query (sql) or do I have to use vbscript to solve this?
thanks
how can i limit an unbounded textbox so only digits from 0-9 will be entered
View 4 Replies View Relatedmake sum of digits from a string, something like if i have txt1 = "1234567890" to make txt2 = "1+2+3+4+ ...+0".
View 13 Replies View RelatedI am currently working on an Access 2010 DB and as of now only one user has access to it and the user is wanting to be able to distribute it to other Managers for them to look at. The problem I am running into is I need a way to hide the first 5 digits of the SSN so the Managers are not able to see the entire number. How to do this? I've tried a combination of different formats, but none have been successful.
View 4 Replies View Relateddear frnd,
i have a access program for printing cheque. i want to use a text box which automaticaly convert number ito digits from user input text box.
Hi all,
here's me again,
I have a question, I'm trying to generate an autonumber but of 4 digits, for example
the first one is: 0001 and autonumber 100 is: 0100, and so on
how can I do that?
any help will e very much appreciated!
Best Regards
CS.
I am trying to hide the first 6 digits in a textbox and only show the last for digits in a text box.
So far I a putting my code in the Format Format section in Property Sheet of a form. Its not seeming to work.
Code:
Left([Card Number],Len([Card Number])-6)
I have the below function that works correctly. However, I would like the "Last_Nbr_Assigned" value to start at 0000 and increment by 1. So the very first number assigned would be 0001, then 0002, then 0003, and so on.
This number is automatically assigned so I haven't had luck with the validation properties. And if manually enter 0001 for the "Last_Nbr_Assigned", it will automatically cut off the 3 zeros, leaving me with 2 instead of the desired 0002.
Code:
Function NewRF() As String
Dim startRange_Var As Long, lastNum_Var As Long
On Error GoTo Err_Execute
'Retrieve last number assigned for RF
groupname_Var = Nz(DLookup("Group_Name", "Codes"), 0)
[Code] ....
I have a 9 digit ID that is inputted on a form and stored in a table but the 0 keeps getting dropped.
I am using an input mask similar to another db but it is not working for this one
mask I am using is 000000000;0;_
So for ID's that start with 0 (EX:012345678) would display and stored as 12345678 instead...