VeggieBoards banner

1 - 10 of 10 Posts

·
Registered
Joined
·
8,440 Posts
Discussion Starter #1
Does any one here have (serious) experience with access2000 ?<br><br><br><br>
I have a query that gets records from a table.<br><br>
It selects all records Between date and date (so far so good).<br><br><br><br>
Problem: the date have to be changed every two weeks. Not by me because I can handle the query in the design mode.<br><br>
I want the dates to be changed in a easy way by a simple user, preferably in a form (or so).<br><br><br><br>
Ive been fooling around with macros but it gets me nowhere.<br><br><br><br>
Is what I want possible and how?
 

·
Banned
Joined
·
5,034 Posts
<div class="quote-container"><span>Quote:</span>
<div class="quote-block"><i>Originally posted by 1vegan</i><br><br><b>Does any one here have (serious) experience with access2000 ?<br><br><br><br>
I have a query that gets records from a table.<br><br>
It selects all records Between date and date (so far so good).<br><br><br><br>
Problem: the date have to be changed every two weeks. Not by me because I can handle the query in the design mode.<br><br>
I want the dates to be changed in a easy way by a simple user, preferably in a form (or so).<br><br><br><br>
Ive been fooling around with macros but it gets me nowhere.<br><br><br><br>
Is what I want possible and how?</b></div>
</div>
<br><br><br>
First, you need a form with two text boxes (or put two text boxes on the form that is currently used to run the query). I make mine have the format of "Short Date" as well as an input mask of short date (to reduce data entry errors).<br><br><br><br>
Then in the Criteria portion of the date field (in the query design view) put:<br><br><b>Between Forms![<i>form_name</i>].[<i>start_date_textbox_name</i>] And Forms![<i>form_name</i>].[<i>end_date_textbox_name</i>]</b><br><br>
(Items in italics should be the actual names of the objects (Form and Textboxes).)<br><br>
If you right-click in the Criteria part and select "Build...", it will open the Expression Builder which can assist you in entering this expression, allowing you to browse through to those text boxes and paste their names into it.<br><br><br><br>
Another method is to use parameters in the query; in the Criteria part of the date field in the query design enter:<br><br><b>Between [Please Enter Start Date] And [Please Enter End Date]</b><br><br>
When this query runs, the user will be prompted to enter the start date, then prompted for the end date.<br><br><br><br>
And another way is to use:<br><br><b>Between <i>start_date_function</i> And <i>end_date_function</i></b><br><br>
The two functions (actually one function, with a parameter passed to them to indicate whether to prompt for start or end date) prompt the user, using an Input Box, for the dates (and can have other information shown, such as a description of the date and a default value). You'll need to know Visual Basic for Applications to do this (writing a function in a module).<br><br><br><br>
I primarily use the first method, because I can add data validation (such as start date being before end date). Data validation can also be done on the third method.<br><br><br><br>
I hope that helps (it's 11pm and I have been SQL coding all day and have all tomorrow to look forward to.... so I hope I was clear).
 

·
Registered
Joined
·
8,440 Posts
Discussion Starter #3
Wow !, thanks a lot, I'm going to try this Monday or Tuesday depending on the work schedule.<br><br><br><br>
Thanks again.
 

·
Registered
Joined
·
8,440 Posts
Discussion Starter #4
Ok Ive tried all kind of things for two hours and it doesnt do what I want it to do.<br><br><br><br>
Ive also forgot something important.<br><br><br><br>
I make a report with an Excel spreadsheet. This spreadsheet gets info straight from the access database (the query).<br><br><br><br>
I made a form where one can fill in the dates. If you open the form the dates that where entered are still there. When the query starts it asks the dates again from the user, and I get the message in Excel that there are two parameters missing.<br><br><br><br>
So Ive tried a variety with a form that fills a record in a table and then tried to make the query use the dates in the form or the dates in the first (and only) record of the table, but the query keeps asking for input.<br><br><br><br>
And thats exactly what I dont want.<br><br><br><br>
I want to store the begin-date and end-date somewhere and that the query uses these dates.<br><br><br><br>
I get the feeling that Im close, but Im overlooking something.?
 

·
Banned
Joined
·
5,034 Posts
I forgot to mention that dates in Access need # on either side of them.<br><br>
Eg:<br><br>
Between #1/1/2001# And #31/1/2001#<br><br><br><br><br><br>
Are you able to post the query for me to check out?
 

·
Registered
Joined
·
8,440 Posts
Discussion Starter #6
Between [Forms]![datumform]![begin] And [Forms]![datumform]![eind]<br><br><br><br>
??
 

·
Registered
Joined
·
0 Posts
Have you tried creating a web page for the users who are updating the database and setting up a System DSN on the host machine?<br><br><br><br>
Is this being run over a LAN or the Internet?<br><br><br><br>
You can create an ASP page very easily that reads to / writes from your database. This way you have ultimate control over the form data types through the server-side validation and form controls you use.
 

·
Registered
Joined
·
8,440 Posts
Discussion Starter #8
The database is on a LAN.<br><br><br><br>
Wouldn't a web-page have the same effect / result as a form ?<br><br><br><br>
I took Kurmudgeon advice and made a form. The query runs when the form is open (loaded). When the form is closed the query asks for input.<br><br><br><br>
This is basicly ok, and I can use it in another "database".<br><br><br><br>
I think that what I want isn't possible, or at least not within my reach/knowledge of access.<br><br><br><br>
I have to approach the "problem" from a different side.<br><br><br><br>
I register errors in the database and use Excel to calculate totals, averages and percentages per person/range.<br><br>
Excel gets the info via a "ms query" straight from the database without the database even open on the PC.<br><br><br><br>
So now I can't get past the problem that the query won't run in the way I want with the database open;<br><br><br><br>
I have to make some effort to do the calculation in access its self.<br><br><img alt="" class="inlineimg" src="/images/smilies/sad.gif" style="border:0px solid;" title=":(">
 

·
Banned
Joined
·
5,034 Posts
Access can do calculations..... I've modified some systems that were based on a mix of Access and Excel to work just in Access (I've also done the reverse, usually when I want better Charts produced).<br><br><br><br>
And if you look into VBA, Access can do a lot more calculating than in queries/reports/forms alone.
 

·
Registered
Joined
·
8,440 Posts
Discussion Starter #10
The down side of the story is that I made the database when "there was no time for it" and I wanted it and made it in my lunch breaks.<br><br><br><br>
I made reports in Excel because that was the quickest route for me.<br><br><br><br>
I would like to do more (automated) in access but then I come in the vba-area. There are not so many books that cover vba (for access) in the lanquage that my version is.<br><br><br><br>
I could turn to the IT-people here, but they don't do much in vba and love the KISS-principle.<br><br><br><br>
Now I have found VB, I don't have that much spare time to experiment in.. <img alt="" class="inlineimg" src="/images/smilies/smiley.gif" style="border:0px solid;" title=":)"><br><br><br><br>
So for now I have to write a small manual for what to do when I'm not around to make the report when it's needed, until I finally have the report in access the way I want it.
 
1 - 10 of 10 Posts
Top