Using right() to return a literal value
Okay, this is something that must be so simple but I can't manage to figure it out:
I have a lookup table that stores an account # with a primary key. In another table I use a lookup combo box to allow the corresponding account # field to be selected with the mouse and autocompleted as its typed.
I am making a report that will allow me to have a control to return only the last 10 characters of the account #. I used the following:
Right([Account #], 10)
The function works but the problem is that it returns the last 10 characters of the account # ID (i.e. its primary key) instead of the actual account #. I thought maybe it was because the function was using the Account # field from the table with the combo box. So then I tried:
Right ([Account # Lookup].[Account #], 10)
in the hopes that it would select the last 10 chars from the lookup table where the account #'s are stored. Instead, it asks me for a paramter when I go to form view on my report.
I tried using right() in a query field and then inserting that in my report but I have the same problem...either I return the primary key instead of the actual value or I get asked for a parameter.
Oh and when I specify the paramter, every record returns the value I typed in.
I know this is something that I should be able to figure out easily but unfortunately the help feature doesn't provide a specific example for this case and I can't find one anywhere.