Empty Spaces Issue

[joesmithf1]

Hi,
I imported an Excel spreadsheet into Access. As you know, Access will automatically create 255 characters size for each 'text' datatype.

Now the issue is, when I create a query and I specify
Right([Field Name],3) I get no/empty results. I know my query syntax is correct, but the issue is, Access is picking up the right 3 character of the 255 field size. But in reality, my field's data are not 255 characters long; the max is probably 30 characters.

So, the question is, how do I go about picking up the 'actual' right 3 characters?

Please advise. Thanks!

Leon

 

[Rabbie]

I have just tested this using an excel worksheet that I access as a linked table.

Right([Field Name],3) gave the correct results regardless of the actual length of the data. In the table design the text fields had a size of 255. The actual data length was between 8 and 25 chars.

I was using A2003. Which version were you using?

 

[neileg]

If somehow the field has been padded with spaces you may get the behaviour you describe. Otherwise I agree with Rabbie. You could use Trim() to remove trailing spaces if this is the issue.

 

[joesmithf1]

Oh...ic....ic. You "link" the table and NOT import the table to get the structure right. THank you all!

Leon

 

[Rabbie]

I am just saying that it shouldn't matter. I just used a linked table because I already had one with the right fields.

Using Niel's suggestion combined with mine
(Right(rtrim([Field Name]),3)) should always give you the correct results.