Date trouble (1 Viewer)

[sarahdonnelly]

I am currently designing a database for a small recording studio. I want to create a query that will display the bookings for the period that I require. For example, I would like to be able to enter a start date and a finish date so that the query will display all the bookings on and between those two dates, does anyone know how to do this?

 

[edtab]

Step 1:
Create a new form and place two unbound text boxes on it. Call the first one "From" and the second one "To".

Save your form giving it the name "DateRange".

Step 2:
Create a new query based on your source table. On your query grid, select the date field and under the date criterion type in "Between forms![DateRange]![From] and forms![DateRange]![To]".

Save the query and name it "DateSearch".

Step 3:
Edit the form "DateRange" and attach a code behind the "After Update" property of the "To" text box; type in the following code:

DoCmd.OpenQuery "DateSearch"
Save your query once more.

Step 4:
Edit your main form containing your menu list. On this form, create a new command button and attach a code under it's "On Click" property; type in the following:

DoCmd.Openform "DateRange".
Save your main form.

And that's it.

Now to test:

- Open your main menu form
- Click on the new command button
youcreated. This should bring up the
form "DateRange"
- Type in your "From" and "To" dates
When you hit enter, your query should run
and will choose only those records
falling between your "From" and "To"
dates.

Let me know if this works.

 

[sarahdonnelly]

I did everything as you said (as far as I can tell!) and when i tested it using the command button it came up with the error message 'Microsoft Access can't find the macro 'DoCmd.'

 

[David R]

When you are at the Events tab of Properties and select OnClick, click the [...] at the end of the row and select Code Builder. Between the open and close of that code subroutine, place the code edtab mentioned above (the cursor should default to the right place). Now try it and see if it works.

When you type it directly on the line, it thinks you're giving it the name of a macro, that's what confused Access (not that it takes much).

Hope that helps,
David R

 

[sarahdonnelly]

No error message is coming up now it is just doing nothing!! (Access never works for me!)any suggestions?

 

[David R]

1) Try to run your query from the database window. You should get two strangely formatted prompts, provided the DateRange form is closed. Enter the dates you want to test and see if the data matches.
2) Check your [Event Procedure]'s on both forms to make sure you actually referred to the correct Query and Form. You can post what you put in the code window here (the After_Update code and the OnClick code).
3) Put a breakpoint in your code before where you think things are falling apart. In the code window click on the gray bar on the lefthand edge and you'll get a red dot. Now when you run that piece of code it will stop and show you the code, highlighted. If it never does that, then you know you're not getting to the code and something else is wrong.

HTH,
David R

 

[sarahdonnelly]

when i try to run query DateSearch i get an error message saying datatype missmatch in criteria expression, could this be something to do with it?

 

[David R]

Try putting

Between "#" & Forms![DateRange]![From] & "#" And "#" & Forms![DateRange]![To] & "#"

in the criteria and see if that helps.

David R


[This message has been edited by David R (edited 04-02-2002).]

 

[sarahdonnelly]

When i run the query the to and from date boxes run,when i entered the dates though, it tells me the expression is incorrect or too complex to be evaluated

 

[Chineham]

Go to your Query and type

Between [Enter start date:] and [Enter end date:]

in the criteria row for the field you want access to search. This one always works for me! Let me know