Use of Functions in a query

[John Milward]

Hi

I have just discovered the use of functions in the criterea of a query.
This has opened up endless possibilities and two immediate questions

1/ How does one get the function to select everything?
Simple example

Data Select
??? a
?? b

Function returns a, I get record 1 - OK
Function returns b, I get record 2 - OK
How do I get 1 & 2?
(Tried 'Empty', '*', '?')

2/ How do dates work
eg if I want 01/11/02 to 30/11/02 (UK format)
I have tried just sending the string that I could type in the criteria with
the function defined as a string and as a variant - No Joy
Any ideas please

Many thanks

John

[Albert D. Kallal]

In general, the use of a function in a query simply means that some value is
converted to another value during display. You do not "return" records in a
function. Hence, if we had a Name field

select name from tblCustomers

We get (you can view this in the query grid

Name
-------
Albert
John
Susan

If you use a function, you can do neat things like have the name field
converted to upper case. Hence:

select name, UCase([Name]) as UpperName from tblCustomers

If you view the above in the grid, you get:

Name UpperName
------- ----------------
Albert ALBERT
John JOHN
Susan SUSAN

Hence, the use of functions only returns a VALUE for a expression. It does
have some magic quality where it returns records.

As for your date range, just simply use a condition in the query grid:

>= 01/11/02 and <= 30/11/02

When the query works, take a look at the actual sql used. You will notice
that # signs are placed around dates...

--
Albert D. Kallal
Edmonton, Alberta Canada
kal...@msn.com

[John Milward]

Albert
Sorry I did not make myself too clear!
I am talking about VBA functions rather than SQL functions
eg In VBA I have....

Function SelectName() as string
SelectName = MyName
End Function
(Myname is a criteria I wish to pass to the query)

In the Query grid 'Criteria' box for the field with names in I put
SelectName()

When I run the query it uses the contents of MyName as a criteria.

I have this working in a basic manner. The big problem is that whereas for
the things you have described you can look at the SQL, with VBA functions it
either works (If you get it right) or doesn't (If you don't know the correct
syntax)

Any ideas?

John

Albert D. Kallal <kal...@msn.com> wrote in message
news:9W1i9.346147$v53.17...@news3.calgary.shaw.ca...

[Albert D. Kallal]

Ah, ok..I see where we going here.

The real problem here, is lets take the date range example.

In general, in the criteria, the function is only going to return ONE
expression value. Hence, for our example for the date range, we could have.

= SelectDate()

Of course our function SelectDate can really only return ONE value, and thus
we can't get the SelectDate() function to return a date range..can we?

However, it turns out that 9 out of 10 times, we are trying to run a report,
or open a form with some condition. That being the case, I suggest you dump
the idea of using functions in the query conditions all together. I would
use the where clause, and much of ms-access is geared to working with a
where clause anyway.

Lets assume we have a report, and we want to ask the user for a date range,
and perhaps even what salesman. I would simply get the report working, base
it on a query. I would then build nice prompt form, and build the where
clause IN CODE, and pass it to the report.

For example, lets assume we have salesman, and a date range. Our button on
this nice prompt form that launches the report would look like the
following:

dim strWhere as string

' build the date range

strWhere = buildcriteria("SalesDate",dbDate,">= " & me.Startdt & _
" and <= " & me.EndDt)

if isnull(me.ComboSalesMan) = False then
strWhere = strWhere " and SalesManId = " & me.ComboSalesMan
end if

docmd.openreport "myreprot",,,strWhere

Hence, use the where condition, and you can create some *very* nice report
prompt forms. Some examples of this type of prompting can be found at:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

[John Milward]

Right
I am currently doing pretty much what you suggest I think, but placing the
string in the report 'filter' .The only reason I started to look for other
solutions is that speed is becoming an issue. I have linked tables to our
business system, which is large and going to grow (7Gb and 2 years old at
present)
I dont know if using the Where clause is any faster?

Some reports use several queries to get SQL agregate functions to work.
Filtering at the end seems to be much slower than restricting records in the
earlier queries. It may be I have to use a combination of approaches.

Regarding the date example, are you saying that the function must return a
variable of type 'Date'? I had been working on the assumption I could return
a string which contained something like for eaxample ">= #01/01/02#" to just
get this years data.

Thanks for the help so far.

regards

John

Albert D. Kallal <kal...@msn.com> wrote in message

news:s83i9.346636$v53.17...@news3.calgary.shaw.ca...

[Albert D. Kallal]

"John Milward" <john.m...@ipco.co.uk> wrote in message
news:amcbr0$ah6$1...@knossos.btinternet.com...

>
> Regarding the date example, are you saying that the function must return a
> variable of type 'Date'? I had been working on the assumption I could
return
> a string which contained something like for eaxample ">= #01/01/02#" to
just
> get this years data.

You can have the function return either the string, or a date. Ms-access
will convert a date value for you. Hence, I would return a type of date,
since then you do not have to worry about the "format" for a date string,
which is supposed to be always in US format mm/dd/yyyy). However, either way
will work.

As for filter vs "where". gee, I unfortunately don't have any hard numbers.
I always use the where clause when opening a form/report.

However, it sounds like you are using a real database server here, and
moving some of the conditions etc to the server as "views" is going to
dramatically improve performance here.

Perhaps more pass-through queries are in order here?

I would also try dumping conditions in the query, and building the whole sql
string, and stuff that into the reports recordsouce...that can help a lot
here (especially if you are only using a jet, and no server solution here).

If you are using a true database server..then you might want to post another
question on how to speed up reports when using a database server...

[John Milward]

Albert
First many thanks for your continued interest. Most usefull.

Re "server application", "Pass through queries" etc. You are probably
correct. I am probably getting out of my depth a bit(!) I certainly dont
feel happy about writing the SQL. I have tried copying SQL strings from a
query to a report record source. I rather feel I am not yet competent to get
this to work. SQL scripting seems a lonely path with no support from the
system.
The database is a comercial package which I have linked to, simply to give
me a better reporting system. I am probably a victim of my own success at
playing with Access.

I have played a bit more with the functions and am very close to what I
need. In the query criteria I can use;-
>=StartDate(0 And <=EndDate()
Where the two functions are declared as type 'Date'
and this works a treat.

I cannot get anything to work using a string, or variant
A data type mismatch is reported, but I'm not sure if this is because of the
function data type or the content being returned.
The code is
Function setdate() As Variant '(or "As String")
setdate = ">=#1/1/2001#"
End Function

Thanks again for the help

John

Albert D. Kallal <kal...@msn.com> wrote in message

news:wnwi9.356192$v53.17...@news3.calgary.shaw.ca...