Using function in WHERE clause

[williey]

Can I use a custom function in a where clause?
what the function does it return a date in the format of #5/7/2009#.

When I tried the below code I get data type mismatch error.

SELECT ProcDate, field1, field2
FROM table
where processdate = GetDate(-1);

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[dhookom]

We don't know what your function accepts in terms of arguments. I expect it doesn't want any and the -1 should be outside the ()s.

SELECT ProcDate, field1, field2
FROM table
where processdate = GetDate()-1;

Duane
Hook'D on Access
MS Access MVP

 

[williey]

my function accepts an integer. Isn't the GetDate() - 1 function only works in SQL Server?

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[dhookom]

GetDate() is a function in SQL Server. If you have created your own it should probably match the SQL Server function. If it doesn't, you should provide the code for your function so we know something about it. You haven't provided:
- the code
- the data type of processdate
- how these would handle null values

Custom/user-defined functions can be used in queries inside Access.

Duane
Hook'D on Access
MS Access MVP

 

[williey]

Here is the code for GetDate.

Public Function GetDate(numofdays As Integer)

Dim ProcessDate
Dim mm, dd, yyyy

ProcessDate = Date + numofdays
mm = month(ProcessDate)
dd = day(ProcessDate)
yyyy = Year(ProcessDate)

GetDate = "#" & mm & "/" & dd & "/" & yyyy & "#"
End Function

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[dhookom]

I would re-write the function to something like:

Public Function GetDate(numofdays As Integer) As Date
   GetDate = Date + numofdays
End Function

I'm not sure why you need all of the other stuff.


Duane
Hook'D on Access
MS Access MVP

 

[williey]

that works! thanks!

------------------------------------------
There are 10 kinds of people in this world. One that understands binary and the other one that does not.

 

[MichaelRed]

I would also recommend that you not create any function with the same name as the intrinsic (e.g. built in) of any known known function. This amounts to an override of the function in some (many instances) and can cause a lot of grief.

A convient way of avoiding almost all such naming issues is to prefix your functions with some common substring, like 'bas' for any procedure written in any of the dialects of basic, e.g your "GetDate" would become "basGetDate"




MichaelRed

 

[PHV]

Furthermore, why a custom function at all ?
WHERE processdate = Date()-1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886