Report based on date field

[lk2827]

Hi

I am trying to make a report where it will display the results in a report based on the date inputted.

I have a table which the filed that I am doing a report for is calulated.
I want to make a query so that if you enter a date it will so you all the dates before that date and show it in a report. I have tried making the report but keep getting a blank page as the field is calulated.

Can someone please help me.

Thanks

Lee

 

[boblarson]

Perhaps this sample will help:
http://downloads.btabdevelopment.com/Samples/dates/ReportAndForm.zip

 

[lk2827]

Hi Again bob,

Thanks I opened the sample and thats what I want.

Could you please explain a bit more how I can link my table to a query.

Thanks

 

[boblarson]

I think you probably need this tutorial then:

http://www.functionx.com/access2003/Lesson17.htm

 

[lk2827]

Hi Bob,

Thanks for that I have looked at the tutorial and I have made the query.
However the problem that I have is beciase the filed I want to query is a calulated date field it is always blank in the report?

 

[boblarson]

What do you mean that it is a calculated date field? You can supply criteria for ANY field in a query, including calculated fields. You just need to know what it is looking for. So, what is the expected output for that field?

 

[lk2827]

OK I have just tried it in another field where the field is a set date and it all works perfect. but when I try to change it to another date field where that date field is calulated it appears blank?

 

[boblarson]

Can you be a little more specific? In other words, what is the calculated field - how is it calculated? What criteria are you trying to use?

 

[lk2827]

sorry, ok
the field which is not working is called next test. I think this is because it is a calulated field and it is always blank in the table which is queried ie calculated in the form and not the table, however the last test is not blank in the table and therefore it will display the report correctly.

 

[lk2827]

I think I have got to the bottom of it. How can I make a field in a query = a form field

 

[boblarson]

If I remember correctly, you are storing the frequency and the last test date. So, you can use that formula I came up with in a query to generate the field by which you can set the criteria.

Just call it in a query by creating an extra column in the Query By Example(QBE) grid:

NextTestDue: NextTest([Last Test],[Frequency Test])


Then you can set your criteria for your report on that field.

 

[lk2827]

I have tried that and it is not working. I cant seem to make the next test field appear automatically in the query.

 

[boblarson]

You apparently didn't do it correctly. I'll repost your sample - look for Query1 as the sample.

 

[lk2827]

Thanks I have the query to now show the next test field automatically. Cheers. I was not removing the old field next test after creating the new one.

Now I have inserted in the new field I have made the following
Between [Forms]![nexttestreport]![txtStartDate] And [Forms]![nexttestreport]![txtEndDate]

But it is now blank, am I putting it in the worng field again.

Sorry for being supid

 

[boblarson]

Okay, take a look at the form and the code behind the button. You don't need to set any criteria to the query at all. Just set it in the code behind the button:

 

[lk2827]

Cheers Bob. It work. Thanks Again for your help

 

[boblarson]

Cheers Bob. It work. Thanks Again for your help

Glad to be of assistance!