?
Solved

Access 2010  form add a space after every 8th Character

Posted on 2014-09-21
16
Medium Priority
?
289 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
  • 9
  • 7
16 Comments
 
LVL 28

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 28

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
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!

 

Author Comment

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

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 28

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 28

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 28

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 28

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Implementing simple internal controls in the Microsoft Access application.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

609 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