Attempting to ease users repetative use of database.

[Remdul]

I have a database that counts a number of events between a certain set of dates. The code looks something like..

SELECT Sum([ACO Report].[Board Fee $]) AS [Boarding Fee $]
FROM [ACO Report]
WHERE (([ACO Report].[Date of Pickup]) Between [First Date:] And [Second Date:]);

There are 20+ queries that search between First and Second date. For each report that these queries print to, you have to put in the same date.... 2x for each 20+ queries. What I would like to do is put in a function to input and save variables. For example, on my swithboard, have a "Insert Dates" function, where you can set the First and Second date. Then have all the queries refer to those saved variables instead of having to type it in each time. Is this possible? Thank you,

Bryan Elliott
Asst. IT Manager

 

[PHV]

Use form's textbox:
WHERE (([ACO Report].[Date of Pickup]) Between [Forms]![your parameters form]![First Date control] And [Forms]![your parameters form]![Second Date control]);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya Remdul

You could also set two [blue]Global Variables[/blue] and return them in a function.

In the [blue]declaration section[/blue] of a module in the modules window:

[blue]   Public Date1 As Date, Date2 As Date[/blue]

. . . and the function ([blue]in the same module[/blue]):

[blue]Public Function GetDate(Idx As Integer) As Date
   If Idx = 1 Then
      GetDate = Date1
   Else
      GetDate = Date2
   End If
End Function[/blue]

SQL would be:

[blue]SELECT Sum([ACO Report].[Board Fee $]) AS [Boarding Fee $]
FROM [ACO Report]
WHERE (([ACO Report].[Date of Pickup]) Between [purple][b]GetDate(1)[/b][/purple] And [purple][b]GetDate(2)[/b][/purple]);[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[Remdul]

PHV, I was unable to get this way to work, unfortunately. I kept getting prompted with search boxes as I ran the query.

Aceman1, I am afraid I do not know a whole lot about the Visual Basic side of Access. How would I be able to set/change the variables as the need called for it? I put it in as you suggested, but I couldn't figure out how to input the two dates. Pardon my ignorance with this program, and thank you for your help.

Bryan Elliott

 

[PHV]

I was unable to get this way to work
Any chance you could explain what you've tried and what happened ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remdul]

Sorry about my vagueness there. WHat I did was I created a form called Date, then put within it two text boxes called Date1 and Date2.

WHERE ((([ACO Report].[Date of Pickup]) Between [Forms]![Date]![Date1] And [Forms]![Date]![Date2]));

When I ran the query, I recieved input boxes much like my original design that said -

Enter Parameter Value (top bar)
Forms!Date!Date1

Enter Parameter Value (top bar)
Forms!Date!Date2

If I put in the same dates I had saved in the Date form, it will return the appropriate amount of money, however these two boxes apparently do not correspond with the Date form. Thank you, PHV.

Bryan

 

[PHV]

Avoid to give reserved name to your object !
Furthermore the form must be an open mainform and the controls populated with valid date values before launching the query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remdul]

Ah, I see! Thank you very much, PHV! I just have to redesign my switchboard now to that I can have this one form open all the time. Not quite sure how I will do that yet, though. This form is definately very helpful for folks like me who are attempting to learn access. Once I figure out why my database keeps disapearing, I'll be set. lol. Thank you again.

Bryan Elliott