Need Help To Create a Function For Parameter Query

[CyndyG]

I have a query named Status. The fields prompt for are Request Status and
Request Date.

On the Request Status field I created a function called Get_Status,when the
macro runs the user is prompt for Open or Closed. THe user makes the
selction needed and eveything works fine.

I decided to do another query for the Request Date field,because I only want
to enter the dates if the status is equal to closed. The date has hours and
minutes included. I want to use >=StartDate and <=EndDate. I have maned the
function for the field Get_CLosedDate,but I don't know how to set the
function up with prompts
using Get_ClosedDate = Input(""). Everything I type is inavlid or saids
invalid datatype.

I wanted to use a function because I use dates often in my queries.
Also the actual field name for the closed date field is dteDateClosed,I use
Request Date: dteDateClosed on the query.

I am also using OutputTo for getting the data to Excel,which works great for
the Status field,but just get headers for the date field because I can't get
it wriiten correctly.

 

[CyndyG]

I should have I would like to create a Module.

 

['69 Camaro]

Hi, Cyndy.

I want to use >=StartDate and <=EndDate. I have maned the
function for the field Get_CLosedDate,but I don't know how to set the
function up with prompts
using Get_ClosedDate = Input(""). Everything I type is inavlid or saids
invalid datatype.

The user's input needs to be converted to the date data type, because the
value returned by the InputBox( ) function is a string. One way to do this
is to paste the following two functions into a standard module, then save and
compile the code:

' * * * * Start Code * * * *

Public Function Get_OpenedDate()

On Error GoTo ErrHandler

Dim sOpened As String

sOpened = InputBox("Enter date:", "Date Opened", Now())

If (IsDate(sOpened)) Then
Get_OpenedDate = CDate(sOpened)
Else
MsgBox sOpened & " is not a valid date. No records will be
selected.", _
vbCritical + vbOKOnly, "Invalid Date!"
End If

Exit Function

ErrHandler:

MsgBox "Error in Get_OpenedDate( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Function

Public Function Get_ClosedDate()

On Error GoTo ErrHandler

Dim sClosed As String

sClosed = InputBox("Enter date:", "Date Closed", Now())

If (IsDate(sClosed)) Then
Get_ClosedDate = CDate(sClosed)
Else
MsgBox sClosed & " is not a valid date. No records will be
selected.", _
vbCritical + vbOKOnly, "Invalid Date!"
End If

Exit Function

ErrHandler:

MsgBox "Error in Get_ClosedDate( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Function

' * * * * End Code * * * *

Then, you could run your query with the following syntax:

SELECT dteDateClosed
FROM tblRequests
WHERE (dteDateClosed BETWEEN Get_OpenedDate( ) AND Get_ClosedDate( ));

.... where tblRequests is the name of the table.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[CyndyG]

ThanKs I will be adding these modules into my databases..

I do think my post was misleading though for this instance.. In the module
the user will be prompted for the dates for closed records only which may not
be current
For Example, the user may enter the following for records closed in
August:StartDate 08/01/2005 and EndDate 08/15.2005

The report will then contain records withn those dates. parameter :

=[StartDate]and <=[EndDate] The Closed field is a Date/time field that will only show dates and not times on the report.The date should be entered in mm/dd/yyyy

format.
They want the date and times on the table,but only the date on the report.

Where do I put the >=StartDate and <= EndDate?
user should receive a message if the above is not true.

Hope I didn't confuse you.