RE: Need Help To Create a Function For Parameter Query
- From: CyndyG <CyndyG@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 19 Sep 2005 16:20:02 -0700
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.
"'69 Camaro" wrote:
> 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" wrote:
>
> > 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.
> >
> >
> >
.
- References:
- RE: Need Help To Create a Function For Parameter Query
- From: '69 Camaro
- RE: Need Help To Create a Function For Parameter Query
- Prev by Date: Re: Please Help Me! trying to play AVI file
- Next by Date: RE: Query as subform object / capture filter by form
- Previous by thread: RE: Need Help To Create a Function For Parameter Query
- Next by thread: Simple Math
- Index(es):
Relevant Pages
|
