Can I "zero fill" a field result 10

[dieselBREATH]

In Access 2000, I have a field that results in both alpha characters and numeric. Ex:

erf55667.77
345676
2345.66
jfhhh334343

How to keep only the numbers, make the field a fixed length and return zeros where there are no numbers? Some refer to this as zero-fill.

Thanks for anyones help

 

[tlbroadbent]

Create this function in a VBA module. You can then use the function in a query.

Public Function RemoveNonNumericCharacters(sInput As String)
Dim sOutput As String, sChar As String, ix As Integer
sChar = ""
For ix = 1 To Len(sInput)
sChar = Mid(sInput, ix, 1)
If sChar >= &quot;0&quot; And sChar <= &quot;9&quot; Then
sOutput = sOutput & sChar
End If
Next ix
RemoveNonNumericCharacters = Format(sOutput, &quot;00000000&quot
End Function

Select RemoveNonNumericCharacters(field1) As NumField
From tbl Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[MichaelRed]

Terry, I 'read' the problem slightly differently. I think they are asking for a one-to-one substitution of any non-numeric char with a Zero. I THINK this would even include the &quot;dot&quot; (a.k.a. decimal point).

My Soloution (adapted from Terry's)

Public Function basRemNonNums(sInput As String)

    Dim sOutput As String
    Dim sChar As String
    Dim Idx As Integer

    sChar = ""

    For Idx = 1 To Len(sInput)
        sChar = Mid(sInput, Idx, 1)

        If (IsNumeric(sChar)) Then
            sOutput = sOutput & sChar
         Else
            sOutput = sOutput & "0"
        End If

    Next Idx
    
    basRemNonNums = sOutput
End Function

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[dieselBREATH]

ok, sorry to show my ignorance, but Is VBA Visual Basic editor? What is a function. And what do you mean by:

Select RemoveNonNumericCharacters(field1) As NumField
From tbl

Thanks!

Bill

 

[MichaelRed]

Yes. and he means this is the SQL statement of the Query which will show / display the proposed transformation (It DOES NOT make a permanant CHANGE to the data in the table, it JUST displays it differently).

How did you get the handle?

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

Is VBA Visual Basic editor? VBA means &quot;Visual Basic for Applications&quot; You access it through the VB Editor.

What is a function? A named sequence of statements executed as a unit. A VB procedure that can return a value.

Open the VB Editor and create a new module or open an existing module. Copy the function - mine or Michael's - that meets your need.

And what do you mean by:

Select RemoveNonNumericCharacters(field1) As NumField
From tbl

This is a select query. It calls the function to convert the contents of the field in the table. It doesn't make permanent changes. It only displays the changed field. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[dieselBREATH]

Sorry guys, but to say I am a begginer is an understatement.
I REALLY appreciate your guys help, but I need some more info, like &quot;go here, click this, pick this, do that&quot;
just something a little more specific if you could.

Sorry, but grateful for your help.

Bill

 

[MichaelRed]

No MORE help untill I understand 'dieselBREATH'!

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[dieselBREATH]

ok, sorry.

I own a big white Ford 4X4, 4 door powerstroke DIESEL engine. And my favorite saying is &quot;I love the smell of diesel in the morning!&quot;

Now, will you hold my hand with this little project? You see, I have to extract data from a DB and export it as a coma delimited text file in a format that the time and billing software will import and like. Un fortunately this requires some data manipulation. I would also like your advise on where would be a good place to start learning SQL queries and VB.

Thanks!

 

[MichaelRed]

Hmmmmmmmmmmm,

&quot;Point and Click&quot;.

Open up the visual basic editor. A way to do this is to click on the &quot;Modules&quot; label / button in the database window. THAT is the one which has lists of the various thinnnggggyyyyyyys (Tables | Queries | Forms | Reports | Macros | Modules). Then click on where is says &quot;new&quot;. Ipso Presto a new (Mostly Blank) screen/ window Appears.

Now, come back to Tek-Tips and select the proceuder you want to use (Terry's or Mine). Use the Standard Windows Block stuff to &quot;Highlight&quot; the part from:

Public Function ...

down to


End Function

Don't worry much if you get an extra line or so, you can just delete them later.


Play &quot;Switch&quot; (AGAIN!), back to the Vb Editor window opened earlier.

Place the &quot;cursor&quot; anywhere in the window and just do the Win Paste operation. The procedure should appear, with (hopefully) better formatting than in the Tek-Tips windows.

Congradlations. You hae just created a &quot;Module&quot; with a Procedure in Visual Basic.

Now, you need to Save it. That is the little floppy disc icon on the tool bar. Click it and follow the signs. Give it a NAME which you can rember / relate to (DieselBreathModule ?)

SElect the database Window again. Select Queries. Select New. A split window appears with a Tables/queries/both dialog box in front of it. ASSUMIING that your ugly strings are in a table, these are preselected. So, scroll down until you find the table with the ugly string stuff and DOUBLE CLICK it. Click close on the DIALOG BOX. It (Mysteriiously?) vanishes and you are looking at the split window. the top is the 'battleship grey' while the bottom looks like (and actually is!) a grid. Your table (with the funny strings) is in the top, with the fields listed (You may need to scroll through the list to find the field). Find the field and DOUBLE Click it. Amazingly, the field appears in the grid (First Column Top Row)! For the exampe, I'll call it FieldX. When you do the following, this (FieldX) refers to the field name you selected.

Place the cursor in the grid in the same square as FieldX. If the Filed name is &quot;selected&quot;, press F2 (Function Key 2). the field should be in &quot;edit&quot; mode. Hit the home key. the cursor should be at the LEFT of FieldX. If it is, type &quot;NewFmt: basRemNonNums([&quot; Hit the &quot;End&quot; key. Type &quot;])&quot;

The cell should read:

NewFmt: basRemNonNums([FieldX]) Asuming you were BRAVE and selected NY version. Otherwise, substitute Terry's procedure name.

&quot;Run&quot; the query. Either Click on the &quot;Bang&quot; (Exclamation sign on the menu bar) or click on the &quot;grid&quot; symbol on the Tool Bar.

If it doesn't work. Take two ???? and post in the AM. I'm out of here for the night!!!
MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[dieselBREATH]

Michael,

You are very kind and I like your humor. Thanks for being patient with me! IT WORKED!!!!!!!!!!

Now, I am happy and sad at the same time. I am happy it worked, but I have no idea how you do it. This is something that I will need to learn....quick. &quot;This&quot; meaning data manipulation within an access database.

I would appreciate you thoughts.

Many thanks!!!!

Bill

 

[tlbroadbent]

Michael,

I'm impressed. I've not seen so much patience on your part You deserve a few stars for your efforts. As usual your answers are good but the extra effort really stands out. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[JoeMiller]

I added one because I thought his tutorial was hilarious! Joe Miller
joe.miller@flotech.net

 

[MichaelRed]

all kudos and JOB offers cheerfully accepted!

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[THoey]

Make it a third star for all of the above reasons. ;-)

Terry, just to let you know, this is Michael's normal style of posts. He has just been a little ragged lately trying to find a job on the east coast. Anyone know of one, be sure to let him know... Terry M. Hoey

Please read the following FAQ to learn how to help me help you...

faq183-874

 

[JoeMiller]

Where on the East Coast Michael? I'm in CT and occasionally have a side project that I can't get to myself, and I would definitely have no problems with someone of your ability writing db's. Email me under separate cover if you prefer...

Joe Miller
joe.miller@flotech.net

 

[dieselBREATH]

Wow, I never thought such a conversation would start from such little thread. I would have to say that I was a little surprised to see Michael's &quot;step by step&quot; instructions when I got up this morning but am truly greatful. I feel I must let everyone know here, that I am not stupid, I just have &quot;0&quot; DB and VB experience. I specialize in small office networks and small office solutions such as internet access for the whole office and VPN's etc. Small office's usually don't have DB needs and if they do, they don't come to me. But, now I have been refered to a larger client and like most people in the IT field: &quot;If I don't know how to do it, I'll learn or find someone who does!&quot;

I owe you lunch Michael. Let me know where to send the money.

DB ( DieselBreath, not Data Base )

 

[MichaelRed]

Not really expecting lunch, but the address is:

Michael Red
10201 Bradley Lane
Columbia, MD 21044
410.997.0588


just in case your'e in the area MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[THoey]

Michael, if you happen to find a job due to this thread, I want my share of the finders fee... Terry M. Hoey

 

[MichaelRed]

Michael, if you happen to find a job due to this thread, I want my share of the finders fee...
Terry M. Hoey

Hmmmmmmmmmmmmm,

At the going 'rate' of employment opportunities through the Public here. the &quot;if&quot; would fill the entire screen, be at least ten fold bolded, italacised, and have a rainbow of colors. All Attributes of course would be blikning/fading in and out ...

but i'll be sure to 'keep uou in mind' --- at least till starvation sets in.



MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over