Lookup Wizard changing Data Type

I am trying to make Lookups from Table Field to Table Field and generally it 
seems OK.

However the LookupWizard is changing the Data Type from Text to Number (I 
guess it's looking at the ID?)

Data picked from Combo Box 'looks' OK in Table view

A Query view is asking for a number but displays the Text field value when a 
valid ID number is added.

Viewing Results in FrontPage Database Wizard, and using hand coded .asp 
querie, it's showing the ID field value.

I can see why...

Any ideas?

Merci.


0
Utf
4/27/2007 4:40:01 PM
access 16762 articles. 3 followers. Follow

7 Replies
952 Views

Similar Articles

[PageSpeed] 44

Yes, the wizard isn't the problem it's the use of Lookup Fields in the
first place.  Don't!  The best solution to your current problem is to
go back to the tables and take them out.  Re-work anything that made
use of their cute little comboboxes.  It's OK to curse MS under your
breath.

See www.mvps.org/access for some relevant discussion.

HTH
-- 
-Larry-
--

"Steve Crowther" <SteveCrowther@discussions.microsoft.com> wrote in
message news:A104F06C-D508-4981-8E77-BDAA08C3502B@microsoft.com...
> I am trying to make Lookups from Table Field to Table Field and
generally it
> seems OK.
>
> However the LookupWizard is changing the Data Type from Text to
Number (I
> guess it's looking at the ID?)
>
> Data picked from Combo Box 'looks' OK in Table view
>
> A Query view is asking for a number but displays the Text field
value when a
> valid ID number is added.
>
> Viewing Results in FrontPage Database Wizard, and using hand coded
..asp
> querie, it's showing the ID field value.
>
> I can see why...
>
> Any ideas?
>
> Merci.
>
>


0
Larry
4/27/2007 5:21:18 PM
Las

So what your saying, in you mildly sarcastic way, is that this doesn't work.

Is there an alternative?

Thanks


-- 
Confusion will be my epitaph...


"Larry Daugherty" wrote:

> Yes, the wizard isn't the problem it's the use of Lookup Fields in the
> first place.  Don't!  The best solution to your current problem is to
> go back to the tables and take them out.  Re-work anything that made
> use of their cute little comboboxes.  It's OK to curse MS under your
> breath.
> 
> See www.mvps.org/access for some relevant discussion.
> 
> HTH
> -- 
> -Larry-
> --
> 
> "Steve Crowther" <SteveCrowther@discussions.microsoft.com> wrote in
> message news:A104F06C-D508-4981-8E77-BDAA08C3502B@microsoft.com...
> > I am trying to make Lookups from Table Field to Table Field and
> generally it
> > seems OK.
> >
> > However the LookupWizard is changing the Data Type from Text to
> Number (I
> > guess it's looking at the ID?)
> >
> > Data picked from Combo Box 'looks' OK in Table view
> >
> > A Query view is asking for a number but displays the Text field
> value when a
> > valid ID number is added.
> >
> > Viewing Results in FrontPage Database Wizard, and using hand coded
> ..asp
> > querie, it's showing the ID field value.
> >
> > I can see why...
> >
> > Any ideas?
> >
> > Merci.
> >
> >
> 
> 
> 
0
Utf
4/27/2007 5:30:02 PM
"Appalachia" <Appalachia@discussions.microsoft.com> wrote in message 
news:B2B4CDC7-917F-4C28-AFA8-8E7DC0AC10C4@microsoft.com...
> Las
>
> So what your saying, in you mildly sarcastic way, is that this doesn't 
> work.
>
> Is there an alternative?

Yes. You can do lookups, but you should not do it with "lookup fields" 
because they obscure what is actually stored in the table. They are useful 
only for end-users who just use datasheet view, and are a mild-to-traumatic 
pain to others who later try to use the field as though it were "real, 
normal" data.

For example, if you have a table of Custom Colors, you could refer to it in 
a lookup field to see the Color Name in datasheet view of the Product Table, 
but what's actually stored is the id or key field of the Custom Colors 
Table, not the name you see.  So, when you query that table, you expect to 
see the name, but instead see the number that's actually stored.

Instead, if you store the number, you can always use a Combo Box to select 
it by looking at the name; you can link the Product Table in a Query to the 
Custom Colors Table to retrieve the color name along with product 
information, or again use a two-column Combo Box. And, when you look in the 
Product Table, it's _obvious_ what is stored, a foreign key to the other 
table, so there are no surprises.

 Larry Linson
 Microsoft Access MVP 


0
Larry
4/27/2007 5:53:58 PM
Lol,

Any examples anywhere ?

Tats.
-- 
Confusion will be my epitaph...


"Larry Linson" wrote:

> 
> "Appalachia" <Appalachia@discussions.microsoft.com> wrote in message 
> news:B2B4CDC7-917F-4C28-AFA8-8E7DC0AC10C4@microsoft.com...
> > Las
> >
> > So what your saying, in you mildly sarcastic way, is that this doesn't 
> > work.
> >
> > Is there an alternative?
> 
> Yes. You can do lookups, but you should not do it with "lookup fields" 
> because they obscure what is actually stored in the table. They are useful 
> only for end-users who just use datasheet view, and are a mild-to-traumatic 
> pain to others who later try to use the field as though it were "real, 
> normal" data.
> 
> For example, if you have a table of Custom Colors, you could refer to it in 
> a lookup field to see the Color Name in datasheet view of the Product Table, 
> but what's actually stored is the id or key field of the Custom Colors 
> Table, not the name you see.  So, when you query that table, you expect to 
> see the name, but instead see the number that's actually stored.
> 
> Instead, if you store the number, you can always use a Combo Box to select 
> it by looking at the name; you can link the Product Table in a Query to the 
> Custom Colors Table to retrieve the color name along with product 
> information, or again use a two-column Combo Box. And, when you look in the 
> Product Table, it's _obvious_ what is stored, a foreign key to the other 
> table, so there are no surprises.
> 
>  Larry Linson
>  Microsoft Access MVP 
> 
> 
> 
0
Utf
4/27/2007 6:06:01 PM
"Appalachia" <Appalachia@discussions.microsoft.com> wrote in message 
news:0F316705-EDFD-45DD-9B8F-91E5D143AFF6@microsoft.com...
> Lol,
>
> Any examples anywhere ?

Yes, there are examples throughout the Northwind Traders example database 
that comes with, and unless you take specific action to prevent it, is 
installed with every copy of every version of Access.

An outstanding "guide" to the examples in the Northwind Traders database 
(and others) is the Developer Solutions sample database, a download link to 
which is available in the Knowledge Base article at 
http://support.microsoft.com/kb/248674/en-us.

 Larry Linson
 Microsoft Access MVP 


0
Larry
4/27/2007 6:46:56 PM
On Fri, 27 Apr 2007 09:40:01 -0700, Steve Crowther
<SteveCrowther@discussions.microsoft.com> wrote:

>I am trying to make Lookups from Table Field to Table Field and generally it 
>seems OK.
>
>However the LookupWizard is changing the Data Type from Text to Number (I 
>guess it's looking at the ID?)
>
>Data picked from Combo Box 'looks' OK in Table view
>
>A Query view is asking for a number but displays the Text field value when a 
>valid ID number is added.
>
>Viewing Results in FrontPage Database Wizard, and using hand coded .asp 
>querie, it's showing the ID field value.

It's showing the ID field value because that is what is in the table. The
Lookup Wizard is pretty much universally considered to be a misfeature and
more of a problem than a benefit. See

http://www.mvps.org/access/lookupfields.htm

for a critique.

You can base your FrontPage display on a stored Query joining to the lookup
field. The lookup wizard just isn't smart enough to help here - the *only*
things it can do are make it easier to use Table Datasheet view in the Access
user interface (which is a Bad Thing because table datasheets should not
generally be used for data interaction anyway), and make it a bit easier to
add combo boxes to Forms (again in the Access user interface).

In short - the lookup wizard will make it harder, not easier, to work with
your Frontpage interface. Use the lookup tables, by all means; but you'll need
to provide your own Queries in order to use ASP.

             John W. Vinson [MVP]
0
John
4/27/2007 7:52:29 PM
I disagree

Lookups should be used everywhere-- just not the lookup wizard


"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:trk4335jo8jntfh75haek233pfifnlqthp@4ax.com...
> On Fri, 27 Apr 2007 09:40:01 -0700, Steve Crowther
> <SteveCrowther@discussions.microsoft.com> wrote:
>
> >I am trying to make Lookups from Table Field to Table Field and generally
it
> >seems OK.
> >
> >However the LookupWizard is changing the Data Type from Text to Number (I
> >guess it's looking at the ID?)
> >
> >Data picked from Combo Box 'looks' OK in Table view
> >
> >A Query view is asking for a number but displays the Text field value
when a
> >valid ID number is added.
> >
> >Viewing Results in FrontPage Database Wizard, and using hand coded .asp
> >querie, it's showing the ID field value.
>
> It's showing the ID field value because that is what is in the table. The
> Lookup Wizard is pretty much universally considered to be a misfeature and
> more of a problem than a benefit. See
>
> http://www.mvps.org/access/lookupfields.htm
>
> for a critique.
>
> You can base your FrontPage display on a stored Query joining to the
lookup
> field. The lookup wizard just isn't smart enough to help here - the *only*
> things it can do are make it easier to use Table Datasheet view in the
Access
> user interface (which is a Bad Thing because table datasheets should not
> generally be used for data interaction anyway), and make it a bit easier
to
> add combo boxes to Forms (again in the Access user interface).
>
> In short - the lookup wizard will make it harder, not easier, to work with
> your Frontpage interface. Use the lookup tables, by all means; but you'll
need
> to provide your own Queries in order to use ASP.
>
>              John W. Vinson [MVP]


0
Aaron
4/27/2007 8:02:49 PM
Reply:

Similar Artilces:

I want to unlock my word doc to make changes its protected
I am writing a word document the other night. I went to carry on with it tonight but found it has protected the document and won't let me continue writing or editing Word 2007? Assuming that you have activated Office, it seems your trial version of the application has expired. Time to pay the piper! -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> ...

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Changing SQL Server for CRM 1.2
As my SQL server is currently on its last breath I am in the process of moving our CRM 1.2 databases to a new dedicated serever. I have detatched the databases from the old server, copied the files to the server and attached them. I have then gone into CRM deployment manager and changed to the SQL server to the new server. That all appears to work fine, no error messages. But when users try to access the CRM system they get an error and the CRM server event log fills with error messages. A few are listed below: dmLog: Failed to execute the SQL batch in the file sqlbatch.sql. MSCRM P...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

Territory Change
Our Sales VP has restructured all of the geographical territories that we had set up in Microsoft CRM. Our many thousands of Accounts are associated with territories. Obviously it is ludicrous to think that we would have to go one by one and change the territory on each account to the new territories. However, I have heard that there are strict rules for making changes directly to the backend SQL database. If we develop a SQL statement to change the territories assigned to each account to the new territories, are we going to be breaking anything? We don't want to screw up any de...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

Sort by Credit Card Type on EDC Detail Report
What do I have to do to get my EDC Detail Report to show different credit card types, like Visa, MC or Amex. Now under Tender Type, it just shows Credit card...well...I kinda knew that. How do I fix it? add tender types for each credit card type rather than just "credit card". then z out. all transactions after this change will reflect what you want in the reports. "tl" <tl@discussions.microsoft.com> wrote in message news:A54EEC95-208C-4962-A089-84B22A67632A@microsoft.com... > What do I have to do to get my EDC Detail Report to show different credit >...

Changing font in Money 2004 register?
Is there any way to change the font(s) used in MS Money 2004's registers? The default font is too small and difficult for me to read. Also, is there any way to change the color scheme to something more pleasing to my eye (like you can do in Quicken...) Thanks. Nope and Nope. See http://umpmfaq.info/faqdb.php?q=136. "Debbie R." <debbimsr@bellsouth.net> wrote in message news:f5ff01c43e15$e2ae3700$a401280a@phx.gbl... > Is there any way to change the font(s) used in MS Money > 2004's registers? The default font is too small and > difficult for me to read....

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Change the Exchange Virtual Directory to different website
I would like to remove the exchange virtual directory default website and move it another website which is currently redirecting to the website I want to delete. Meaning rather than logon to OWA as http://www.wheresmylunch.com/exchange (current default website) I want to move to http://www.getyourownsandwich,com/exchange. I am using Exchange 2000 server. Rube You would change the host header on the current website. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply t...

question on the rules wizard
When clikcing on the Rules Wizard, Outlook locks up - consistently. Has anyone got a remedy for this? Thank you, rich rpage@concerto.com ...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Customer check data
When customers pay by check RMS asks for specific information such as drivers license number, routing number, account number, address and phone number. Does anyone have a report and or a way to extract this info from the database for cases when the check is returned for NSF? Please advise, Scott We can write you this report. Contact me for detail on price . Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "Scott Santorio" <scott@tt-newyork.com> wrote in message news:e8ZKkR6$HHA.3716@TK2MSFTNGP03.phx.gbl... > When customers pa...

Money 2000 Account Balance Changed Inexplicably
Opening Money 2000, which I've used without problem since late 1999, I noticed that my checking account balance was overstated by almost $2000! I went to the register to see if there was a false transaction entered and it appears that this balance change goes back years with no obvious single entry being the culprit. When I run the "balance this account" function, it shows that the closing balance from my last statement, which was correct and rectified, is now also wrong and reflects the higher balance. What do I do now? The only thing that I can think of is to restore...

Macro to seperate data
Hi I seem to be struggling to find a macro that will work in previous threads. In sheet 1 is a list of data in columns A:N and the number of rows will vary. It is a list of sales with each sale record ocuppying one row. The salesperson's name is in column C and each salesperson will have multiple entries. What I am trying to do is create a seperate summary sheet in the workbook for each salesperson. Therefore sheets 2 to 20 are templates that already exist with a different salesperson's name entered into cell C3 on each of them. I am trying to find a macro that ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

Problem with Database Wizard
I'm trying to generate a diagram based on the contents of an Access database, using the database to provide x,y coords for instances of a Master shape. It seems I need to run the wizard twice, first to link a master, then to generate the drawing. The first bit appears to work OK, but when I do the second bit, Visio says that there is no master in the stencil that it can use. But I know the master is connected, because if I modify the database, then refresh the shapes, they change accordingly. Does anyone have any idea why this isn't working for me ? I'm using Visio 20...

Changing language
I am running the Swedish version of Excel for XP and I need the US (or English) version. Is there a way to convert the language and all the settings associated with it? Using the swedish version is rather annoying since the formula names are translated to swedish. Thank you, Magnus ...

With and import tool can you change only item description?
Is there a way to change only the item description on a large quanity of items. What about the extended description? Thanks for your help. Use the MS SQL Data Import Tool by EMS. $65.00. The QSImport Tool available to download from Microsoft will probably work but is not supported by Microsoft. Kinnard L. Kohler Business Machines Systems 6101 South Shackleford Road Little Rock, AR 72204-8606 (T) 501-375-8380 (F) 501-375-0043 (Cell) 501-412-5686 Email: kinnard@removebmsar.com "Lisa" wrote: > Is there a way to change only the item description on a large quanity of >...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...

Changing a profile on Microsoft Outlook 2003
I set up two profiles through the control panel and directed Outlook to prompt me for which profile to use each time it was opened. But now it skips the prompt and goes straight to one of the profiles. I need to restore that prompt, but it won't respond to the instructions in the control panel Mail dialogue box Hi Chris, did you get the same behavior after a restart of the computer? You could try "Sart/run/fixmapi.exe" (you don�t get any confirmation message) and restart the computer again. If this wouldn�t wotk, I would create a 3rd (test) profile. Maybe Outlook don�t ...

macros entering data
How do I create a macro that goes to one cell then waits until I enter new data, then goes to another cell and waits until I enter new data etc? thanks How about something like sub Enter_Data() dim NewValue NewValue = inputbox("Enter the value for cell A1: ") range("a1").value = NewValue NewValue = inputbox("Enter the value for cell G2: ") range("g2").value = NewValue NewValue = inputbox("Enter the value for cell I8: ") range("i8").value = NewValue end sub ...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...