Invalid Value types in Lookup field

I am new at Access and don't really know what I am doing.  I created two 
tables... 1. contact information 2. subcontractors used at job sites.  I 
created a lookup field for the subcontractors so when I create reports or 
queries I can connect the subcontractors with their contact information.  I 
set the field to text, but everytime I try to put the cursor in the field it 
comes up with an error message saying that the value isn't valid for the 
field.  How do I get it to stop doing this? 
0
Utf
2/20/2008 9:24:01 PM
access 16762 articles. 3 followers. Follow

2 Replies
1659 Views

Similar Articles

[PageSpeed] 37

Your first problem was defining a "lookup field" -- I'm sure Microsoft was 
trying to help end-users who access their data in datasheet view, but the 
many, many problems that are due to lookup fields that are posted here in 
the newsgroups have confirmed to me that they are _more trouble than they 
are worth_.

A lookup field obscures what is actually stored by displaying something 
else, but the user will be in for a surprise when they use it in a query... 
instead of the name or description they are used to seeing, they will only 
see a number (the number identifying the lookup record containing the data 
displayed in datasheet view).

If you'd clarify in detail the circumstances under which you "try to put the 
cursor in the field"... is that in datasheet view, or in a Form, or ???, and 
the exact number and wording of the error message, someone might be able to 
offer a useful suggestion.

 Larry Linson
 Microsoft Office Access MVP


"Publishing vs. Converting" 
<PublishingvsConverting@discussions.microsoft.com> wrote in message 
news:9A576E27-608F-491F-8576-99C24141E678@microsoft.com...
>I am new at Access and don't really know what I am doing.  I created two
> tables... 1. contact information 2. subcontractors used at job sites.  I
> created a lookup field for the subcontractors so when I create reports or
> queries I can connect the subcontractors with their contact information. 
> I
> set the field to text, but everytime I try to put the cursor in the field 
> it
> comes up with an error message saying that the value isn't valid for the
> field.  How do I get it to stop doing this? 


0
Larry
2/20/2008 9:38:51 PM
I am in datasheet view and whether I click within the field or tab over to it 
I receive three error messages in a row stating "The value you entered is not 
valid for this field.  For example, you may have entered text in a numeric 
fields or a number that is larger than the FieldSize permits."  The only 
thing is that I have set the field to be Text.  And after getting the cursor 
into the field, the first three letters that I type are interupted by this 
same error four times.  Then it works fine and accepts the entry I have 
placed into the field.  I just want these errors to stop inturupting the 
process.

"Larry Linson" wrote:

> Your first problem was defining a "lookup field" -- I'm sure Microsoft was 
> trying to help end-users who access their data in datasheet view, but the 
> many, many problems that are due to lookup fields that are posted here in 
> the newsgroups have confirmed to me that they are _more trouble than they 
> are worth_.
> 
> A lookup field obscures what is actually stored by displaying something 
> else, but the user will be in for a surprise when they use it in a query... 
> instead of the name or description they are used to seeing, they will only 
> see a number (the number identifying the lookup record containing the data 
> displayed in datasheet view).
> 
> If you'd clarify in detail the circumstances under which you "try to put the 
> cursor in the field"... is that in datasheet view, or in a Form, or ???, and 
> the exact number and wording of the error message, someone might be able to 
> offer a useful suggestion.
> 
>  Larry Linson
>  Microsoft Office Access MVP
> 
> 
> "Publishing vs. Converting" 
> <PublishingvsConverting@discussions.microsoft.com> wrote in message 
> news:9A576E27-608F-491F-8576-99C24141E678@microsoft.com...
> >I am new at Access and don't really know what I am doing.  I created two
> > tables... 1. contact information 2. subcontractors used at job sites.  I
> > created a lookup field for the subcontractors so when I create reports or
> > queries I can connect the subcontractors with their contact information. 
> > I
> > set the field to text, but everytime I try to put the cursor in the field 
> > it
> > comes up with an error message saying that the value isn't valid for the
> > field.  How do I get it to stop doing this? 
> 
> 
> 
0
Utf
2/21/2008 2:15:01 PM
Reply:

