Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2014
    Posts
    3

    Unanswered: Text is stored in field with as Integer ???

    Hello, I wasn't able to google anything regarding this topic, so here I am

    However I inherited a complex database. I have a table with the field "Name". The field is populated with names like "John Nowak". Surprisingly, the field "Name" is a Number, Long integer, when I'm looking into the design view of the table.

    Now the problem
    - query using a parameter for the field "Name" - not working due to the format
    - combo box in a new form - displaying the name "John Nowak" as 50123 or something like that
    - filter criteria in the query [Name]="John Nowak" - not working
    - trying to convert the field to a text format and text string, all string functions return numbers, like 52143, 45895 etc. instead of names

    I'm trying to create a combo box, where customer would select his name, but I'm not able to do anything with the format. Could you please suggest anything? Is there a way to create a parameter criteria for a query that would take into consideration exactly the string that we see in the field, and not its background format? The parameter "John Nowak" returns no records at the moment.
    I've found tons of "how to convert number to text", but nothing like "how to convert number to text, if the number is actually displayed as text string"

    Thank you.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    A field set as long CANNOT store text. If it is showing this, the table is corrupted.

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If your Field name is actually Name that may be the root of the problem! Name is a Reserved Word, in Access, and they should never be used as the names of anything! And of all the Reserved Words, I can not imagine one that is more likely to confuse the Access Gnomes than Name!

    I doubt seriously that you're going to be able to do any kind of conversion that's going to resolve this. When looking at the Table in Datasheet View, what do you see in the Field?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If it's displaying name but storing number, I'd guess it's a lookup field:

    The Access Web - The Evils of Lookup Fields in Tables
    Paul

  5. #5
    Join Date
    Jul 2014
    Posts
    3
    Thank you for replies.

    To Missinglinq:
    The field name is not a problem, as its exact name is "Customer Name", I'm sorry for the confusion.

    To pbaldy: I checked the link you provided and then the field and - unfortunately - yes, this is the case of a lookup field. It is pulling the names from another query and the field itself is working as a combo box within the table (where you can select whichever name you want).

    So as I understood - there is no way to filter values or use parameter for a lookup field? I'm trying to setup a form, where customer would be prompted to select his name from combo box/parameter box first, so that he can see only those records that are assigned to his name, returned by the query (query which is pulling data from the table where I have the lookup field "Name"). Is there any way how to do this?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a table NOT an Access lookup field. the Lookup field works OK for some tasks. but its really aimed at simple applications. it quickly hits problems such as you have found.


    hence why I strongly recommend that you don't use it
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2014
    Posts
    3
    But -
    I have a table with the lookup field - it is filled with new data whenever customer fills out a form.

    Then I have a table with the list of all customers.

    I cannot match these two tables, as their formats are, of course, Text vs. Number.
    So basically I cannot do anything in order to create a filter based on customer name, which would restrict the list of the table with the lookup field?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes you can
    use a table.....
    do the job properly
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •