CODEWITHSUNDEEP
sqlms-access
Rchee
Rchee

Reputation: 113

Limit a record character length in Access query

I have the following query in Access:

UCase(TRIM([field1]+" "+[field2]+" "+RIGHT([field3],3) AS TEST

The results look something like this:

"TEST: RICHARD ROBERT APPLESEED 112233 789"

What I'm wanting to do is limit the length of the first field (field1) within this query to 19 characters and if the name goes over then it starts trimming from the right side like so:

"TEST: RICHARD ROBERT APPL 112233 789"

There are records that don't go over the 19 characters and those should remain the same but for the ones that do they need to stay at 19.

Thanks in advance!

Upvotes: 0

Views: 1144

Answers (2)

FiercestJim
FiercestJim

Reputation: 308

You can use SUBSTR :

UCase(SUBSTR(TRIM([field1],0,19) & " " & [field2] & " " & RIGHT([field3], 3), 19) AS TEST

EDIT:

It appears Access doesn't have SUBSTR....... sorry!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Just use left():

LEFT(UCase(TRIM([field1] & " " & [field2] & " " & RIGHT([field3], 3), 19) AS TEST

Upvotes: 4

Related Questions