Same field name in two tables

I am running a query that utilizes two joined table. One of the fields for my 
query is  Left([ACCT_NBR] ,3) AS Prefix.  The problem is that both tables 
have a field called [ACCT_NBR].  How do I designate which table it is coming 
from.  thanks 
0
Utf
2/19/2010 6:10:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
4242 Views

Similar Articles

[PageSpeed] 54

Qualify it with the table name:

Left([TheTableNameGoesHere].[ACCT_NBR] ,3)

Ken Sheridan
Stafford, England

cluckers wrote:
>I am running a query that utilizes two joined table. One of the fields for my 
>query is  Left([ACCT_NBR] ,3) AS Prefix.  The problem is that both tables 
>have a field called [ACCT_NBR].  How do I designate which table it is coming 
>from.  thanks

-- 
Message posted via http://www.accessmonster.com

0
KenSheridan
2/19/2010 6:22:13 PM
Cluckers - 

Try this (using your table name):

Left([tablename]![ACCT_NBR] ,3)

-- 
Daryl S


"cluckers" wrote:

> I am running a query that utilizes two joined table. One of the fields for my 
> query is  Left([ACCT_NBR] ,3) AS Prefix.  The problem is that both tables 
> have a field called [ACCT_NBR].  How do I designate which table it is coming 
> from.  thanks 
0
Utf
2/19/2010 6:42:01 PM
=?Utf-8?B?RGFyeWwgUw==?= <DarylS@discussions.microsoft.com> wrote in
news:09057D4C-2131-4EE3-A755-858DA1F9944D@microsoft.com: 

> Try this (using your table name):
> 
> Left([tablename]![ACCT_NBR] ,3)

Uh, I think that should be a period instead of an exclamation point.

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
2/20/2010 12:51:31 AM
Reply:

Similar Artilces:

Length of User defined field in Inventory item master
Is it possible to increase the Length of User defined field (created from Inventory control window) in Inventory item master? GP provides about 12 characters. We require to key in a serial number which is 25 characters in length. Would this be possible? Thanks CarlG Did you try to increase the Keyable lenght of the field in Modifier? If the table is setup to accept more characters then you can Modify the window, select the field, double click on data Type property, select the field from the list, click Open and then set the keyable length to the desired length. You also need to assign...

