Access 97 Export Spec's 1

[swaimdw]

I have created an Export Spec in Access 97 to export the results of a query as a text file. Two of the fields are data type number which fluctuate in size from 0 to 100. The problem is that the "DoCmd.TransferText acExportFixed" command writes these two fields as if left justified. The Spec is in Fixed Width format due to the customers requirements. The problem is that these two number fields do not line up right justified as number columns should and the customer is unhappy. So far I have been unable to force the transfer to use the formatting being used in the database, which is aligning fine. Any suggestions?
Thanks
Darrell

 

[RickSpr]

Instead of exporting the field directly from the table, export from a query. In the query, select each of the fields you want to export, except the fields you want right justified. For those, set the Field to an expression using the Format() function concatenated to spaces.

For example, if you have a Single that you want right-justified with 2 decimal places in a 15-byte field, you would set the query's Field for this column to something like this:

Right$(Space$(15) & Format$([Fieldname],"-###,###,###.##"),15)

Naturally, you can alter the format string to whatever you need.
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[swaimdw]

Well, that sounds good, but I don't see how to add this peice to my puzzle. I am already exporting from a query. Where does the field expression you mentioned go in the query? It is not accepted in the Format Field Properties of the fields in question. The VB Code I am using to transport the query data to a text file is: "DoCmd.TransferText acExportFixed, "ExportSpec", strQname, "C/Academy_Archive_File"
I am about to attempt to write a new module to read the text file and alter manually the two fields to appear right justified in the finished product. This just seems the long way around the barn. I have had problems in the past writing advanced code for Access 97 so even this may not work. If I were allowed to write this in C, no problem. Any other suggestions?
Thanks
Darrell

"The Early Bird gets the worm,
But the second mouse gets the cheese."

 

[RickSpr]

The expression goes on the Field: line of a new column in the query grid. You usually put a column name there, but any SQL expression resulting in a single value is allowed.

