dear friends
i want an query function to have serial numbers between two pre said numbers
like between 100 and 105 = 101,102,103,104,105.thus i created an function
in standard access module .
public function _ foils(firstfoil as int ,lastfoil as int) as integer
for foils = firstfoil TO lastfoil step 1
next foils
end function
IN query window it appeared as foils( <firstfoil>,<lastfoil>)
while accessing and running on QBE window and while runnig query
error accoring as "undefined function "foils" in qbe
how to build query functions and use it please
with regards

Rather than call a function, a better solution would be to create a table
containing all the possible values, and use a Cartesian Product query to get
a record for each number.

This article explains how to build such a table, and populate it
programmatically so you do not need to type all the possible values.

Once you have the table, you can:
1. Create a query using your existing table, and tblCount. There must be no
line joining the 2 tables in the upper pane of query design.

2. Add the CountID field from tblCount to the grid, and enter this
expression in the Criteria row under this field:
Between [firstfoil] And [lastfoil]
using your field names in place of "firstfoil" and "lastfoil".

The query generates a row for each serial number.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"balu" <(E-Mail Removed)> wrote in message
news:8E26660F-FC82-4DF0-9F0F-(E-Mail Removed)...
> dear friends
> i want an query function to have serial numbers between two pre said
> numbers
> like between 100 and 105 = 101,102,103,104,105.thus i created an function
> in standard access module .
> public function _ foils(firstfoil as int ,lastfoil as int) as integer
> for foils = firstfoil TO lastfoil step 1
> next foils
> end function
> IN query window it appeared as foils( <firstfoil>,<lastfoil>)
> while accessing and running on QBE window and while runnig query
> error accoring as "undefined function "foils" in qbe
> how to build query functions and use it please
> with regards

Hi Allen,

I read most of your posts with interest and almost always accept the
wisdom you impart. Thanks for your terrific contributions.

I tremble to offer a counter suggestion an algorithm supported by the
likes of you and John Vinson; two of my favorite Access authorities
and resources. There are issues to which your and John's solution is,
in all likelihood, the best.

Here I have to suggest a different solution for OP. It may suit the
needs of this poster and others. Rather than creating a special
purpose table and then using it in arcane ways, create an intermediate
form, named appropriately for its purpose. Put a couple of unbound
text boxes, appropriately named and labeled for their purposes:
~Beginning Serial # & ~Ending Serial # Add a command button to
launch the Query or Report with the appropriate view or action. In
the criteria line for the targeted query, whether named or embedded in
the Report, put the statement
Between [forms!frmMyLauncher!txtBeginSerial#] and
[forms!frmMyLauncher!txtBeginSerial#]

HTH
--
-Larry-
--

"Allen Browne" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Rather than call a function, a better solution would be to create a
table
> containing all the possible values, and use a Cartesian Product
query to get
> a record for each number.
>
> This article explains how to build such a table, and populate it
> programmatically so you do not need to type all the possible values.
>
> Once you have the table, you can:
> 1. Create a query using your existing table, and tblCount. There
must be no
> line joining the 2 tables in the upper pane of query design.
>
> 2. Add the CountID field from tblCount to the grid, and enter this
> expression in the Criteria row under this field:
> Between [firstfoil] And [lastfoil]
> using your field names in place of "firstfoil" and "lastfoil".
>
> The query generates a row for each serial number.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "balu" <(E-Mail Removed)> wrote in message
> news:8E26660F-FC82-4DF0-9F0F-(E-Mail Removed)...
> > dear friends
> > i want an query function to have serial numbers between two pre
said
> > numbers
> > like between 100 and 105 = 101,102,103,104,105.thus i created an
function
> > in standard access module .
> > public function _ foils(firstfoil as int ,lastfoil as int) as
integer
> > for foils = firstfoil TO lastfoil step 1
> > next foils
> > end function
> > IN query window it appeared as foils( <firstfoil>,<lastfoil>)
> > while accessing and running on QBE window and while runnig query
> > error accoring as "undefined function "foils" in qbe
> > how to build query functions and use it please
> > with regards
>

dear sir,
i used cartesian query which worked great but the problm is .
my table is like this
table [booksreceived]
filds booknumber number (Pk)
" foilnumberfrom number
" foilnumber to number
table2 [membershipcollection]
field [membername] text
" [booknumber] number (Fk)
" [foilnumber] number (Pk),(combobox on form with cartesian
query)
" [amount] currency
on the [membershipcollection] form as and whenever the booknumber changes
the foil numbers between ([table]!booksreceived.foilnumberfrom and
,,foilnumberto.
is gtting fired but it just remains in the first fired book number only
dear sir how to come over such probloms




"Allen Browne" wrote:

> Rather than call a function, a better solution would be to create a table
> containing all the possible values, and use a Cartesian Product query to get
> a record for each number.
>
> This article explains how to build such a table, and populate it
> programmatically so you do not need to type all the possible values.
>
> Once you have the table, you can:
> 1. Create a query using your existing table, and tblCount. There must be no
> line joining the 2 tables in the upper pane of query design.
>
> 2. Add the CountID field from tblCount to the grid, and enter this
> expression in the Criteria row under this field:
> Between [firstfoil] And [lastfoil]
> using your field names in place of "firstfoil" and "lastfoil".
>
> The query generates a row for each serial number.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "balu" <(E-Mail Removed)> wrote in message
> news:8E26660F-FC82-4DF0-9F0F-(E-Mail Removed)...
> > dear friends
> > i want an query function to have serial numbers between two pre said
> > numbers
> > like between 100 and 105 = 101,102,103,104,105.thus i created an function
> > in standard access module .
> > public function _ foils(firstfoil as int ,lastfoil as int) as integer
> > for foils = firstfoil TO lastfoil step 1
> > next foils
> > end function
> > IN query window it appeared as foils( <firstfoil>,<lastfoil>)
> > while accessing and running on QBE window and while runnig query
> > error accoring as "undefined function "foils" in qbe
> > how to build query functions and use it please
> > with regards
>
>