Fetch Question 
Author Message
 Fetch Question

CW5.5EE ABC

Is there a way to determine if more than one records is returned as a result
of a Fetch

i.e.

Field = 'Data'
Access:Table.Fetch(KeyField)
IF ERRORCODE() = 35
    !No Records Found
ELSE
    ??????
    !More than records found
        ELSE
            !Only one records found
        END
    END
END

TIA

Tim Morrison



Sun, 22 May 2005 06:03:06 GMT  
 Fetch Question
On Tue, 3 Dec 2002 16:03:06 -0600, "Tim Morrison"

Quote:

>Is there a way to determine if more than one records is returned as a result
>of a Fetch

Fetch can only retrieve a single record, so I'm not sure what you are
asking.  Unless you are wondering if more than one record exists with
the specified key value?  In that case you will have to use SET->NEXT
to loop through the records.  (You really should avoid using Fetch
(GET) on any key that allows duplicates, as you have no control over
what record, if any, will be retrieved.)

Quote:
>Access:Table.Fetch(KeyField)
>IF ERRORCODE() = 35

Also, in the above example code, you cannot test for ERRORCODE() after
using a FileManager command, as it will likely be set to zero.

Jason



Sun, 22 May 2005 06:39:52 GMT  
 Fetch Question
Currently I use this method in areas where there is a unique key.  I am now
needing to do this for a key that MAY not be unique.

Access:Table.Fetch(KeyField)
IF ERRORCODE() = 35

This works just fine to test if a particular key exists.

I want to avoid doing a loop as there may be millions of records to loop
thru just to find out if there is more than one value.

I suppose I could:

Loc:Count = 0              !BYTE
Loc:Records = 0          !BYTE
SET(KeyField)
LOOP
NEXT(Table)
IF ERRORCODE() THEN BREAK.
IF Loc:Records >= 2 THEN BREAK.
Loc:Records = Loc:Records + 1
IF DAT:FIELD = 'Data'
    Loc:Count = Loc:Count + 1
    IF Loc:Count >= 2 THEN BREAK.
END
END
IF Loc:Records = 2 AND Loc:Count = 2
    !There are more that one record with the same key value
END
IF Loc:Count = 1
    !There is only one record with the defined value.
END

What do you think? This would eliminate the need to loop thru more than 2
records to determine if there are more than a single value is a particular
key present.

Tim Morrison


Quote:
> On Tue, 3 Dec 2002 16:03:06 -0600, "Tim Morrison"

> >Is there a way to determine if more than one records is returned as a
result
> >of a Fetch

> Fetch can only retrieve a single record, so I'm not sure what you are
> asking.  Unless you are wondering if more than one record exists with
> the specified key value?  In that case you will have to use SET->NEXT
> to loop through the records.  (You really should avoid using Fetch
> (GET) on any key that allows duplicates, as you have no control over
> what record, if any, will be retrieved.)

> >Access:Table.Fetch(KeyField)
> >IF ERRORCODE() = 35

> Also, in the above example code, you cannot test for ERRORCODE() after
> using a FileManager command, as it will likely be set to zero.

> Jason



Sun, 22 May 2005 06:52:33 GMT  
 Fetch Question
I use mike hanson's view class and with it do this type of stuff all the
time...

You could handcode if you like

The basic "trick" is in the filter statement...

ViewFilter('FieldToCheck = ' & ValueToCheck & ' AND SysIDField NOT = ' &
CurrentRecordSysIDField)

IF View.Next()
 No Duplicate
ELSE
 DUPLICATE
END

Where SysIDField is the Primary Unique "Autonumber" field of the file (Or
orther Unique Key)

Kevin


Quote:
> Currently I use this method in areas where there is a unique key.  I am
now
> needing to do this for a key that MAY not be unique.

> Access:Table.Fetch(KeyField)
> IF ERRORCODE() = 35

> This works just fine to test if a particular key exists.

> I want to avoid doing a loop as there may be millions of records to loop
> thru just to find out if there is more than one value.

> I suppose I could:

> Loc:Count = 0              !BYTE
> Loc:Records = 0          !BYTE
> SET(KeyField)
> LOOP
> NEXT(Table)
> IF ERRORCODE() THEN BREAK.
> IF Loc:Records >= 2 THEN BREAK.
> Loc:Records = Loc:Records + 1
> IF DAT:FIELD = 'Data'
>     Loc:Count = Loc:Count + 1
>     IF Loc:Count >= 2 THEN BREAK.
> END
> END
> IF Loc:Records = 2 AND Loc:Count = 2
>     !There are more that one record with the same key value
> END
> IF Loc:Count = 1
>     !There is only one record with the defined value.
> END

> What do you think? This would eliminate the need to loop thru more than 2
> records to determine if there are more than a single value is a particular
> key present.

> Tim Morrison



> > On Tue, 3 Dec 2002 16:03:06 -0600, "Tim Morrison"

> > >Is there a way to determine if more than one records is returned as a
> result
> > >of a Fetch

