call function in query

[Guest]

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

 

[Allen Browne]

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.

 

[Larry Daugherty]

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-
--

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Guest]

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 (

!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

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

Reply to group, rather than allenbrowne at mvps dot org.

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