Actually it’s now an Access question.
Is it now a “Resolved” access question, or no?
Easier than running a bunch of update queries, just do it all in a select query and then export the output.
Text-type fields will automatically left-justify; IIUI your problem is numeric fields, which you need right-justified.
Open a query, add your table, add each column to the query output. For each column you need formatted, change the Field: expression from ‘Field1’ to ‘Format([Field1],“00000”)’ (with however many digits you want your numeric field to be).
Value “11” in Field1 will export as “00011”.
Also, when you’ve got your export widths all set up in the wizard, click the Advanced button and it will allow you to save your export specifications. Next export, click the Advanced button and select those specs again. You can use the same specs on any table or query that has matching column names.
Does that help?
Well, I’m the “Ask the Excel Guy”, and here’s how I do it without bothering with Access.
I use a function I wrote called “pad”. Here’s the function:
Function pad(mytext, length, character, lrc)
Dim myformat As Variant
Dim thetext As String
Dim spacecount As Double
If Len(mytext) = 0 Then
pad = Application.Rept(character, length)
Exit Function
Else
myformat = mytext.Cells(1).NumberFormat
thetext = Application.Text(mytext, myformat)
spacecount = length - Len(thetext)
If spacecount <= 0 Then
pad = Left(thetext, length)
Else
If thetext = "" Then
pad = Application.Rept(character, spacecount)
Else
Select Case lrc
Case 1
pad = Application.Rept(character, spacecount) & thetext
Case 2
pad = thetext & Application.Rept(character, spacecount)
Case 3
pad = Application.Rept(character, spacecount / 2) & thetext & Application.WorksheetFunction.Rept(character, spacecount / 2)
End Select
End If
End If
End If
End Function
What this function does is pads “mytext” to the length “length” using the character “character”, padding on the left (that is to say, adding spaces to the left), right, or center (indicated by 1, 2, or 3).
It is used like this:
=pad(C14,12,"_",1)
This pads cell C14 to a total length of 12, using the underscore character, padding on the left. So if Cell C14 contains say “123456”, the result would be “______123456”. Note that the number format is maintained.
So. First pad the cells in each column to the proper length. I typically do it, column by column, to the right of the data. Often, I’ll put the column width in as a variable above each column. In the example, I might put it in cell C11 and then use
=pad(C14,C$11," ",1)
That makes the total column width easier to change if later on I need it to be changed. Replicate this formula to the right for as many columns as needed. Note that I’m using a space (" ") to do the padding.
Last step. Combine all of the columns in a given row into one cell. Say the padded data starts in H14, somewhere off to the right I put in the formula
= H14 & I14 & J14 … etc
Replicate the formulas down, copy the final column, and paste it into a word processor.
Questions? Askem … what’s clear to me may not be clear to you, my writing is not always of the best.
w.
Years ago I could open an Access page someone wrote with selectable boxes that allowed me to change stuff. But this was years ago, and I’ve found I’ve forgotten what very little I knew. I can import a file and export it, with the justification issue I’ve mentioned, but that’s it. I understand that a ‘query’ is a way of accessing and displaying stuff that’s in the database, but I’ve no idea how to write one, so…
The table is the file I’ve imported/opened. How do I open a query and do what you said? I fear I’ll need step-by-step, click-here-and-then-do-this instructions. I get the concept, but I don’t know how to execute it.
intention: As you can see, your solution is above me at this stage.
I can do just about anything I want with the file with Easytrieve. Access is just a means to get the file in a format Easytrieve can use. But if I have to use Access anyway, I may as well see if I can use it to save myself some coding on the backend.
Another question: Usually the city, state and ZIP information are in their own columns (good). Sometimes they’re not (not preferred). And in the latter case, they’re often inconsistent. For example:
San Diego, CA 92111
Los Angeles CA 90034-4950
Lancaster CA 93536
Bellingham, WA, 98225
Notice that some cities have two words, sometimes there are commas, and sometimes there aren’t commas. Can Access sort those out? With Easytrieve I can parse from the end of the field. If a byte numeric, then it’s a ZIP code. If it’s a dash, then there should be another numeric coming and that would be the ZIP code. After the last (or first, as it were) numeric there will be the state abbreviation. Simple enough. Write out the two characters. Account for a space and/or comma, and the rest should be the city. Lots of coding though, and I just don’t like the idea of parsing fields byte-by-byte. Just seems inefficient. So if Access can fix it that’d be handy-dander.
Thanks for the answers so far.