[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problem formatting a string

Posted on 2011-05-04
7
Medium Priority
?
266 Views
Last Modified: 2012-05-11
I am trying to format the following strings using the format function Format( Cstr(Format([ChargeNumber])),"00000000\.00\.00\.00\.000")

Input String                                  Desired Output                 Actual Output
18006350037100000              18006350.03.71.00.000    18006350.03.71.00.000
1800565125710032A              18005651.25.71.00.32A    1800565125710032A

As you can see, the first input string is formatted properly, however the second string is not being formatted.
         
0
Comment
Question by:chtullu135
  • 4
  • 2
7 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 35691210
because it is not numeric...
0
 
LVL 6

Expert Comment

by:cfEngineers
ID: 35691243
try
"00000000\.00\.00\.00\.00#"
or
"00000000\.00\.00\.00\.00_"

trying to remember
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35691253
you can use something like this (not tested)

Mid(ChargeNumber,1,8) & "." & 
Mid(ChargeNumber,9,2) & "." & 
Mid(ChargeNumber,11,2) & "." & 
Mid(ChargeNumber,13,3)
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 35691297
or use this

Format( Cstr(Format("1800565125710032A")),"@@@@@@@@\.@@\.@@\.@@\.@@@")
0
 
LVL 6

Expert Comment

by:cfEngineers
ID: 35691338
ah yes the @ good catch HainKurt
0
 

Author Closing Comment

by:chtullu135
ID: 35691459
Thanks.  That's what I was looking for.  I had checked help in Access 97 but I saw no mention of using @.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35691560
0

Featured Post

Technology Partners: 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 Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

872 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