Solved

Access 2010  form add a space after every 8th Character

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

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
subtract 1 in Access 2003 query 7 37
Sum Multiple Columns in Access Query 5 48
Sub Reports 8 21
Prevent use of Microsoft Office application 9 53
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

919 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

16 Experts available now in Live!

Get 1:1 Help Now