Right Function SQL syntax help

[hellfire45]

So I've got this column that I am aliasing as per the following: dbo_Entities.firstname AS [Loan#]

I need two calculated columns based off that column. One that takes the right most character (regardless of if it is a character or digit) and one that takes the right 2 most characters (regardless of if it is a character or digit). Can somebody help me out by writing the bit of SQL so I can see how this is done? I'm really unfamiliar with the SQL in MS Access.

thank you so much!

[amrut]

Try the Right function Right("TheString",2) will return ng and Right("TheString",1) will return g

[hellfire45]

I'm sorry. It's possible I'm a little slow. In the GUI, I've got this written:

L: Right([dbo_entities.firstname],1)

I also tried the following:

L: Right([firstname],1)
L: Right([loan#],1)
L: Right('Firstname',1)

I'm sorry but none of these are working.

[JoeM]

Where are you trying to return this calculation?
In a query, form, report, etc?

If in a Query, can you paste your SQL code for that query here?

[hellfire45]

It's a query in ACCESS. The query is kinda enormous. They've got me pulling back over 130 columns from 12 tables. I guess I can paste a condensed version below. Thanks! The dbo_Entities.firstname AS [Loan#] is actually a 9 digit integer. They have the name in the database as "firstname" but its actual a loan#. The RIGHT function I'm trying to use is on the end.

Thanks again!

SELECT dbo_Entities.EntityID AS [File#], dbo_Entities.MiddleName AS FHLMC, dbo_Entities.firstname AS [Loan#], Right([dbo_entities.firstname],1) AS L

FROM dbo_Entities

[JoeM]

SELECT dbo_Entities.EntityID AS [File#], dbo_Entities.MiddleName AS FHLMC, dbo_Entities.firstname AS [Loan#], Right([dbo_entities.firstname],1) AS L
FROM dbo_Entities

That is valid Access query Syntax.
What happens when you try to run this query?
Are you getting an error message (if so, exactly what does it say),
or are you getting unexpected results (if unexpected results, please provide a few data examples)?

[hellfire45]

It's not giving me an error, it's just giving me nothing. Blank column.

[JoeM]

Sounds like you may have a blank space (or spaces) at the end of your string, so that is what it is returning.
Try this:

Right(Trim([dbo_entities.firstname]),1)

Does that make a difference?

If not, see what this returns:

Len([dbo_entities.firstname])

That should return the length of the firstname field. Does the value it return seem right to you?
If you have an entry like "Bob" and it returns something like 5 instead of 3, you know that there are some extra spaces or special characters causing issues.

[Perceptus]

I would Also try.

Right(Val([dbo_entities.firstname]),1)

[hellfire45]

This fixed it. thanks!