how to convert lookup values to the "display text"

I'm using an sql code (below) which uses a few lookup fields.  Unfortunately 
in the datasheet view, I get the "bound values" instead of the "display 
values".  How can I change the properties for the these lookup fields so I 
can see the "display values" from the datasheet view?



SELECT 
[Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status]
FROM [Form_9_Status]

UNION ALL SELECT 
[Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status]
FROM [TDY_Status]

UNION ALL SELECT 
[Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status]
FROM [Order_Status] INNER JOIN [Funding] ON Order_Status.Funding = Funding.ID;

0
Utf
2/22/2010 3:04:01 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1940 Views

Similar Articles

[PageSpeed] 20

Ccorley -

You do this by joining the lookup table in each SELECT.  Assuming you have a 
lookup table called [ExpenseTypes] and you want the field 
[Expense_Description] to show instead of [Expense_Type], and all tables have 
[Expense_Type] populated, you change the query as follows:


SELECT 
[Funding],[Date],[Description],[Company],[Expense_Description],[Amount],[Status]
FROM [Form_9_Status] INNER JOIN [ExpenseTypes] ON 
[Form_9_Status].[Expense_Type] = [ExpenseTypes].[Expense_Type]

UNION ALL SELECT 
[Funding],[Date],[Description],[Company],[Expense_Description],[Amount],[Status]
FROM [TDY_Status] INNER JOIN [ExpenseTypes] ON [TDY_Status].[Expense_Type] = 
[ExpenseTypes].[Expense_Type]

UNION ALL SELECT 
[Funding],[Date],[Description],[Company],[Expense_Description],[Amount],[Status]
FROM ([Order_Status] INNER JOIN [Funding] ON Order_Status.Funding = 
Funding.ID) INNER JOIN [ExpenseTypes] ON [Order_Status].[Expense_Type] = 
[ExpenseTypes].[Expense_Type];

If not all tables have [Expense_Type] populated, then you need an inner join 
instead.  Here is a sample:
SELECT 
[Funding],[Date],[Description],[Company],nz([Expense_Description],""),[Amount],[Status]
FROM [Form_9_Status] LEFT JOIN [ExpenseTypes] ON 
[Form_9_Status].[Expense_Type] = [ExpenseTypes].[Expense_Type]

You will need to make these changes for each lookup table you have.

-- 
Daryl S


"ccorley" wrote:

> I'm using an sql code (below) which uses a few lookup fields.  Unfortunately 
> in the datasheet view, I get the "bound values" instead of the "display 
> values".  How can I change the properties for the these lookup fields so I 
> can see the "display values" from the datasheet view?
> 
> 
> 
> SELECT 
> [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status]
> FROM [Form_9_Status]
> 
> UNION ALL SELECT 
> [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status]
> FROM [TDY_Status]
> 
> UNION ALL SELECT 
> [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status]
> FROM [Order_Status] INNER JOIN [Funding] ON Order_Status.Funding = Funding.ID;
> 
0
Utf
2/22/2010 3:30:01 PM
Reply:

Similar Artilces:

