Define field length for query results

I currently have a table of information in access that I need to query certain fields with.  

The issue is that I can not define a field length for the query's results.
For example:
Lname must have a field length of 30 regardless of use
TermHrs must have a field length of 3 regardless of use

I've tried left(), mid(), right(), substr()...and none of them have worked.  
Jond0312Asked:
Who is Participating?
 
jjafferrConnect With a Mentor Commented:
Do you mean you want the rest of the space to be BLANKS? or Underscores??

you will need a Function to do this,
save the following in a module:



now in your query, call this function, like this:
Lname1: FormatText([Lname], 30)
Public Function FormatText(txt As String, iDigits as integer)
dim NewT, i 
    For i = Len(txt) + 1 To iDigits 
        NewT = NewT & " "   'replace the space between the quotes with whatever you want to fill the rest of the spaces
    Next i
    FormatText = txt & NewT
    
End Function

Open in new window

0
 
darkmooinkCommented:
len()
0
 
jjafferrCommented:
try this:

Lname1: mid([Lname],1,30)
TermHrs1: mid([TermHrs],1,3)

jaffer
0
 
Jond0312Author Commented:
I tried

mid([Lname],1,30) & mid([Fname],1,30)

my results...

DerekKellgren

i need

Derek________________________Kellgren______________________
0
 
jjafferrCommented:
for [Lname]  and  [Fname]

FullName:FormatText([Lname], 30) & " " & FormatText([Fname], 30)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.