• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

access query insert a number of spaces

Hi!

I have an access query, and I want to modify a text field as follows:
The query should add a certain number of spaces to the beginning of the field. How many? It'll calculate that, based on the length of another field.

Is this possible?

Note: the reason for this funny request is that it's for a report, where I will show two fields, and I want them to line up one after the other, but the first will vary in size.

Thanks!
0
etech0
Asked:
etech0
  • 5
  • 3
  • 2
1 Solution
 
mbizupCommented:
You can add spaces like this:

UPDATE YourTable
SET YourField = Space(5) & YourField

Open in new window



If you want to make your field 20 characters in length (just as an example):


UPDATE YourTable
SET YourField = Space(20 - Len(YourField)) & YourField

Open in new window

0
 
IrogSintaCommented:
In your report you can just add a textbox with something like this in its ControlSource:
=[NameOfField1] & " " & [NameOfField2]

In the sample above, Field2 will come right after Field1 with a space in between them.
0
 
etech0Author Commented:
@mbizup: Great! I didn't know about that Space function.

@IrogSinta: The reason I didn't do it that way is because the two fields need to have different fonts. But thanks for the suggestion.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
IrogSintaCommented:
That makes sense.  One thing I forgot to mention, is that if ever you see mbizup and myself posting answers on the same question and in the end you prefer her answer, she and I have an agreement that I can have her points.
0
 
IrogSintaCommented:
I'm just kidding about that last post, of course.
 :-)
0
 
etech0Author Commented:
The real question is: does she get the points when your answer is preferred?
0
 
IrogSintaCommented:
Yes she would of course.  But afterwards, she would generously pass them on to me.
:-)
0
 
etech0Author Commented:
...and you would generously pass them back. Or to me???
0
 
mbizupCommented:
Lol!   Ron- when I accumulate enough points I will cash them in and buy you a drink.
0
 
IrogSintaCommented:
:-)
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now