need help creating a function

[punderful]

I'm new to writing functions, and can really use your help before this project is pulled from me!!

tables: projects, changeorders
Changeorder table fields: projectID, taskName, due, complete, billed

problem: have to be able to pull ONE of the 3 dates (due, complete, billed) for a specific task. Tasks vary from project to project, hence I am not creating a field for each task.

desire: drop that one resulting date in a column of a query (or directly in a report)

What I have come up with is creating a function that brings in the taskName and the type of date needed (such as due), returning the resulting date. Here is what I have for code so far, but it's not working:

Public Function GetRS(strTask As String, strDateType As String) As Date

'Creates recordset for reporting based on user criteria

Dim rs As Recordset
Dim strSQL As String

rs.Filter = strTask
strSQL = "SELECT tblChangeOrder." & strDateType & " FROM tblChangeOrder"

Set db = CurrentDb()

Set rs = db.openrecordset(sqlString, dbOpenSnapshot)
return GetRS

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

 

[John Lewis]

Well, just off the top of my head:

Set rs = db.openrecordset(sqlString, dbOpenSnapshot)
should be
Set rs = db.openrecordset(strSQL, dbOpenSnapshot)

 

[PWise]

i have changed some lines let me know if you get any errors and where the errors are
Public Function GetRS(project, Task As String, strDateType As String) As Date

'Creates recordset for reporting based on user criteria
dim db as database
Dim rs As Recordset
Dim strSQL As String


strSQL = "SELECT tblChangeOrder." & strDateType & " FROM tblChangeOrder where projectid=" & project & " and taskName='" & strTask & "';"

Set db = CurrentDb()

Set rs = db.openrecordset(sqlString, dbOpenSnapshot)
rs.movefirst
getrs=rs!fields(0)

rs.Close
Set rs = Nothing
Set db = Nothing

End Function

 

[punderful]

I'm getting an error: (user defined type not defined) error on "dim db as database".

Also, I am confused on whether or not I need to include the projectID in the incoming arguments. Using the function in a query or report wouldn't it pull the data from the recordset from the appropriate projectID (based on the relationship)? If I need to pass the projectID into the function I am not sure how to do that, for example a report would print all the details for a projectID then print the next projectID info, etc.

 

[PWise]

About the error on dim db as database have you set a reference to doa. See in the code window tools menu select references.
About including the projected, the way I read your data structure I think you will need it.

 

[punderful]

So if I am using this function to fill a cell in a report, how do I feed the projectID into the function? Is there something similar to me.object that will look at the projectID the report is currently printing and use that record#?