Need help on Between Date Criteria Queries

[mkdeneve]

Hi, I am a novice now using Access 2003 for my job. Each month I need to modify and run over 100 queries that I must update with a date range in a criteria field. An example of the field that I must update is: "Between #5/31/2014# And #6/27/2014#".

Question: Is there a way that I can update the date criteria once (like in a table) then modify the queries so that they will use this modified date? That way, I would only need to modify the date in one place instead of modifying all of the separate queries each month.

Keep in mind that I am a novice, so you might need to keep answers simple for me to understand. Any help would be greatly appreciated. Thanks!

 

[Galaxiom]

It could be done from a table but the easiest way is to have the date range read from a control on a form.

However, having to run 100 update queries each time a month changes suggest the database structure might not be ideal.

Could you elaborate about why you need to do this and briefly describe your table structures.

 

[mkdeneve]

Galaxiom,

Thanks for the reply. Here is the situation. I inherited this database from someone who has left our company. The main purpose is to generate billing reports that require a unique query to provide the data for them. These are the 100+ queries that I need to modify, so that they will pull the data for the specific report for a specific billing month. Does this help?

Also, I need to modify 100+ reports to use the new name of the 100+ queries I modified, so if the query names can remain the same from month to month, I'll never need to modify the 100+ reports as well.

This database was designed when there were only a small number of reports that were needed monthly. The usage has grown to the point that it is too work intensive. It probably needs a total re-write to meet the new needs, but I need a quick fix now to make the workload bearable. This could be a huge productivity advancement if there is a way to do it. Unfortunately I have failed to find a way on my own, thus I am putting this question out to the forum. Thanks!

 

[Galaxiom]

Create a form with textboxes to hold the dates you need to feed to the query. The form does not need to be based on any tables so just use the Form Design button and add the textboxes manually from the Design menu.

Modify the criteria in the query so that instead of a date they are in this style:

Forms!formname!textboxname

Now when you run the query it will get the dates from those boxes.

A script could be used to replace all the dates in the queries with these references once you get one of them to work with the form. Don't do that manually to 100 queries.

Why do you need to change the names of the queries?

One thing for sure your database needs a complete rethink.

 

[mkdeneve]

Thanks, Galaxiom. I agree with the rethink, but need to save on work to get time to do it.

I'll try your suggestion. I am not quite sure of how to do it, but I'll try a few things and repost.

 

[mkdeneve]

I have never created a form before. According to help text I need to create a table or query with the data. How do I do I crate the ext boxes like you say without referecing a table or query?

 

[Galaxiom]

The help instructions are for a bound form. You only need an unbound form.

Been a long whiile since I use A2003. If I remember correctly Create Form will make a form based on a table or query that is currently selected in the navigation pane using a wizard but Form Design will start an empty form.

That is what happens in the ribbon on later versions anyway.

I have attached a quick and dirty demo that might help you get the idea.

You could import the form to your database and change it to what you need.

Take a look a the property sheet of the form controls, particurly the Default Value of the textboxes so see how the form opens with something already in the boxes.

 

[mkdeneve]

Galaxiom,

Many thanks. I had trouble running your demo because of missing references to a DLL. Anyway, I ignored the error message and I am still confused. It looks like the form is looking for inputs (which I understand) but I not understand how to make my queries take the inputs, or what your run query button does. I would like to enter the date ranges somewhere once, then have the reports reference/run the queries to without needing to enter data every time. Sorry for my ignorance, I am just learning.

Here is what I did. When I modify a test query, it has problems. I created a form called Data Input Form with text boxes called Start Date and End Date. If I modify the test query criteria to use: Between "!Forms!Data Input Form!Start Date" And "!Forms!Data Input Form!End Date" I get an error stating Data type mismatch in criteria expression. It looks like it wants to see a date value vs. going to the form for the data. I am stumped.

Any help would be appreciated! Thanks!

 

[Galaxiom]

I had trouble running your demo because of missing references to a DLL.

That would be because it is an mdb created in A2010.