Similar Artilces:

Trouble with hyhens within text when using LOOKUP
I have two columns, each containing a list of part numbers. Some of the part numbers contain hyphens. I am using LOOKUP and/or VLOOKUP to determine if the value in one column exists in the other. This works great on non-hyphenated part numbers. However, it will not find or return the hyphenated part numbers from the specified arrays. As a test, I did a quick if statement to compare the instances of identical hyphenated values that exist in both columns. Those statements did not have a problem with the hyphens. Can anyone offer any help? If hyphens cannot be used in conjunction with the ...

Assign values to both A2 and A3
Using =If((A3=A2),"X","O") It assigns "X" for the A2 and and "O" for the A3. How can get it to also assign an "X" for the A3? Thanks Mickie Mickie Not quite with you. That formula will assign an X in the *cell it is in* if A3=A2, else it will assign a O in that cell BTW: parenthesis are not required around the A3=A2 ;-) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk "mickie" <lvrr@adelphia.net> wrote in message news:1165463099.250719.17...

instructions disppear when users begin type (text field)
Hi all, I need to customize the outlook contact form and I want to add one text field to allow users to add details info and instruct users how to add. Instructions shows in the field and the instructions disappear when users click and begin to type. How should I do this? exchange 2003/outlook2003 Thank you. It's hard for me to visualize exactly what you're expecting to happen. If you want the instructions to stay on the screen, you could display them in a label control. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumps...

Updating Boolean Field with onChange
I am having difficulty with setting a value of a new boolean field using the OnChange scripting against a picklist. If anyone has some sample code / formatting information that I can use it would be greatly appreciated. Also, I also need to know if it is possible to stop the default value for the Revenue (isrevenuesystemcalculated) field on the opportunity form being set to "Yes" when the "Convert Lead" function is used. We can set the default value to "No" for user created Opportunities but when Convert Lead is used it seems to ignore the default value. Again, a...

When hitting reply to a message, no "Bcc:" header field appears
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange Hi. When I hit &quot;Reply&quot; or &quot;Reply All&quot; to a message in Entourage, the composition window that appears has the usual &quot;To:&quot;, &quot;Cc:&quot;, and &quot;Subject:&quot; form fields near the top, but not one for &quot;Bcc:&quot;. I've looked all over Preferences but don't see an option for this. What am I missing? <br><br>Thanks, <br><br>-c On 2/26/10 11:51 AM, cmetzler@officeforma...

Summing Values using multiple criertia
Does anybody know a formula I could use to sum a range of values based on multiple criertia? Example: Division Type Wage Bulk Driver 200.00 Bulk Admin 400.00 General Admin 500.00 Bulk Driver 100.00 I want to sum the wages for Divison "Bulk" & Type "Driver". How can I do this??? Thanks! Jane =SUMPRODUCT((A2:A4="Bulk")*(B2:B4="Driver")*(C2:C4)) If there are lots of such totals, you may want to consider a pivot table rather than formulas. On Mon, 27 Sep 2004 20:14:27 -0700, "Jane" <anonymous@dis...

Chart to show Portfolio Value over time?
I'm using Money 2003. I would like to be able to see the $ value I have in my portfolio over time. So that I can weep. Money does have a chart view that allows you to see the PRICE history for a given stock over time - but not the dollar value of your investment in the stock over time. In fact, I can't seem to find a view at all that shows you the net value of your portfolio/individual stocks changing over time. The best I've been able to do is to use the "Net Worth" report and unselect all the other accounts. This has insufficient granularity (months instea...

how do i change the default value of measure from points to inche.
how do i change the default value of measure from points to inches when setting the width and hight of cells? You don't. Excel uses only points for these measures best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "yoyo4u" <yoyo4u@discussions.microsoft.com> wrote in message news:33420157-6E05-4A55-9003-088D731E495E@microsoft.com... > how do i change the default value of measure from points to inches when > setting the width and hight of cells? yoyo Row heights are measured in points. There are 72 points to an inch. Th...

