Function to Query

[Guest]

Hi, I'm trying to pass a function into a append query but I'm not getting any
records, can anyone help? Here is my function and query:

Function ChangeQuarter() As String

Dim mydate As Variant
mydate = Date

If mydate = Date Then
ChangeQuarter = ">=20072"
End If

End Function
******************************************
Select dbo_application.reqst_admit_yyyyq_code
from Table1
WHERE (((dbo_application.reqst_admit_yyyyq_code)=ChangeQuarter()));

 

[Douglas J. Steele]

Unfortunately, you can't do what you're trying to do using a function.

Your SQL has an equals operator between
dbo_application.reqst_admit_yyyyq_code and the function call, but your
function is returning >= in addition to the value. However, if you remove
the equal sign from your SQL, the query will with invalid syntax.

The only approach that occurs to me is to dynamically regenerate the SQL
associated with the query.

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "Select dbo_application.reqst_admit_yyyyq_code " & _
"from Table1 " & _
"WHERE dbo_application.reqst_admit_yyyyq_code" & ChangeQuarter()
Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("NameOfQuery")
qdfCurr = strSQL

 

[Guest]

You can set it that way

Function ChangeQuarter() As Long

Dim mydate As Variant
mydate = Date

If mydate = Date Then
ChangeQuarter = 2007
End If

End Function
******************************************
Select dbo_application.reqst_admit_yyyyq_code
from Table1
WHERE dbo_application.reqst_admit_yyyyq_code >= ChangeQuarter()

Put the >= in the query and pass the year with the functon