Using a function in a query

[James D.]

Hi, I cant seem to get the following to work.

Problem:
I am trying to make a simple select query whos criteria is populated by a
function named Getname(). Every time I try and run it, it produces the
following error: "Compile error. in query expression
(((tblCustomers.tblName)=GetName()))"

Background:
I have a query which use's the function GetName() function in the criteria
of the Name Column of the select query to get all customers who is like the
name .

the "like" keyword is used infront of the GetName() function eg. like
GetName().

GetName() takes a input field value from an open parameter form and tests
it. If the input fields value is null it returns a "*", otherwise it returns
the value of the input field.

I thought the query should then run and provide me with all the customer
names who is like the returned value of the GetName() function, but it just
produces the above error.

Solutions tried: I have removed the like condition to see if that was it
and still it will not work. I have placed a break point at the function
declaration and it seems that it never even gets to that point when the query
is run.

Here is my code for the function:
"
Public Function GetName() As String

If IsNull(Forms![frmMulitSearch]![txtName]) Then
GetName = "*"
Else
GetName = Forms![frmMulitSearch]![txtName]
End If

End Function
"

Here is my code for the query:

SELECT tblCustomers.name
FROM tblCustomers
WHERE (((tblcustomers.name)=GetName()))

The paramater form:

Form name: frmMulitSearch
Field name: txtName

I am pretty sure I am missing somthing really simple, but for the life of me
I can't figer out what.

Any help would be greatly apprecaited.

James D.

 

[Ofer]

Just incase you put the function under the form, you need to place it in a
module so the query will find it.

Try Like instead of = with square brackets in the name. the "name" is a key
word in Access so it better keeping it in square brackets

SELECT tblCustomers.[name]
FROM tblCustomers
WHERE (((tblcustomers.[name]) Like GetName()))

 

[James D.]

Hi , unfortunatly the function is in a module and not a form. As well I
tried the proper bracketing of the [name] field and still no go.

I really appreciate the help, thanks!

Any other suggestions would be awesome.

James D.

Just incase you put the function under the form, you need to place it in a
module so the query will find it.

Try Like instead of = with square brackets in the name. the "name" is a key
word in Access so it better keeping it in square brackets

SELECT tblCustomers.[name]
FROM tblCustomers
WHERE (((tblcustomers.[name]) Like GetName()))


--
\\// Live Long and Prosper \\//
BS"D

Hi, I cant seem to get the following to work.

Problem:
I am trying to make a simple select query whos criteria is populated by a
function named Getname(). Every time I try and run it, it produces the
following error: "Compile error. in query expression
(((tblCustomers.tblName)=GetName()))"

Background:
I have a query which use's the function GetName() function in the criteria
of the Name Column of the select query to get all customers who is like the
name .

the "like" keyword is used infront of the GetName() function eg. like
GetName().

GetName() takes a input field value from an open parameter form and tests
it. If the input fields value is null it returns a "*", otherwise it returns
the value of the input field.

I thought the query should then run and provide me with all the customer
names who is like the returned value of the GetName() function, but it just
produces the above error.

Solutions tried: I have removed the like condition to see if that was it
and still it will not work. I have placed a break point at the function
declaration and it seems that it never even gets to that point when the query
is run.

Here is my code for the function:
"
Public Function GetName() As String

If IsNull(Forms![frmMulitSearch]![txtName]) Then
GetName = "*"
Else
GetName = Forms![frmMulitSearch]![txtName]
End If

End Function
"

Here is my code for the query:

SELECT tblCustomers.name
FROM tblCustomers
WHERE (((tblcustomers.name)=GetName()))

The paramater form:

Form name: frmMulitSearch
Field name: txtName

I am pretty sure I am missing somthing really simple, but for the life of me
I can't figer out what.

Any help would be greatly apprecaited.

James D.