Two Queries: Two date parameters - One Report

[deeda67]

I have been trying to do something that should be simple I'm sure, but I can't figure out what to do. I have two tables (one for Projects and one for Change Orders to those projects linked by Project number). I need to create one sales log report that will include both, BUT I need them both to have beginning and ending dates (so all the Prjts from 01/01/07 thru 01/30/07 AND all the COs from the same dates). If I don't do it that way, only the COs from the project numbers in 07 will come up (which isn't always the case).

I have two queries that have the Beginning and ending dates criteria, but when I try to put them in a report (one main report and one Subreport), it just keeps asking for the dates over and over.

I've tried reading the threads pertaining to this and piece an answer together but I've had no luck. Apparently I need Dummy instructions! I would appreciate the help!

Deeda67

 

[pbaldy]

The most common solution is to create a form that the user enters the dates on, and have the queries look there for the criteria.

 

[deeda67]

I'm sorry, I'm not sure how to do that for two queries. I've been using Access for years but that is new to me (I'm not a programmer). Any suggestions where to go for some help on that?

Thank you.

 

[pbaldy]

No programming required. Create a form with 2 textboxes on it, name it frmCriteria. Call them txtFromDate and txtToDate. Then in your query, change [Enter start date] to:

Forms!frmCriteria.txtFromDate

Same for the other. Now as long as the form is open with dates filled in, the queries will run without prompting.

 

[deeda67]

Thanks for your patience.

When I do that, and open the Criteria Form, it prompts me for a date before the form opens. So I can't fill it in on the form. Then when I type the dates it prompts me for, no records come up. What did I do wrong?

I've only done this on one of the queries (for one table) so far. Do I need to create a second "Criteria" form for the other? If so, how will this all work in one form?

 

[deeda67]

OK, I got it to take dates in the Criteria Form (didn't link it to either query). However, when that form is open with the dates filled in and with Forms!frmCriteria.txtFromDate set in the query, when I open the query it prompts for
Forms!frmCriteria.txtFromDate

 

[pbaldy]

The form is still open and dates in it? It shouldn't prompt for the date then. Double check all your spellings, as that's the most likely culprit.

 

[deeda67]

yes, the form is open with the correct dates and I even took everything out and retyped it slowly and it still did it. I assume I'm supposed to put that in the criteria section of the query field, correct?

 

[pbaldy]

Correct; can you post a sample db?

 

[deeda67]

I'm trying to use a url to get it uploaded. However, it keeps telling me its an invalid file.

 

[pbaldy]

In the "Manage Attachments" area of a reply here, you can attach the file. It has to be zipped and under 393k.

 

[deeda67]

For some reason I can't zip any more, somone has removed my zip software. I compressed the file and gave it a zip extension. It worked on this end, but it still tells me Operations failed.

 

[deeda67]

Under the Query (this is how it is set up and that is what pops up when I open the query with the form open and dates filled in) Does that help?

Field: Date
Table: Project Table
Sort: Ascending
Show: ON
Criteria: [Forms]![frmCriteria].[txtFromDate]

 

[deeda67]

When I remove frm from the equation, it no longer prompts me for that; however, it still doesn't read from the Criteria form when its open with the dates filled in.

Forms!Criteria.txtFromDate

 

[deeda67]

View attachment Copy of propprjt.zip

see if this worked

 

[pbaldy]

You didn't name the textboxes as I recommended, you named the labels. The actual names of the textboxes are Text0 and Text2, so this works in your query:

[Forms]![Criteria].[text0]

 

[deeda67]

Sorry about that. I made the change on the query, saved it, opened the form, filled in the dates, then opened the query and still nothing comes up. Is there anything else I'm missing?

 

[Wiz47]

You didn't name the textboxes as I recommended, you named the labels. The actual names of the textboxes are Text0 and Text2, so this works in your query:

[Forms]![Criteria].[text0]

Using your example, a simple:

AND tbldate BETWEEN txtStartDate and txtEndDate

Should work, right? Well, as long as the Forms!hisFormName!histextbox are named correctly.

 

[pbaldy]

Well, there's no data in the sample you posted, so for me the query runs without prompting, but returns no records (as you'd expect).

 

[pbaldy]

Using your example, a simple:

AND tbldate BETWEEN txtStartDate and txtEndDate

Should work, right? Well, as long as the Forms!hisFormName!histextbox are named correctly.

No, as it won't know what txtStartDate is. You need the "Forms!..." bit to tell it to look on a form by that name for a control of that name.