Insert Date Value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to make a report return records between two fields "Start Date" and
"End Date".

this is the code that I have so far:
Private Sub Report_Open(Cancel As Date)
Dim StartDate As String
Dim EndDate As String

InputBox "Start Date", , "01/01/05"

InputBox "End Date", , "01/01/05"

End Sub


and then I get stuck. It is someone else's database and I have just run a
query and a report from a query. But I don't know how to insert the fields
into the query with a message box asking for an input box.

End Sub
 
Sharon:

You can put the start and end date parameters directly in your query. For
any reference to a value that Access cannot reconcile to a field in a table,
Access will assume it is an input parameter and prompt you for its value.

In the query designer, in the Criteria section for the Date field you want
to constrain, you can add something like the following:

BETWEEN [StartDate] AND [EndDate]

As long as you do not have any fields with these names, Access will prompt
you for their values automatically, whether you are running the query by
itself, or as the record source for a report.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I want to make a report return records between two fields "Start Date" and
"End Date".

this is the code that I have so far:
Private Sub Report_Open(Cancel As Date)
Dim StartDate As String
Dim EndDate As String

InputBox "Start Date", , "01/01/05"

InputBox "End Date", , "01/01/05"

End Sub


and then I get stuck. It is someone else's database and I have just run a
query and a report from a query. But I don't know how to insert the fields
into the query with a message box asking for an input box.

End Sub
 
I would utilize a variation on QUERY BY FORM which is a technique that
uses a FORM to capture criteria for a query. The same technique can be
used to open a form. I recommend this over using InputBox() because if
the person hits enter or supplies an invalid value, the report will
probably crap out. So create a form with two text boxes (be certain to
set the .Format and .InputMask properties accordingly. Then add a button
- PRINT REPORT. The Wizard for the command button will should capture
the information you need to get the code to open the report. Once you've
got all of that, post back the name of the report and the name of the
fields and we'll walk you through modifying the code to open the report.
 
David,
That worked great! Actually, by you steering me in the right direction, I
was able to create two queries: The first query "weeds out" the fields where
the ActionDate is null and the completed date is not null. That way, it only
returns records that have an Action date that is due and has not been
completed yet. Then, I created the second query that actually asks me for
the start date of my report and the end date of my report and it returns just
the records that I want!!

Now, it possible, I would like to add a parameter that would ask me which
file number to return. For example, one client begins with "3999."
(3999.000100, 3999.000200 etc.) and another client begins with "4001"
(4001.000100, 4001.000200, etc.) I can make it return the records that I
want when if I enter

Like "4001." & "*"

or

Like "3999." & "*"

But I want an input box to ask me which client number prefix to return
records for. I have tried putting it on the same line as the Between start
and end date on the criteria line, but it won't work there. Can you do more
than one query in this manner?

Thanks.
 
Back
Top