How to limit number of records?

[Guest]

Hi there,
Can you help with something.
I've got to create files to export data to a mainframe system and one of the
limits of the file spec is that the file cannot contain more than 99 client
records - the number of records is stated within the batch header and there
are only 2 digits available for this.
Can you perhaps advise me how I might do this either via SQL or via the
Query Design Grid?
Many thanks for any advice or help you can give.
Regards,
Lee

 

[John Vinson]

On Wed, 13 Oct 2004 10:19:03 -0700, "Baby Face Lee" <Baby Face

Hi there,
Can you help with something.
I've got to create files to export data to a mainframe system and one of the
limits of the file spec is that the file cannot contain more than 99 client
records - the number of records is stated within the batch header and there
are only 2 digits available for this.
Can you perhaps advise me how I might do this either via SQL or via the
Query Design Grid?
Many thanks for any advice or help you can give.
Regards,
Lee

Depends on the table structure. Do you have a field in the table with
this 1 to 99 value? If so, you could use a Validation Rule

0 AND < 100

though the error message might be irksome.

How are you creating the files? From what source? Are you using a
Select query? If so, look at the TOP VALUES property of the query -
set it to 99. How you'll handle cases where there are over 99 client
records (do you just throw out the surplus?) is up to you...


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps