Results 1 to 6 of 6

Thread: (Access 97)

  1. #1
    New Lounger
    Join Date
    Jun 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    (Access 97)

    I have a database in which an individual site (site_seq_id) demographic data can be used in multiple programs (program_code) with each site having a physical ALL (01) and possible mailing ALL(02)address and a program having a possible specific mailing PROG (02) address. In mailing correspondence to a site the address is chosen in preference of availability of program 02, ALL 02, ALL 01. The SQL plus code is below. How can I do the same thing in Access 97.
    function company_addressFormula return Char is
    temp varchar2(400);
    address_code varchar2(5);
    citystatezip varchar2(100);
    count_all number;
    address1 varchar2(100);
    address2 varchar2(100);
    begin
    select address_type_code into address_code
    from T_COM_SITE_ADDRESS
    where site_seq_id = :site_seq_id
    and address_type_code = '02' and program_code = 'PCO';
    if address_code = '02' then
    select site_street_address1,site_street_address2,
    site_city ||', '|| state_code ||' ' ||site_zip into address1, address2, citystatezip
    from T_COM_SITE_ADDRESS
    where site_seq_id = :site_seq_id and
    address_type_code = '02' and program_code = 'PCO' ;
    if address2 is not null then
    temp := address1 || chr(10) ||address2 ||chr(10) || citystatezip;
    else
    temp := address1 || chr(10) || citystatezip;
    end if;
    end if;
    return temp;
    exception
    when no_data_found then

    select count(*) into count_all
    from T_COM_SITE_ADDRESS
    where site_seq_id = :site_seq_id
    and address_type_code = '02' and program_code = 'ALL';

    if count_all = 0 then
    select site_street_address1,site_street_address2,
    site_city ||', '|| state_code ||' ' ||site_zip into address1, address2, citystatezip
    from T_COM_SITE_ADDRESS
    where site_seq_id = :site_seq_id and
    address_type_code = '01' and program_code = 'ALL' ;
    else
    select site_street_address1,site_street_address2,
    site_city ||', '|| state_code ||' ' ||site_zip into address1, address2, citystatezip
    from T_COM_SITE_ADDRESS
    where site_seq_id = :site_seq_id and
    address_type_code = '02' and program_code = 'ALL' ;
    end if;
    if address2 is not null then
    temp := address1 || chr(10) ||address2 ||chr(10) || citystatezip;
    else
    temp := address1 || chr(10) || citystatezip;
    end if;

    return temp;
    end;

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: (Access 97)

    Could you explain this again? I don't understand the ALL(01), etc.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: (Access 97)

    Is SQL Plus the stored procedure language for Oracle?
    Are you asking how to convert the stored procedure to VBA or how to run it from Access?

    If you're asking us to take a stored procedure written in another language and translate it into functions that will run in Access 97, you're going to have to intrepret a lot of what is going on in this SP. For example, what are the double pipes (||) for and, as Mark asked, what the ALL (nn) represents?

    Can you write some pseudocode for the process this stored procedure represents? Otherwise, only someone who is fluent in both VBA and SQL Plus will be able to tell whether the answers you get will work.
    Charlotte

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: (Access 97)

    Hi Charlotte,
    AFAIK SQL* Plus is a tool that comes with Oracle for development and testing of source code and to allow you to run PL/SQL scripts on a database. The || sign is the Oracle concatenation symbol. Not sure about the ALL(nn) bit though.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: (Access 97)

    Thanks, Rory. I've seen the books around the office, which led me to the Oracle connection, but I've managed to avoid opening them. <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20> Maybe there are some Oracle gurus lurking who will be able to help with this without the need for pseudocode.
    Charlotte

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: (Access 97)

    Hi,
    Are you trying to do this with an Access front-end to your Oracle database or is the data in an Access database file?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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