Solved

Access 2010  form add a space after every 8th Character

Posted on 2014-09-21
16
272 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 26

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 26

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
 

Author Comment

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

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 26

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

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

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 26

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 26

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now