Referencing the name of a worksheet in a cell
hello excel gurus... I would like to have a string of text that incorporates the name of on the worksheet tab. I have a series of worksheet that are all very similar, it is just one word in the title that changes and that is what I will be naming the worksheet. Is there a way that I can have that cell reference the tab name, so that I only have to name it once and not twice. Thanks! -Bob Bob, Not sure if you want to get the cell from the tab name, or set the tab name from the cell value. So, if the former =MID(CELL("filename",A1),FIND("]",CELL("filename&qu...

Go to named range nominated by user
Hi I'm a novice macro user - I apologise in advance. In Office 2007 excel I want to create a macro that:- 1. Creates a msg book and asks the user to enter a named range e.g. "Nov_09" 2. Go to that named range on the current worksheet 3. Copy and paste values within that named range End. I searched past threads but I couldn't find anything that deals with a user nominating the named range. -- Thank for your help BeSmart Try the below macro which works on the active sheet. Edit the destination cell to suit your requirement Sub Macro() Dim strName...

5000 character limit in data fields
Is there any way to extend the 5000 character limit for data fields? I have one field that will be at least 10000 characters regularly. It appears that I cannot make a custom field larger then 5000 characters though even with the ntext format. Just wanted you to know that we have just created a 5000 character ntext field in the Leads entity and get an error going offline with that data. Microsoft is working on a fix for this, but in the meantime we're stuck. Can you use the Notes Entity? "fbell@itstrategists.com" wrote: > Is there any way to extend the 5000 character...

Fields to be Filled in
Hi I have a field called "Carer Availability". This field is a combo box and displays either "Has a carer" (Value = 1) or "Has No Carer" (Value = 2). They return these values to a table After this field i have several fields relating to Carer details (Carer Family Name, Carer Given Name, Carer DOB and Carer Language) all of this fields need to filled in if the Carer availability = has a carer (Value = 2) If they are not all filled in then i need a message box advising which ones need to be filled in. Can someone help me out with this. Im only starti...

Report Name Customer
Hi We have created a custom report in Visual Studio which shows all the service activities per customer in CRM. I now would like to display the customer name which was used in the filter as the heading of the report. If no customer was selected the heading should be blank. Do I need to use CRM_FilterText to achieve this? I have attempted to insert a parameter without success. I would really appreciate some guidance in how to achieve this. Many Thanks Mark no you can easily achieve this with a table group heading "Mark Braithwaite" <MarkBraithwaite@discussions.microsoft....

Why doesn't SFO Mail Merge allow Custom Fields?
Can anyone tell me WHY SFO mail merge will NOT allow Custom Fields? You can use Custom Fields for MailMerge! You only have to use our Add-On WordMailMerge for MSCRM 1.2 You can download a TRIAL-Version from www.mscrm-addons.com -- -- Christian support@mscrm-addons.com www.mscrm-addons.com Your company for MS-CRM ADD-ONS! GroupCalendar for MSCRM Related Documents for MSCRM WordMailMerge for MSCRM "Darryl" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:350301c51f3e$c31fed10$a601280a@phx.gbl... > > Can anyone tell...

Make subform visible when a field has a particular value
Hi, I have a field in my form that is a text field, but will only be entered as Y or N. If the field is Y, I want a subform to be displayed, if it's N the subform can stay hidden. I can't work out exactly how / what to have the subform bound to to make this work. thanks JJ Use the link master field and link child field to link the subform to the main form. Use the after update event of the textbox to show or hide the subform. Use the current event to show or hide the subform when opening an existing record for editing. Jeanette Cunningham MS Access MVP -- Melb...

company name change
Hello Our company has recently changes names. We already have two email domains currently in use, one set as primary for everything and another which was used in the past. We have one defined SMTP address, which is primary for all users. We also have another which was used int he past, but is not listed as a SMTP address in EXCH system mgr, recipients policies. Of course, I have a new domain to add as our primary smtp address and will need to make the current smtp address secondary for users. In EXCH system mgr, I do see CCMAIL and MS properties which appear to be our old email domain...

Cannot see fields in query design view on laptop; Access 2003
When in query design view on desktop (large screen), I can see the fields and criteria pane in the bottom half of the window, but when in query design view on laptop (small screen), I sometimes cannot see the fields and criteria pane no matter how far I scroll down. The only solution I have found so far is to move the fields and criteria pane to the top half of the window before closing the Access file on my desktop and before opening it on my laptop. Has anybody else run into this problem and found a way to view the fields and criteria pane if not immediately visible in the ...

Table in custom form
Hi, Is there anyway to place a table in a form? Thanks for the help in advance, dan Yes, but the details depend on ... the details of what you have in mind, = whether you want a table in the item body or something table-like on the = form surface.=20 FYI, there is a newsgroup specifically for Outlook forms issues "down = the hall" at microsoft.public.outlook.program_forms or, via web = interface, at = http://www.microsoft.com/office/community/en-us/default.mspx?dg=3Dmicroso= ft.public.outlook.program_forms --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outloo...

Changing Pivot table defaults
I am using excel 2003 and did something to change a default and I don't know how to set it back. Whenever I create a pivot table and drag a field into the data area the default behavior is to count the items. I thought it used to sum them by default. Does anyone know how I can change the default back to summing the data? Thanks! S If all the data in the field is numeric, then you'll see Sum. If any of the data in the field is text (or empty!), then you'll see Count. I don't think you can change this behavior. But you can use Debra Dalgleish's pivottable addin ...

Pivot Tables
When I display years and I would like to know the Difference From previous in either Excel 2003 or 2007, the values in the previous year do not display, only the resulting difference. Is it possible to display the previous year's values so the difference would make sense? Thanks for your assistance. -- I Teach Drag a second instance of the field you are aggregating into the data area. -- HTH... Jim Thomlinson "I Teach" wrote: > When I display years and I would like to know the Difference From previous in > either Excel 2003 or 2007, the values ...

Auto Complete (auto fill) in Lookup Fields
Main table looks up to 2nd table, using this code: SELECT [1a-Emb Format-tbl_Formats].[ID], [1a-Emb Format-tbl_Formats].[Format] FROM [1a-Emb Format-tbl_Formats] ORDER BY [Format]; In form view, when typing a value from the list, the value "auto completes" fine until I close the database, re-open, add a new record and try to populate the field again. Even if i type the copy exact value from the lookup table list, an error results saying it doesn't match. I'm forced to click and scroll on the drop arrow to populate. When I remove the relationship from the ...

'To' field missing
When you open an email the "To" Field Is Not Displayed in Outlook Inside Outlook, try clicking on View Message Header "Ron" <youwishbabe@hotmail.com> wrote in message news:024001c368de$4bbd4730$a401280a@phx.gbl... > When you open an email the "To" Field Is Not Displayed in > Outlook ...

HTML_Control Range name
I have a problem with one of my macros where I am copy an entire worksheet (not by range) to another workbook. However, every other worksheet it seems comes across with an message indicating that that range name HTML_Control already exists. I had run a piece of code in my worksheet to outline all range names and it appears to be some kind of range name....and to my surprise there are a whole bunch of other names as well. Can someone tell me if there is any specific purpose for it? Hi, Do you have controls on the worksheet? If so try renaimng them to something other than commandbutton1...

Desktop names have been changed
Don't know how this happened - but most of my Desktop file and folder names have been changed. Following is an example: e9193x999233 (12) This is what it was changed to. When Vista SP2 boots, just for a moment the correct name appears. But then, the names change to something like what I showed above. I tried System Restore and ERUNT - no luck. Can anyone tell me how to retrieve the file and folder names? On Sun, 06 Jun 2010 20:45:22 -0400, PeoplesChoice@Chicago.net wrote: > Don't know how this happened - but most of my Desktop file and folder > names...

Pivot table & dates
I have a table of information that currently has entries by mm/dd/yy that I want to read in a pivot table just as mm/yy. I run into a problem when I try to convert the date because it is read by its number (i.e., 38314 instead of as 11/23/04) and so if I use a LEFT function, it doesn't work. Does anyone have any ideas? Thanks! Hi you may group the data by month -- Regards Frank Kabel Frankfurt, Germany "tawtrey(remove this )@pacificfoods.com" <tawtrey(remove this )@pacificfoods.com@discussions.microsoft.com> schrieb im Newsbeitrag news:645C3F3A-27B2-4938-8B92-97D51...

Pivot Table Formatting
I am trying to customize the format of both a pivot table and pivot chart (color, line width, etc.) and have the format remain upon refreshing the data. Basically, I want to make this custom color design the default formatting for tables and charts. Does anyone know if this is possible? Also, can you customize color options as well? For example, if my color table doesn't have a brown, can I add brown to the format color options? You can create a custom chart type, and set it as the default. However, if you change or refresh the pivot table or chart, some of the formatting may be l...

problem with printing import of word table
Running Vista Ultimate 64-bit; office pro 2007 suite: How do I get rid of the boxes when trying to publishing? I need to import a table created in word 2007. I can get the text box set for margins and size, but I cannot find the command to keep the boxes surrounding each row and column of the table from printing. I apologize in advance for seeming so ignorant, but this is the first time trying this kind of operation. Your help is totally appreciated in advance. Thanks Do you mean the borders? High-light the entire table, Lines, more lines, select the pre-set grid, expand the colo...

Change name of tab sheet
The 'help' menu tells me to double click on the tab sheet and override the name. When I do this, nothing happens! delaze@chartermi.net Hello, Double-clicking should highlight the sheet name, allowing you to change it just be typing. If you can't get it to work, try right-clicking on the sheet name, and selecting rename from the menu. If that doesn't work, the worksheet is probably protected. Choose Tools, Protect, from the menu bar, and see if there is a menu option "Unprotect Sheet". If so, the sheet is protected. If is says "Protect Sheet", then...

FK references same column in same table
Hi, I have been reviewing the database objects in one of our development team's databases and have found something that i haven't seen before. There is a foreign key on one of the tables that has been created on the same column that it references i.e ALTER TABLE [dbo].[tbl_Address] WITH CHECK ADD CONSTRAINT [FK_tbl_Address_tbl_Address] FOREIGN KEY([ID]) REFERENCES [dbo].[tbl_Address] ([ID]) GO The ID column is an identity column. Is there a point to doing this, or is it a mistake? I can only surmise that the system will act as if it is not there at all. ...

Removing OrderBy from Tables and Queries in VBA
I have an issue where people are in my access database and they look at data through a table, sort it, and when they close the table, they are asked if they want to save their changes, they invariably say yes. This is slowing down my database. I want to write code that goes through all tables, queries, forms, and reports, and turns off the orderby or filters. forms and reports are no problem since I can open them and close them, but querydefs and tabledefs seems to be a different beast. I found the OrderByOn property which is dug into the object, and I have code to change...

when importing address book only names get imported, not email add
When I import a CSV file that contains names and email addresses to Outlook 2007, only the names get imported, not the email addresses. "Kathy" <Kathy @discussions.microsoft.com> wrote in message news:8D7BFFE8-AA77-4F5B-98B6-86F449907EE7@microsoft.com... > When I import a CSV file that contains names and email addresses to Outlook > 2007, only the names get imported, not the email addresses. Then you didn't map the fields correctly. -- Brian Tillman [MVP-Outlook] ...

Excel Table to Individual product list and spend
Hi, Basically I have an Excel data table and I have a Product name drop down list. I want to create a formula or macro so when a Product is selected, it would output the list of companies and their spend with the product. Below is a sample but the real spreadsheet has thousands of vendor names and spend for each product and there are probably ten product names altogether. Company Product1 Product2 Product3 Product4 Total ABC 7,009 8,000 15,009 Key 805,036 805,036 PC 56,016 57,470 568 114,054 Water 3,090 651 298,250 301,991 Total 66,115 863,157 306,818 -...