string functions

I have a table with a column called qty that has numbers like this 3245.1823 I want to use an update query to get rid of the decimal place and everything past it. Next, I want to make all of the numbers 7 digits by adding leading zeros. The numbers are not the same lenght. What is the best way to accomplish this?

On Mon, 7 Jun 2004 12:26:11 -0700, Chris Moore wrote:

I have a table with a column called qty that has numbers like this
3245.1823 I want to use an update query to get rid of the decimal
place and everything past it. Next, I want to make all of the
numbers 7 digits by adding leading zeros. The numbers are not the
same lenght. What is the best way to accomplish this?

Splitting the field is easy.
Update YourTable Set YourTable.[NewField] =Fix([Qty]);

Why do you need leading zeros in a number field?
Access does not store leading zeros. If the field name is [Qty] I
assume you will need that value as a number for calculations, so
storing it as text (which does store leading zeros) would be counter
productive.

Store the value without the leading zeros.
Whenever you need to display the field, set that control's Format
property to
0000000
or use =Format([NewField],"0000000")
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Hi,

Take a look at:

ACC2000: How to Pad Character Strings on the Left or Right Sides
http://support.microsoft.com/?kbid=210573

- and -

the CInt function within Microsoft Visual Basic for Applications help
topics.

Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
http://www.microsoft.com/security/security_bulletins/ms03-026.asp and/or
to visit Windows Update at http://windowsupdate.microsoft.com/ to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights


--------------------
| Thread-Topic: string functions
| thread-index: AcRMxUq0lKlLeqJ5TgmWVdliF9+Ymg==
| X-WN-Post: microsoft.public.access.forms
| From: "=?Utf-8?B?Q2hyaXMgTW9vcmU=?="
| Subject: string functions
| Date: Mon, 7 Jun 2004 12:26:11 -0700
| Lines: 1
| Message-ID:
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.access.forms
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.forms:268998
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.access.forms
|
| I have a table with a column called qty that has numbers like this
3245.1823 I want to use an update query to get rid of the decimal place and
everything past it. Next, I want to make all of the numbers 7 digits by
adding leading zeros. The numbers are not the same lenght. What is the best
way to accomplish this?
|