Solved

Access 2010  form add a space after every 8th Character

Posted on 2014-09-21
16
280 Views
Last Modified: 2014-09-21
I have an access form with a field that I would like to put a space after every 8th Character. Thanks
0
Comment
Question by:shieldsco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 
LVL 27

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 40335494
You can use this small function:
Function Demo(strInput As String) As String
    Dim i As Integer
    For i = 1 To Len(strInput)
        Demo = Demo & IIf(i Mod 8 = 0, Mid(strInput, i, 1) & " ", Mid(strInput, i, 1))
    Next
End Function

Open in new window

0
 

Author Comment

by:shieldsco
ID: 40335550
Proprietary_Debit: Space(8) - It puts 8 in the field. Example of data in the field :252513458899661212345678 -- expected result:25251345 88996612 1212345678. Notice the space
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40335560
If we take your sample number "252513458899661212345678" and add a space every 8 characters this is the string you will get "25251345 88996612 12345678" (which is the result the aforementioned function returns) not 25251345 88996612 1212345678 in which the last group has 10 chars.

Original: 252513458899661212345678
Added space every 8th char: 25251345 88996612 12345678
New expected result: 25251345 88996612 1212345678
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:shieldsco
ID: 40335568
can you execute it on a query -
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40335575
Sure.

YourFieldAlias: Demo(YourField)
0
 

Author Closing Comment

by:shieldsco
ID: 40335583
Thanks
0
 

Author Comment

by:shieldsco
ID: 40335587
I get an error if the value in the field is null or blank
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40335597
Of course you do, the function tries to process a string of text that doesn't exist! You have to check for null\blank (aka zero length strings)\less than 8 chars.
0
 

Author Comment

by:shieldsco
ID: 40335599
How would I do that
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40335600
What do you expect to happen if one of the above problems occur?
0
 

Author Comment

by:shieldsco
ID: 40335607
If it's blank then it should remain blank-- I tried the code below and it seems to work
Proprietary_Debit: IIf([tblAccounting Transactions]![Proprietary_Debit] Is Null Or " ",Null,Space([tblAccounting Transactions]![Budgetary_Debit]))
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40335611
Try this:
Proprietary_Debit: IIf(IsNull([tblAccounting Transactions]![Proprietary_Debit]) Or [tblAccounting Transactions]![Proprietary_Debit]="","",Space([tblAccounting Transactions]![Budgetary_Debit]))

Open in new window

0
 

Author Comment

by:shieldsco
ID: 40335627
I still can an error value
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 40335631
When?
0
 

Author Comment

by:shieldsco
ID: 40335638
It actually returns a null value for all records
Proprietary_Debit: IIf(IsNull([tblAccounting Transactions]![Proprietary_Debit]) Or [tblAccounting Transactions]![Proprietary_Debit]="","",Space([tblAccounting Transactions]![Budgetary_Debit]))
0
 

Author Comment

by:shieldsco
ID: 40335656
Thanks I figured it out --- good job
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question