Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Very important question (Access2000)

    I have broker information in a table which is linked to two main tables that store data entered by the user. The user sometimes enters the broker code as SSB_BCP or SSB_BCP_, when I extract data for reports it is not extracting few records because of this.

    I want to add SSB_BCP in a column and add another column next to it with SSB_BCP_ and have somekind of formulae (or link) to let access know that it sould look for broker code in both columns.

    Is this possible? If yes how?
    Don't tell me my designs are poor coz I did NOT design this program, a professional of some kind did the honour of designing and creating the program.

    Thanx

  2. #2
    Lounger
    Join Date
    Jun 2001
    Location
    Syracuse, NY
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very important question (Access2000)

    So if I understand correctly, your saying this is a input error?

    You should be validating the broker code at time of input rather then trying to fix the problem of a incorrect code later on.

    Jim.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very important question (Access2000)

    Like I said I did not deign this program and I cannot go change the design coz its locked. I can't change the records coz theres more than 4000 records. I can ONLY retrive them,

    So if you could help me PLEASEEEEEEEEEE DO.
    Thanx

  4. #4
    Lounger
    Join Date
    Jun 2001
    Location
    Syracuse, NY
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very important question (Access2000)

    If you unwilling/cannot change the design, then fixing up the data is the only solution. Write an update query to look for the extra character:

    ="SSB_BCP_"

    and update to:

    "SSB_BCP"

    Of course that does nothing for fixing the problem itself as it can easily happen again.

    Jim.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very important question (Access2000)

    I was reading about update queries and I don't think I could use this to do what I want to do coz there are more than one broker code that I want to update, and there are more than 4000 records to be updated.

    Do u think its possible?

  6. #6
    Lounger
    Join Date
    Jun 2001
    Location
    Syracuse, NY
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very important question (Access2000)

    You can either write multiple queries or you can call a function in the query and supply the right code. For example, the update to would be defined as:

    =GetNewCode([BrokerCode])

    and the function:

    Public Function GetNewCode(strBrokerCode as string) as string

    Select Case strBrokerCode

    Case "AAAAAAA_"

    Case "BBBBBB_"

    End Select

    That's one way to handle it.

    I don't have enough info to give you other alternatives. For example, if their all the same length, then you could just check for the extra character on the end and strip it off.

    or is it always a "_" on the end that's the problem? If so, you could perform a criteria check on that and strip off the extra character.

    The function though is a way to handle the translation of the codes on a case by case basis.

    Jim.

  7. #7
    Lounger
    Join Date
    Jun 2001
    Location
    Syracuse, NY
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very important question (Access2000)

    You'll have to type a few examples. I don't see any files attached to your last message and I wouldn't know how to get them anyway as I rarely come here.

    Jim.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very important question (Access2000)

    there was no attachment on your last message

  9. #9
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very important question (Access2000)

    I've attached an spreadsheet with this reply it has two columns, the first one contains some of the broker code that is stored in the tables and the 2nd column contains how I want the broker data to be.

    SORRY GUYS, totally forgot about the attachment at the last minute. The attached file is created and used for a combo. Time to time they add new broker data to the attached table.
    Thank you.
    Attached Files Attached Files

  10. #10
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very important question (Access2000)

    Attachment is there, PLEASE help if you can.
    Thank you..

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Very important question (Access2000)

    There are no attachments on your messages, so there isn't any point in insisting it's there. If you attach a file, you can't preview the message or the attachment gets lost.
    Charlotte

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Very important question (Access2000)

    I can see now that it's there, but it did not appear to be a few minutes ago.
    Charlotte

  13. #13
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very important question (Access2000)

    It is there coz I opened it JUST to make sure, but I've attached the file with this reply.
    It's been attached to the 7th post from the top.
    Attached Files Attached Files

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Flims, Switzerland
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very important question (Access2000)

    I would definitely take Jim's approach of updating the records. You can put VBA into your function which simply looks for a trailing '_' and removes it, for example.

    Your data volume presents no problem. 4000 records is a very small number. I deal with databases with millions of records on a daily basis.

  15. #15
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Very important question (Access2000)

    Thanx guys but I've solved the proble by creating an extra table with the codes from the input table and linking that to the table with the codes I wanted and then linking it with the contact details. Then I've created a query and used that with the report queries.

    Its a long mission but I've done it without changing the data in the input tables.
    And about the attachment <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>, <img src=/S/megashout.gif border=0 alt=megashout width=33 height=17> IT WAS THERE ALL ALONG. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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