> > Fetch can only retrieve a single record, so I'm not sure what you are
> > asking.  Unless you are wondering if more than one record exists with
> > the specified key value?  In that case you will have to use SET->NEXT
> > to loop through the records.  (You really should avoid using Fetch
> > (GET) on any key that allows duplicates, as you have no control over
> > what record, if any, will be retrieved.)

> > >Access:Table.Fetch(KeyField)
> > >IF ERRORCODE() = 35

> > Also, in the above example code, you cannot test for ERRORCODE() after
> > using a FileManager command, as it will likely be set to zero.

> > Jason



Sun, 22 May 2005 07:07:24 GMT  
 Fetch Question

Quote:
> Field = 'Data'
> Access:Table.Fetch(KeyField)
> IF ERRORCODE() = 35
>     !No Records Found
> ELSE

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
             SET(KEY)
             NEXT(Table)
             NEXT(Table)
             IF ! THE FIELDS STILL = THE FIELDS FROM YOUR FETCH
                !More than records found
             ELSE
Quote:
>              !Only one records found
>           END
> END

> TIA

> Tim Morrison



Sun, 22 May 2005 16:37:48 GMT  
 Fetch Question
On Tue, 3 Dec 2002 16:52:33 -0600, "Tim Morrison"

Quote:

>Currently I use this method in areas where there is a unique key.  I am now
>needing to do this for a key that MAY not be unique.

>Access:Table.Fetch(KeyField)
>IF ERRORCODE() = 35

>This works just fine to test if a particular key exists.

I guess it does, according to the help file.  I seem to remember
having problems doing a GET on a non-unique key, but that would have
been in CPD (and I could be mistaken).  I have always avoided doing
that, because you almost always want to get a specific record, and on
a non-unique key, you cannot GET a specific record - you just get one
of the records with a matching key value.

Quote:
>I want to avoid doing a loop as there may be millions of records to loop
>thru just to find out if there is more than one value.

In that case, you only have to loop twice, as you have noted in your
code.  However, you can make it cleaner using LOOP 2 TIMES, and I will
also note that SET(Key) is not the same as SET(Key, Key).

CLEAR(Count)
CLEAR(File)
KeyField = PrimeValue
SET(Key, Key)
LOOP 2 TIMES
   NEXT(File)
   IF ERRORCODE() = 0
      IF KeyField = PrimeValue
         Count += 1
      END
   END
END

CASE Count
OF 0
   ! No matching values
OF 1
   ! 1 matching value
OF 2
   ! 2 matching values
END

Quote:
>What do you think? This would eliminate the need to loop thru more than 2
>records to determine if there are more than a single value is a particular
>key present.

Yup.

Jason



Sun, 22 May 2005 23:16:14 GMT  
 Fetch Question
Thank you.

I will end up using this quite a bit.

Tim Morrison


Quote:
> On Tue, 3 Dec 2002 16:52:33 -0600, "Tim Morrison"

> >Currently I use this method in areas where there is a unique key.  I am
now
> >needing to do this for a key that MAY not be unique.

> >Access:Table.Fetch(KeyField)
> >IF ERRORCODE() = 35

> >This works just fine to test if a particular key exists.

> I guess it does, according to the help file.  I seem to remember
> having problems doing a GET on a non-unique key, but that would have
> been in CPD (and I could be mistaken).  I have always avoided doing
> that, because you almost always want to get a specific record, and on
> a non-unique key, you cannot GET a specific record - you just get one
> of the records with a matching key value.

> >I want to avoid doing a loop as there may be millions of records to loop
> >thru just to find out if there is more than one value.

> In that case, you only have to loop twice, as you have noted in your
> code.  However, you can make it cleaner using LOOP 2 TIMES, and I will
> also note that SET(Key) is not the same as SET(Key, Key).

> CLEAR(Count)
> CLEAR(File)
> KeyField = PrimeValue
> SET(Key, Key)
> LOOP 2 TIMES
>    NEXT(File)
>    IF ERRORCODE() = 0
>       IF KeyField = PrimeValue
>          Count += 1
>       END
>    END
> END

> CASE Count
> OF 0
>    ! No matching values
> OF 1
>    ! 1 matching value
> OF 2
>    ! 2 matching values
> END

> >What do you think? This would eliminate the need to loop thru more than 2
> >records to determine if there are more than a single value is a
particular
> >key present.

> Yup.

> Jason



Mon, 23 May 2005 00:41:56 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Question about Access:File.fetch(Key) ...

2. Fetching HTML Pages with Socket Control

3. Fetch Returns Record Not Found... revisited

4. Problem with fetch after insert

5. Fetch Not Working (C5EE & ABC)

6. How to check for error on a FETCH

7. HELP can't perform FETCH

8. HELP: Keeping entries in record buffer after a fetch

9. Fetch error, please help

10. Fetch the Address book from exchange server ...

11. Fetch a record

12. GET vs. FETCH

 

 
Powered by phpBB® Forum Software