When you use an expression, Access automatically assigns a name. The first such column will be named Expr1, so that the grid cell winds up looking like:
Expr1: Right$(Space$(15) & Format$([Fieldname],"-###,###,###.##&quot,15)
In the SQL statement, it equates to this in the list of columns following the SELECT keyword:
(Right$(Space$(15) & Format$([Fieldname],"-###,###,###.##&quot,15)) As Expr1

You can give and expression a name of your own choosing by typing your chosen name followed by ":" before the expression. However, the name you give it must not match the name of any existing column. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[swaimdw]

Ohhh, that's something new. I admit my Access knowledge base is a little short. Inserting code directly into the Field name is great...but what does it mean? Is there some documentation I can read? I need to do some more manipulation with it. For example: the "##.##" is automatically replaced with "#.#" My value is Expr1: Right$(Space$(7) & Format$([Fieldname],"#.#&quot,15))
The text file is better but I need to tweak it a bit.
Thanks for your help
Darrell

 

[lameid]

Office 97 does not by default install the VBA help files for Access 97. Begin by starting office setup and under help files select the VBA help file option. I think that is found under Access in Office 97. Anyway, once you have the help files installed, go into help and lookup the functions used. With a C background, this shouldn't be to difficult to figure out.

Also I noticed you changed the space to be 7 spaces instead of 15... That means you need to change the parameter of the right function to 7 as well...

Right$(Space$(7) & Format$([Fieldname],"-###,###,###.##&quot,7)

The only other thing I have to add is that putting a dollar sign after a function as was done in this example forces the output to be a string and not a variant. Not all functions have a string version.

RickSpr, BTW that's an interesting way to go about it... I would have used

Space$(7 - Len([Format$([Fieldname],"-###,###,###.##&quot)) & Format$([Fieldname],"-###,###,###.##&quot

I have also written a funtion that accomplishes this sort of thing. I use it to put in leading 0's into things I import from Excel which converts my text containing numerics to a number for import. It is overkill here because the space function works nicely.

Function FillLeft(strin As String, strFillString As String, lngFillLengthTo As Long) As String
Dim lngNumberCharactersNeeded As Long
Dim lngCount As Long
Dim strTemp As String
lngNumberCharactersNeeded = lngFillLengthTo - Len(strin)
lngCount = 0
While lngCount < lngNumberCharactersNeeded
lngCount = lngCount + 1
strTemp = strTemp & strFillString
Wend
FillLeft = strTemp & strin
End Function

 

[RickSpr]

swaimdw,
Do lameid's suggestions get you the documentation you need? I don't know whether you're more interested in the VBA documentation or the SQL syntax. Or perhaps it's a surprise to you that you can put VBA functions into an SQL command. If you need further explanation, just ask.

lameid,
I'm an old assembler programmer with some knowledge of how compilers translate statements into machine language. It's an unconscious habit with me to write my high level language code in the way that generates the fastest object code, based on my experience.

Your expression is mathematically a better statement of what you intend, but it might evaluate the Format() function call twice--once to determine the length of the result, and once to get the result string itself. The VB compiler might be sophisticated enough to realize the common expression and optimize the code, but most compilers aren't that clever. The Format() function is very flexible, so it's probably relatively slow. The Space() function should be must faster. Your expression also involves an additional subtraction.

I'm not saying my expression is better; with today's personal computers, speed of the VBA code is often of no concern. Yours has the advantage of perhaps being more easily understandable. It's a question of the coder's priorities. Coding for speed is just my default mode.

A comment on your FillLeft() function: If you need to fill n characters, you're going to do n string concatenation operations. The concatenation itself (copying the characters) is fast, but each time you do one VB has to allocate a temporary string to hold the result, and memory allocation slows it down a little. There's a much faster way:

Function FillLeft(strin As String, strFillString As String, lngFillLengthTo As Long) As String
    Dim lngNumberCharactersNeeded As Long
    lngNumberCharactersNeeded = lngFillLengthTo - Len(strin)
    FillLeft = String$(lngNumberCharactersNeeded, strFillString) & strin
End Function

The String$() function produces exactly the same result as your loop. (Except, that is, if strFillString is longer than one character, but your function doesn't work right if that is the case.) Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[swaimdw]

I appreciate all the help. The final code does exactly what the customer wants…until they change their mind again. I used: Expr1: Right$(7) & Format$([Hours],”0.0”),7) and Expr1: Right$(5) & Format$([Score],”0.0”),5) for the 2 fields. I noticed that these new fields apparently are linked to the corresponding fields within the query itself, not the original tables. I therefore had to create a transitional query in order for the export specification to work properly in the DoCmd. It still feels like I did some traveling to get there, but it works and its not as dirty as writing a ton of VB to read and convert the text file. I have another Access tool I can use and I still feel like a programmer. Thanks again.

 

[lameid]

swaimdw,

Sounds to me like you made a second query to get your fieldnames back to what they should be because you want the same fieldnames as in the original tables. You can do this in the first query by disambigufying your field referneces by fully qualifying them. For Example if you wanted to use Hours as your resultant field name and hours comes from TableX you could use:

Hours: Right$(7) & Format$([TableX].[Hours],”0.0”),7)

Note that TableX does not have to be a table, it could also be query. Sometimes it is necessary to use layered queries but it sounded like it could be avoided here. I don't know that I'd go back and change your existing code... Just thought you could file this under future reference.


RickSpr,

I obviously never ran accross the string function before- thanks. I'm not sure how I missed it. I also noticed that

Space$(7 - Len([Format$([Fieldname],&quot;-###,###,###.##&quot)) & Format$([Fieldname],&quot;-###,###,###.##&quot

would probably call format twice. I would have probably written a function to take a string and number of spaces as arguements if I were using this on a big enough dataset. The I would pass the format result and number of spaces to the new function. Same cat skinned differently. I bet even in this scenario your code is slightly faster being found in compiled dll's as opposed to compiled modules.

My assembly is limitted to a couple courses in school on a Motorolla Chip (I even forget the chip number now). But if I cut my teeth on it I bet I would see the faster execution before the most logically direct method. Hopefully, I'll catch more things like this in the future.

 

[RickSpr]

lameid,

I didn't realize you could reuse the column name by disambiguating the reference--so I learned something from you, too! You get a star. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein