Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-16-2018, 11:51 AM   #1
sebekkg
Newly Registered User
 
Join Date: Apr 2017
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
sebekkg is on a distinguished road
Concatenation

Hello guys can you help me on one issue.

For example i have some text fields that i want to represent differently.

They are like this example z564640 54331 but i want them to be 8 in length and if they are shorter it needs to add one or more 0 in front

So from z564640 to 0z564640 and 54331 would be 00054331.

Thanks in advance.

Sent from my SM-N9005 using Tapatalk

sebekkg is offline   Reply With Quote
Old 05-16-2018, 12:33 PM   #2
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 732
Thanks: 0
Thanked 169 Times in 169 Posts
June7 will become famous soon enough
Re: Concatenation

Formatting a number to prefix with 0's is simple with: Format(54331, "00000000")

However, throwing in alphas fails.

Will there always be 7 characters when an alpha is present? Maybe:

0 & Format([Fieldname], "0000000")

Manipulating text requires consistency in structure. If it gets much more complicated (inconsistent structure), build a custom function.
June7 is offline   Reply With Quote
Old 05-16-2018, 12:50 PM   #3
sebekkg
Newly Registered User
 
Join Date: Apr 2017
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
sebekkg is on a distinguished road
Re: Concatenation

It is numeric or alfa numeric. "Z" in front or "hz" in front.

Sent from my SM-N9005 using Tapatalk

sebekkg is offline   Reply With Quote
Old 05-16-2018, 01:07 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,401
Thanks: 13
Thanked 1,416 Times in 1,350 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Concatenation

I would create a function because the IIf() will get pretty complex. If you prefer an IIf(), you can convert the logic below. The Space() function returns the number of spaces specified by its argument. There is no function that returns a string of 0's or other characters. So, after you comcatemate the spaces, convert the spaces to 0's.

Code:
Public Function Add0Prefix(InputVal as variant) as String
    Dim InLength As Int
    Dim WorkVal As String
    InLength = Len(InputVal)
    If InLength < 0 Then
        WorkVal = Space(8 - InLength) & InputVal
        WorkVal = Replace(WorkVal, " ", "0")
        Add0Prefix = WorkVal
    Else
        Add0Prefix = InputVal
    End If
End Function
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
sebekkg (05-16-2018)
Old 05-16-2018, 01:20 PM   #5
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 732
Thanks: 0
Thanked 169 Times in 169 Posts
June7 will become famous soon enough
Re: Concatenation

Actually, there is. Just remembered some code I have used for this.

Let x represent your field.

String(8-Len(x), "0") & x
June7 is offline   Reply With Quote
Old 05-16-2018, 07:57 PM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,401
Thanks: 13
Thanked 1,416 Times in 1,350 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Concatenation

Thanks, I must have been looking at an outdated list.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 05-16-2018, 08:35 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,307
Thanks: 62
Thanked 1,150 Times in 1,051 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Concatenation

Let's say that the magic number of characters you need is represented by MN and the input string is INPS and the output string is OUTPS

Code:
OUTPS = Right( ( String( MN, "0" ) & Trim$( INPS ) ), MN )
This should work correctly even if INPS is null or an empty string or a sequence of blanks. I added a technically unneeded pair of parentheses just to clarify the expression grouping.


__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenation Mitsuki General 2 10-01-2006 02:25 AM
Concatenation Pharcyde General 6 02-13-2004 12:51 PM
Concatenation Nenya Reports 4 07-22-2003 07:03 AM
Concatenation I think? Oscar Queries 4 03-28-2003 01:16 AM
[SOLVED] Concatenation Mark E Queries 2 10-07-2001 05:26 AM




All times are GMT -8. The time now is 07:47 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World