?
Solved

Access 2010  form add a space after every 8th Character

Posted on 2014-09-21
16
Medium Priority
?
282 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 2000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 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