Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 8th, 2006, 02:26 PM
Registered User
 
Join Date: Jan 2006
Location: Eatontown, NJ, USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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.

 
Old February 8th, 2006, 02:29 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

What are the field names of your lookup table?

Mike
EchoVue.com
 
Old February 8th, 2006, 02:38 PM
Registered User
 
Join Date: Jan 2006
Location: Eatontown, NJ, USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Field names on Account # Lookup: Account ID (which is the primary key) & Account #

Order table is linked to Account # Lookup via the Charge #.

As it turns out I managed to get it to work after posting my question. I was generating a report from a query that only had the Order table. Once I went in and added the Charge # lookup table to the query, I used:

Right([Account # Lookup].[Account #], 10)

and it worked. I don't know if that was necessarily the best way to get it to work but it seems to function okay.


 
Old February 8th, 2006, 02:53 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

As long as it works!!


Mike
EchoVue.com
 
Old February 8th, 2006, 02:56 PM
Friend of Wrox
 
Join Date: Jan 2005
Location: Kansas City, Missouri, USA.
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

I've found that using the pound sign "#" in field names causes problems. I believe (I may be wrong!), that the pound sign is a reserved character that has special meaning, therefore, I avoid using it in any names.

Kevin

dartcoach




Similar Threads
Thread Thread Starter Forum Replies Last Post
Label vs Literal pinch BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 October 9th, 2007 01:01 PM
What's the use of braces in a string literal? aaaa0441 Pro PHP 7 January 20th, 2007 04:23 AM
What's the use of braces in a string literal? aaaa0441 Beginning PHP 4 January 14th, 2007 08:10 AM
Literal Control bilal589 ASP.NET 1.0 and 1.1 Basics 5 August 19th, 2006 01:42 PM
Object literal syntax shortt Javascript 4 May 28th, 2005 07:44 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.