Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Location
    Plymouth, UK
    Posts
    59

    Unanswered: for specific records enter in a code??

    I have a table with about 50,000 records of which I need to split them by post codes/ zip codes,

    I.E.

    PL1 = EH
    PL2 = EH
    TQ1 = MN

    etc...

    I am not brilliant with if statements I guess this is what you would need, within an append query??

    Please help?

    Regards,

    Dave.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: for specific records enter in a code??

    Originally posted by Davekingwcp
    I have a table with about 50,000 records of which I need to split them by post codes/ zip codes,

    I.E.

    PL1 = EH
    PL2 = EH
    TQ1 = MN

    etc...

    I am not brilliant with if statements I guess this is what you would need, within an append query??

    Please help?

    Regards,

    Dave.
    What are you trying to do? Read them? Write them? Look them up? You're kinda vague ...

  3. #3
    Join Date
    Nov 2003
    Location
    Plymouth, UK
    Posts
    59
    Sorry I need to define between customers in specific areas, so when a query is requested to include only people in the Plymouth area, i.e. only specific postcodes PL1 - PL9 and PL19 - PL21 as the in between postcodes are not plymouth area so the above postcodes need to be tagged as a particular code i.e. EH which is the code for the relevent areas newspaper, rather than the user having to do a between criteria search for each postcode!

    So the ideal would be to update the table and have EH entered to all of the matching fields,

    Please help,

    Regards

    Dave

  4. #4
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    lets clear this up:

    there is a field into which u enter this "EH" into right?
    do you only want to do this for which the area code is PL1-PL9 and PL19-PL21?

    or is there others u have to enter other stuff into?

  5. #5
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    and how comfortable are u with VB code?

  6. #6
    Join Date
    Nov 2003
    Location
    Plymouth, UK
    Posts
    59
    quite comfortable with VB ish!

    There are postcodes which don't match the plymouth ones, PL1-PL9 etc.. these will need a code of MN.

    Cheers!

  7. #7
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    then it should be quite easy!

    Let [PostCode] be the field for you postal codes.
    Let [OtherField] be the field where u enter in that "EH" stuff..

    then using ADO..

    Dim conn as adodb.connection
    Dim rec as adodb.recordset

    set conn = currentproject.connection
    set rec = new adodb.recordset

    rec.Open "SELECT [PostCode], [OtherField] FROM [TableName];", conn, adOpenStatic, adLockOptimistic

    for i = 0 to rec.RecordCount - 1
    if (Left(rec(0), 2))="PL" then
    dim number as integer
    number=Int(Right(rec(0), 1))
    if((number>=1) AND (number<=9)) OR ((number>=19)AND (number<=21)) then
    rec(1).Value = "EH"
    rec.Update
    else
    rec(1).Value = "MN"
    rec.Update
    end if
    end if

    rec.MoveNext
    next

    might be errors...u get the gist

  8. #8
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    you could also use SQL to do this....UPDATE ..... WHERE right(...)

Posting Permissions

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