Here is what I did. When I modify a test query, it has problems. I created a form called Data Input Form with text boxes called Start Date and End Date. If I modify the test query criteria to use: Between "!Forms!Data Input Form!Start Date" And "!Forms!Data Input Form!End Date"

All good so far.

I get an error stating Data type mismatch in criteria expression. It looks like it wants to see a date value vs. going to the form for the data.

Try entering the Format property of the text boxes as ShortDate.

Check if the date field in the table is a DateTime or Text.

 

[mkdeneve]

I entered ShortDate (was blank) as the format properties in the query and still got the same error. I then changed the table date field from Date/Time to Text. Now the query runs, but does not have any results. All the other queries no longer get results either. Ideas?

 

[Galaxiom]

I entered ShortDate (was blank) as the format properties in the query and still got the same error. I then changed the table date field from Date/Time to Text.

The field should be DateTime.

I am suspecting the later versions of Access might be smarter at implicit casting of text to date as required.

Try this criteria:

Between CDate(Forms!formname!StartDate) AND CDate(Forms!formname!EndDate)

This will explicitily cast the criteria to dates.

BTW. Recommend you drop spaces from object names otherwise you would need to put square brackets around them.

eg [Data Input Form]

Access will do that automatically in some places but not in code. It saves a lot of work later and makes code easier to read.

 

[mkdeneve]

Making some progress......

I did what you said, changed table field back to DateTime and dropped the spaces too, and now I am being asked to "Enter Parameter Value" Forms!InputDataForm!StartDate when running the query. When I enter the values, I get the expected result. What I want is to not have to enter any values, and have the query run and use the dates that I have in the text box in the form.

I also now see square brackets on what I entered in the criteria field: Between CDate([Forms]![InputDataForm]![StartDate]) And CDate([Forms]![InputDataForm]![EndDate]).

Original queries still get the data type mismatch.

Ideas?

 

[Galaxiom]

If the form is open it should get the values there.

Did you change the form name to not have spaces? Check the spelling carefully.

The other thing that can cause a mismatch error is Nulls in the data. Nulls don't match any data type, even other Nulls.

Do all records have a date entered in that field?

 

[mkdeneve]

If the form is open it should get the values there.

The form is open

Did you change the form name to not have spaces? Check the spelling carefully.

yes, it is correct

The other thing that can cause a mismatch error is Nulls in the data. Nulls don't match any data type, even other Nulls.

No nulls, data is OK

Do all records have a date entered in that field?

yes, all records have a date

I did not have the form open when I ran the queries earlier. I opened the form, and I think that the form has problems. When I open it, it shows #Name? in the data fields. When I view the properties, I see the default values to be what I set them to. I think this is the problem, but not sure how to fix it.

 

[mkdeneve]

I made some changes to the form, now query returns all records except what I specified. I am stumped.

 

[Galaxiom]

When I open it, it shows #Name? in the data fields. When I view the properties, I see the default values to be what I set them to. I think this is the problem, but not sure how to fix it.

This would be because you entered the dates in the text boxes in Design View. That will load what you type into the ControlSource property which is meant to be the name of the bound field in the RecordSource of the form.

You have an unbound form so the ControlSource should be blank. In Design View the boxes should say "unbound". Note that you do not type that, it is put there by Access. Just leave it blank.

Enter the dates after the form is opened in Form view and I think you will basically be there.

The next refinement is if you want a particular date pattern to already be there when you open the form then you can get it to appear by putting an expression in the Default property.

That is what I did in my demo but I probably made it more confusing. Sorry.

 

[mkdeneve]

Galaxiom,

I did some clean up as you directed and it is running reliably now. Is there a way that I can have the form hold the last value entered? This would really speed things up. Thank you very much for you continued assistance!

 

[Galaxiom]

Is there a way that I can have the form hold the last value entered?

Easiest way is to bind the form.

Make a table with two fields to store StartDate and EndDate. You can use those as fieldnames.

Put the table name in the RecordSource Property of the form.

Enter FirstDate and EndDate in the ControlSOurce Property of the textboxes on the form.

If you like you can set the form's AllowAdditions and AllowDeletions to No so it only ever has the one record.

 

[mkdeneve]

This is awesome! Thanks!