If Null
I have a report that tracks the dates certain events happen. i.e. Date Request received Date sent out Approval date etc... How do I get the report to display the text "Not Available" if there is no date recorded in the table? The NZ function is your friend here ;-) The Nz function replaces nulls with zero by default, but can be used to replace a null with any value/string you want. So, to display "Not Available" if a field named RequestDate is null, you enter the expression =Nz([RequestDate],"Not Available") in the textbox which is displaying the [Req...

MSWORD: Replacing display text with field codes in a list of hyperlinks
Happy New Year to all! Could someone kindly make my 2010 by suggesting how I can replace the display text in a list of hyperlinks with the corresponding field codes? Thanks, Nimmi Hi nimmi, Pressing Alt-F9 toggles the field code display on/off. If the hyperlinks are formatted as such (eg blue underlined text and clicking on them activates the links), pressing Alt-F9 should display the field codes. -- Cheers macropod [Microsoft MVP - Word] <nimmi_srivastav@yahoo.com> wrote in message news:62836dc4-c36b-4776-bb94-fc1d27a28a41@j24g2000yqa.googlegroups.com... >...

how to convert lookup values to the "display text"
I'm using an sql code (below) which uses a few lookup fields. Unfortunately in the datasheet view, I get the "bound values" instead of the "display values". How can I change the properties for the these lookup fields so I can see the "display values" from the datasheet view? SELECT [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status] FROM [Form_9_Status] UNION ALL SELECT [Funding],[Date],[Description],[Company],[Expense_Type],[Amount],[Status] FROM [TDY_Status] UNION ALL SELECT [Funding],[Date],[Description],[C...

how to display text when a cell>0 ?
Hi there. i have something like this: A B C D 1 hello 1 2 test 0 3 bye 1 Now i want to display hello at D7 when B1>0 So the output should be: A B C D 1 hello 1 hello 2 test 0 3 bye 1 bye How can i do this? thanks, Jan Willem Jan, try this, =IF(B1>0,A1,"") -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Janwillem" &...

outlook express will not display text or pictures in preview pane
can someone tell me why i cannot see the messages or pictures from newsgroups in the preview pane. the message looks like it downloads but dose not showup in the preview pane. check your Tools, Options, Security settings... "kaal" <anonymous@discussions.microsoft.com> wrote in message news:33da01c4a84d$e4d5edf0$a601280a@phx.gbl... > can someone tell me why i cannot see the messages or > pictures from newsgroups in the preview pane. > > the message looks like it downloads but dose not showup > in the preview pane. kaal <anonymous@discussions.microsoft...

Outlook 2002 unable to display text/plain INLINE attachments
Anyone else encountered this problem? Any fixes? I'm a email developer and we're firing different combos of MIME headers at a 2002 Outlook client to try and find a combo that it CAN handle. Content-Type: TEXT/PLAIN Content-Disposition: INLINE on a multipart MIME message is still parsed as an "attachment" - and it bungles up an ATTxxxx attachment name for them as well. Outlook Express, Netscape, and other clients correctly display the attachments inline with the message body. direct responses appreciated - rcb at 3k dot com ...

Lookup a value in a table and display text if a value exists
I have a customer database form named frmMain with a field called txtAgency. I also have a table that has a column of agency codes that are in the Outlook Global directory. The table is called tbl_Global and consist of about 40 records. If the user inputs an agency that is within this table on the frmMain, I want the phone number field (txtPhone) on the database form to automatically display "See Outlook". If the agency is not within this list, I want the end user to be able input a value on the form. What is the best way to go about this? Use DLookup() to get t...

Code Snippet of Display Text on Button Control
Hi, I want to display a text on a button which is dragged from the toolbox and placed on the dialog. I have posted a message regarding this ----> "Displaying text on a controls" I got the reply. But it is not still working Here is the code snippet..... MyApplicationDlg.h CButton m_button_control; MyApplicationDlg.cpp void CMyApplicationDlg::DoDataExchange(CDataExchange* pDX) { CDialog::DoDataExchange(pDX); DDX_Control(pDX, IDC_BUTTON_TEST, m_button_control); } BOOL CMyApplicationDlg::OnInitDialog() { CDialog::OnInitDialog(); (GetDlgItem(IDC_BUTTON1))->SetW...

Display Text with Formulas
I have 2 times and i want to display the difference between them in Hrs & Mins including the text "Hrs" & "Mins". Example Cell A1 Cell B1 Cell C1 8:00 12:30 4hrs 30mins I am currently using =B1-A1 formula which gives me my correct answer displayed as 4:30 but i want to include the hrs and mins. Any help would be great. Thanks Submitted via EggHeadCafe - Software Developer Portal of Choice Hierarchical Relationships Without DataRelation http://www.eggheadcafe.com/tutorials/aspnet/7cacaeec-65f4-4bfd-9fb9-48ead40566e2/hiera...

using a check box to display text from a list
Excel 2003 I am trying to use a check box to display a category (Text) from a list in a cell and have tried code based on an "if structure" with no success. Has anyone got any suggestions. Roger W wrote: > Excel 2003 > I am trying to use a check box to display a category (Text) from a > list in a cell and have tried code based on an "if structure" with no > success. Has anyone got any suggestions. Hi Roger, Try to post the code you used, so we can suggest how to modify... -- Thanks in advance for your feedback. Ciao Franz Verga from Italy ...

Display Text in the Data Area of a Pivot Table
I would like to simply display text in the data area of a pivot table without summarizing it. For example, take the following database: Date Time Instructor 8/9 8am Jones 8/9 9am Smith 8/10 8am Anderson 8/10 9am Doe ....etc and display it in a pivot table like so: | 8/9 | 8/10 --------------------------------- 8am | Jones | Anderson 9am | Smith | Doe thanks. ...

Hyperlink display text maximum in Excel 2003
Every time I try to create a hyperlink of a cell (that contains a couple of sentences worth of text), I seem to reach the maximum allowed characters for the display text - and Excel cuts off the end of the paragraph. Hence, the the hyperlink is created, but I loose half the content of the cell. Any ideas? It looks like that text to display can be 255 characters. And the same thing holds for =hyperlink(link_location,Friendly_name) Friendly_name can be up to 255 characters--and if you point at another cell that contains a longer string, you'll get an error back. Maybe you could put t...

Excel 2007
Another formula creates the value of D18. In cell E18 I want to write a formula. In that formula, if the value of cell D18 is between a set of numbers, like between 60 & 70 then display "TEXT" in E18. If the value of cell D18 is between 0 & 59 or between 71 & infinty, then I want "DIFFERENTTEXT" displayed in E18. I can get this to work on one specific # with the If formula in this example. =If(D18=60,"TEXT",DIFFERENTTEXT") The value of Cell D18 will be in tenths and I do not want to keep adding If formulas for ever tenth between 60.0...

Can excel display text in CHART's data table?
All my text are displayed as "0".......just want to keep some comments nicely in there.Not sure whether excel can do this. Or anybody knows other better way? something like this: para1 1.2 1.3 1.4 para2 1 7 100 comments bad good excellent Hi, The chart data table is very limited in terms of formatting. You would be better using cells to display the information you want. And position the chart above them Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "8uv" <8uv@discussions.m...

Using Pivot Table to display text values
I am trying to use an Excel Pivot Table to display the associated text data (there is only one value cell for each row and column combination). The data is currently displayed as zeros in the Pivot Table since it is trying to COUNT, rather than just display the text. Is there a way to turn off the Count function without using another formula such as SUM or AVERAGE? Text can't be displayed in the data area of a pivot table. You have to use one of the available summarize options (Count, Sum, etc.), but you could format the cells with white font, so the results aren't visible. ...

display text in one cell as a number in an adjacent cell in excel
would love to get an example of the right formula, thanks Perhaps you mean something like this in say, E1: =VLOOKUP(D1,{"Text1",10;"Text2",20;"Text3",30},2,0) If D1 contains: Text2, then E1 returns: 20 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Denno" wrote: > would love to get an example of the right formula, thanks Re: display text in one cell as a number in an adjacent cell in excel ...

find text, display text
I want to find text within a cell and, if found, display a text message of my choosing. For instance, if A1 has the txt "Special Price", then I want B1 to say, "Special." If it says "Regular Price", I want B1 to say "Reg." Thanks! Hi you may have a look at VLOOKUP (together with a lookup table). See: http://www.contextures.com/xlFunctions02.html -- Regards Frank Kabel Frankfurt, Germany "shaun" <shaun@discussions.microsoft.com> schrieb im Newsbeitrag news:F55C22DF-F71D-4261-A4D8-91DA409F4989@microsoft.com... > I want to find tex...

display textfile at server side..
In a client server system, the client's job is generating a random number and save it in a text file ( at the server side). Meanwhile, the server's job is display the text file when meet some condition at the client side. How can i code in the way that asking the server to show the text file ? And do i need to install any related software at the server side? Hope can get the solution soon cause my project is running out of time.. thanks p/s: i dont have any experience at client server system First of all, the scenario itself isn't complete. Does the server needs to send comple...

Display text as date
Hey guys :) I use this formula =">="&DATEVALUE(Files!D3)+TIMEVALUE(Files!D3) to display and use as filteroption. (Dont ask why I have to do this exercise....) However the formula works all right but the display appears as decimalnumber in this text expression: Actual displayed: >=40170,6145833333 Wanted displayed: >=23.12.2009 14:45:00 I KNOW that there is some way to transform the expression displayed, but have not found anything useful on the web - so far. Are there anybody called to give me a hand on this one? Best regards Snoopy (Wish you a Happy ...

Display text in Excel Bubble chart.
H1!, I have an Excel spreadsheet which contains X-coordinates, Y-Cordinates, Well name and Cumulative Oil/Gas/Water production. I have created bubble map using X,Y and Z(Cum production). I need to show the well name on each Bubble, which I am unable to do. Kindly sugest method to display the well name on the bubbles. Thanks, Raja Mukherjee ...

Why does Outlook display text in yellow? How can i change it?
When pasting text into an e-mail, it appears in pale yellow. Sometimes, parts of meesages from others are also yellow. How can I prevent this? Are you using stationery by any chance? -- Kathleen Orland "Wise Dog" <Wise Dog@discussions.microsoft.com> wrote in message news:2D2FBAF7-2B2B-4C66-AE33-B2CA5A51B821@microsoft.com... > When pasting text into an e-mail, it appears in pale yellow. Sometimes, > parts of meesages from others are also yellow. How can I prevent this? ...

Display text of functional band in which a shape is located
I am trying to display the Shapetext of a functional band inside standard shape positionned in that band. I would like the Shapetext t change as I move the shape is moved from one band to another. I am able to do it for a shape that doesn't move around from band t band in this fashion: Click the shape located in the Functional band. From the Insert menu, choose Field and click Custom Formula from th Category list. After the equal sign, insert SHAPETEXT(Functional band.19!TheText) where Functional band.19 is the name of the band in which the shape i currently located. This will insert th...

Display text on Tabs of Property Pages from Application
Hi, I am working on property pages. Every thing is working fine.Just i want to display "Page1", "Page2","Page3","Page4" on the tabs of the property pages from my application. I have created this property pages using the property sheets by using AddPage() API. Now how to display on the tabs from the application. Is there any API for that.......... Thanks lucky If you're talking about changing the text on the tabs: http://support.microsoft.com/kb/q141487/ Tom "lucky" <Laxmanmaruthy@gmail.com> wrote in message news:117336696...

Grid column display text trimming
Can text length that extends beyond column width be formated to display "..." to indicate the text length extends beyond the column. If so, would the same technique be used in the grid display with Visual Basic.Net. ...

Display text with Unicode format (ex: Chinese) on controls written by VC6.0
Hi all, We can write a project with Debug-Unicode mode and display text with Unicode format? Ex: Chinese or Japanese Idea? Thanks. >We can write a project with Debug-Unicode mode and display text with Unicode >format? Ex: Chinese or Japanese With VC6 you can build a debug (or release) Unicode project that when run on a proper Unicode platform (like XP) should be able to display Chinese or Japanese texts (providing the font you're using has the characters). Dave Tittle bar, menu, button can display text with Unicode format when i build a debug Unicode project? We can use fo...