Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automate Data based on middle number (2000)

    Hi,
    I have a PR# that has XX-XX-XXXX. The middle 2 numbers identify which sales guy made the sale. I'd like the sales guy name to appear in another text field once the PR# is entered. The name can be 2 different fields. I know how to point fields like that based on the whole field but not the 2 middle numbers. I have a table already created with all the sales people data plus that ID#. All of this is on a form.
    Thanks,
    Deb

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate Data based on middle number (2000)

    What do you mean by "The name can be 2 different fields"?

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate Data based on middle number (2000)

    Well right now I have the last name as a combo box that the user selects and then another field with the first name, title, phone ext. that automatically fill in based on the name that is selected from the combo box. Bascially I'd like the same thing to happen but now based on the middle 2 numbers and not have the combo box any longer.
    thanks
    deb

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate Data based on middle number (2000)

    One way would be to split the XX-XX-XXXX into three fields. You would use a combo box to select the middle one, and the other fields would be filled in automatically from the combo box, more or less as you have now.

    Another way would be a query based on the table, with a calculated field that selects the middle XX and converts it to a number:

    SalesGuyID: Val(Mid(Nz([PRfield]),4,2))

    You can use this value to retrieve the other data.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate Data based on middle number (2000)

    I'm intrigued by the query method, but let me clarify some things. In the tblCARList I have the following fields: lngCARID, FN, LN, Crew, JobTitle, PhoneExt, PRID. The PRID just has the 2 number identification number. On another form Purchase Request they have to type in a Purchase Request Number XX-XX-XXXX. The middle 2 is the PRID that identifies the CAR (customer account rep). I tried your query suggestion but I'm really not sure where to start. Can you give a little more detail.
    Thanks,
    Deb

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate Data based on middle number (2000)

    See attached example.
    Attached Files Attached Files

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate Data based on middle number (2000)

    Took me a little bit but I finally got it to work. Now my problem is that I also need that data to print on a report but can't get to show up. From the report I was pointing to the subform!field, but it just gives me a #Name error. Would I need to do the same thing as far as a subform for the report as well?
    Thanks
    Deb

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate Data based on middle number (2000)

    I created the same type of subreports on my reports and it works GREAT! Thanks. I just toasted you as I sit here on a Friday night of a holiday weekend sipping a Mike's Cranberry Lemonade working on this stinkin' database. Time for fun. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>
    Thanks,
    Deb

Posting Permissions

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