Fields
I am working in an education template and want to see if there is a way to create a field that can be added to periodically to show all the courses a person takes from my instructors. I created a field named course but it is only allowing me to put one course in. How can I make the field list all the courses? Thanks, Nantika -- Enjoy every day! On Sat, 17 Apr 2010 09:18:01 -0700, Nantika <Nantika@discussions.microsoft.com> wrote: >I am working in an education template and want to see if there is a way to >create a field that can be added to periodically ...

Insert empty numeric value
Dear all, In VB, I have three textbox which are amount1,amount2 and amount3. After user enter the value in the textbox, I will insert the value into Access table. The table have three columns amount1 , amount2 and amount3, and all are nummeric Type. However, if the user do not enter any value in textbox . The insert statement will become as follows: Insert into table1 (amount1,amount2,amount3) values (,,) Then access complain that there is syntax error in insert statement. Does that mean I cannot insert empty value for the numeric value in access.? How to solve this problem. Than...

Subtracting value from main form
I have a borrow module which will alow user to return item separately. So, I have get the structure of returning it separately. In my main form is the borrowing item, with the loaned quantity and the owed quantity (will be calculated). In the subform, there is the returning transaction. User will need to key in the quantity returned and it will be automatically deducted from the quantity owed. But how am I supposed to get the quantity deducted while it 1 is in main form and the other is in subform? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-fo...

