Queries - Right justify

How do I right justify? The field is 7 characters long and I've tried this but it no longer brings me any data in fields I've previously had data.

25: Space(7-Len(CLng(Int([rev.250])+Int([rev.255])+Int([rev.258]))))

well first the line you posted would simply return spaces.

25: Space(7-Len(CLng(Int([rev.250])+Int([rev.255])+Int([rev.258]))))

It would need to be modified to return the actual value:

25: Space(7-Len(CLng(Int([rev.250])+Int([rev.255])+Int([rev.258])))) & CLng(Int([rev.250])+Int([rev.255])+Int([rev.258]))

The simpler way to do this would be:
Right(Space(7) & CLng(Int([rev.250])+Int([rev.255])+Int([rev.258])), 7)

Or you could probably use:
Format(CLng(Int([rev.250])+Int([rev.255])+Int([rev.258])), "<7spaces>")

I'm close. I've tried all of these and they all center the value, not justify it to the right. What am I missing?

Ummmm how are you displaying it?

It sounds like the textbox is wider than the 7 characters and it isn't set to right justify.

I'm doing it directly in the query so there is no textbox. I'm new so I apologize if that's a stupid response. I can't understand why if I export it to a table and make the text right justified in the properties box for that field, why it won't keep it when I export it.

I know of no way to format the data in a query.

You can change the width of the field being displayed so that it would appear to be right justified or you can add more spaces to the front, again so it appears to be righ justified.

However, if you want to actually format the results of a query you'll have to put it into a form or report.

You probably would need to use a monospace font as well. Otherwise, it will never right-justify.

ok, thanks

ummm, may I make a suggestion.... convert those numbers to strings using the str() function you do the len() around the string function:

25: Space(7-Len(trim(str(Int([rev.250])+Int([rev.255])+Int([rev.258]))))) & trim(str(Int([rev.250])+Int([rev.255])+Int([rev.258])))

Now you may need to increase the spaces from 7 for the query view, or shrink the size of the datasheet column in your query view. I think courier font will also work as well as monotype.

I tried that and it centered it rather than right justified it. I'm using the query to create a table then I export the table to a .txt file and ship it off to the state. I tried going into the properties of the field on the table and left justify it there but it loses it during the export to .txt file. Is it just not doable?

Justification (right, left, or center) are display properties and have little to do with the data itself. The exception being needing to left pad a number or string due to fixed width fields in a .txt file.

Tables are for storing data and queries are for retrieving data. Forms and reports are used to display data.

What you want to do is very doable in a form or report becuase that is what they are for, displaying data. You can view the data in a table, but that is not their (tables) primary function.

By padding the field with spaces you've already 'right justified' the data for a fixed field length.

If you want a display that can be right justified and looks like a table view I'd suggest you create a datasheet form and view that data through it.

I can export the datasheet form to a .txt file and it'll keep the formatting?

is the data numbers? If it's only numbers then you can do a val([yourfield]).

Just for your information, queries aren't designed to give your formatted output, so to speak, like forms, reports, or datapages can.

In queries, text is left justified and numbers/dates are right justified. No way around it.

If you want something that looks like a query (datasheet view), create a form using that query with the wizard, choose datasheet type, and then when you go into design view of the form, for each of the fields, go to properties, format tab, text align, and choose right.

Will it keep the alignment if I export it to a .txt file?