can lookup return cell reference istead of "text" for sumif?
I am trying to use a lookup-function to determine a different sum range for several criteria. Like so: =Sumif($A$7:$A$1447;"<"&X3;vlookup(e3;AT3:AU11;2;false)-Sumif($A$7:$A $1447;"<"&y3;vlookup(e3;AT3:AU11;2;false) The problem is that the vlookup returns text and not the cell reference. Is there a way to get the answer from the lookup expressed as cell reference instead of text, since sumif can't use text, just the cell reference? I use it to calculate the number of hours the staff should be paid, so it's different from weekdays to saturdays, holidays...

How do I find a value on a line?
I made a line graph of data to use as a calibration. I know the y value and I want to find the X value. Is there a way that I find find this specifically on the line without using a trendline formula or guessing by looking at the gridlines? The only way to find a specific value is to use the formula. Rgs, Bou If you can accept piece-wise linear interpolation, see Interactive Chart http://www.tushar- mehta.com/excel/software/interactive_chart_display/index.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity s...

Default value for custom field?
How can I populate a custom field with a default value? Specifically, I have a custom field "DisplayName" associated with the Quote Detail object. I want initially to populate this field with the value of the product name field when a new product is added to a quote. The user can then edit the DisplayName custom field if desired. The DisplayName custom field will be used as the product name on a Crystal Reports quote form. ...

Replace null value with the previous value?
I have a database that was just imported that has approximately 388000 records. The problem is that there is information about a person in multiple different records but the name did not come across with each record. (So I have 10 records with information for a certain name, but the name only appears in field 1 of the first record and not the subsequent 9, etc.) I need to create a query or expression that will fill field 1 with the preceding value if it is null. This way I will have all the information for field 1 in a manner that I can link and combine data. Simply I need to fil...

modify description field in reports
When I print my PO's I can make the part # field in the view report screen wider but when i print it, it defaults back to the regular size. The problem is it cuts off my part numbers on some of my long numbers. Is there a way to overcome this? you must modify the po.xml file directly. "www.choppersapparel.com" <www.choppersapparel.com@discussions.microsoft.com> wrote in message news:8B4E31D5-10A7-4A99-913D-F8F6E3BD7043@microsoft.com... > When I print my PO's I can make the part # field in the view report screen > wider but when i print it, it defaults ...

How do you change a field name in 2002 Excel
I have copied and pasted a whole database from Works into the Excel program but can't seem to find a way to change the field names from A, B, C, to what I want as Last Names, First Names, etc. Aarrrrgh. It can't be THAT difficult! <G> Using the HELP did nothing for me thus I am here asking this silly Q. AnnE in MN You cannot change the Column letters from A, B, C etc. You can choose to have column and row headers not shown under Tools>Options>View Enter your titles(names) in row 1 then select A2 and Window>Freeze Panes to lock row 1 in view. Gord Di...

Multiple indexes on same fields in different order
I sometimes need to find the largest or smallest value in a field. If I put two indexes on the same field, one in ascending order and one in descending order, is Jet smart enough to use the proper index if a give it a grouping query with the condition Max or Min, or am I just wasting resources? Pete -- This e-mail address is fake, to keep spammers and their address harvesters out of my hair. If you want to get in touch personally, I am 'pdanes' and I use yahoo mail. But please use the newsgroup when possible, so that all may benefit from the exchange of ideas. ...

Data types.
Hello, I have TextBox1 with this Value = 57.7 I need do something like this MyNewVar = CDbl(TextBox1) .... but this is Type mismatch error 13 I need transform text value to number value. Thanks Tom I found the problem. I need transform 57.7 to 57,7 "Tom" <tom@fake.com> p�e v diskusn�m p��sp�vku news:ezqjoGcLEHA.2260@TK2MSFTNGP09.phx.gbl... > Hello, > > I have TextBox1 with this Value = 57.7 > I need do something like this > > MyNewVar = CDbl(TextBox1) .... but this is Type mismatch error 13 > > I need transform text value to number value. > &g...

Calculating values for empty cells.
Hello. I have a very simple problem that I cannot find the answer to. I have data in two columns, some of the data in one of the columns is missing and I want to automatically extrapolate what the data should be based on the trend. How can I get Excel to fill in empty values without overwriting the known values. Below is a sample of my data. 1500 1600 1700 1800 4000 1887 5700 1900 5500 1910 7300 1912 8100 1920 8800 1926 10100 1930 11900 1936 12200 1938 -- Ryan Taylor rtaylor@stgeorgeconsulting.com Not sure what yo...

Limit value for Storage Groups
Hello, we are running Exchange 2003 Standard edition. The limitation on Storage Groups for e.g. "Issue warning at(KB)" is default set by 2097151. I read that i can change this value by using ADSI Edit and edit the values mDBOverHardQuotaLimit mDBOverQuotaLimit mDBStorageQuota But each of them has a lots of values and i could not find out which one i have to edit. Could you please give me an advice? TIA helpi If you want to set a value bigger than 2GB for all users on an entire mailbox store, bring up the properties of that mailbox store in ADSIEdit and set the mDBStorageQuot...

Pivot table field dropdown list reset #2
I would like to know if there is a way to reset a field dropdown list in a pivot table. In some field sometimes I delete data from the source and some items of the field will no longer be there. However, when I refresh the pivot table the dropdown list will still show the old items even if they're not in the source data anymore. I really need your help with this. Thanks a lot in advance for your help, Francisco Molina ...

Select more than 1 object in the lookup field
I would like to have the ability to select more than one object in a lookup field. Is there a way to accomplish this? Thanks No. The only exception to this is the party list (like the to, cc and bcc fields in an email), but this is an internal thing and cannot be used in customizations. -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup Dialog: http://www.stunnware.com/crm2/?area=customLookup ---------------------------------------------------------- "Onetreeup" <Onetreeup@discussions...

Extract Values from a Column
I have a worksheet used to track time spent on various jobs. One column is for the JOB # while others are for descriptions, etc. I would like to have formulas or possible a macro (if necessary) to sum the time spent on different jobs. For example, say (for simplicity) that each row is equal to 1 unit of time. Then in this row I put 342 for JOB # 342. After the entire day I have worked on say 5 different jobs. I would like to have a cell that says "Total time spent on Job # 342" and then next to it a formula that would look at the column and count all the values that are equal t...

Value shading map issue
I am creating a map in Excel using MS Map to show a US map. When using the value shading function, I can't figure out how to keep the states that have no values blank. What happens is the key shows a range of values (0- 10, 11-20). I need the states that have NO value to have their own color (or lack of color) and I am not sure how to do this... Any suggestions? Chris In your data, you could calculate the state name in a new column, and use the calculated name in the map. For example, if state names are in column D, and values are in column E: =IF(E2=0,"",D2) Th...