Dynamic form

[guinness]

Ok here goes

I have two queries set up with criteria to filter on mm/yyyy and then a third query that displays the difference between the two forms. So if query 1 shows 02/2011 (Feb) and query 2 shows 01/2011(Jan) then the third query shows the +/- difference between Jan and Feb 2011. I have set the third query to display as a pivot form.

I want to set up a form to enter the criteria rather than the pop up criteria boxes from the queries. I want to put 02/2011 in one text box and 01/2011 in the next and have it display the results (query three) in a pivot table view.

Can this be done and how?

Thanks in advance

 

[Galaxiom]

The queries can refer to values in controls on a form like this:

Forms!formname.controlname

BTW. If you are filtering dates you will get a performance by using full dates than you will by generating the mm/yyyy from the date field for comparison. This is because the date field can be indexed.

 

[guinness]

Thanks galaxiomathome. I'm ok on how to reference the form from within the query however I'm not sure how to set the 'Format' option on the form.

In the query 1 I'm using Expr1: Format([Utilisation Step 2]![W/C Date],"mm/yyyy") as a field and then [Compare this month: mm/yyyy] as a criteria. How do I express this as a format in the form ie how do I get the form to accept 02/2011 as a date input and apply it as a criteria to the query?

I agree with you on using whole dates however the query sums whole dates to get the month data so I would need to enter start and end dates for each step of the query which I want to avoid.

Thanks again

 

[Galaxiom]

Access can't compare raw dates as strings because the order of the characters does not result in a chronological sequence. You must convert the text input so you might as well convert it to a date and compare with the date in your table.

Assuming your regional date format is dd/mm/yyyy this is the Criteria in the date field of your query design.

BETWEEN CDate("1/" & Forms!formname.startmonthyear) AND DateAdd("m",1,CDate("1/" & Forms!formname.endmonthyear))-1

The second expression converts to the date as the first of the month, adds a month and subtracts a day to get the end of the specified month. It does not care if the month is mm or just m.

It uses just two conversions instead of converting every date in your table and can instantly find the records in the range from the field's index. This would be (conservatively) at least one hundred times faster than the way you propose. As the table grows the speed advantage would increase.

Unfortunately if you have mm/dd/yyyy regional date it will be considerably more complex and I would recommend a custom function. Post back if this is a problem.

 

[guinness]

Hi Galaxiomathome. I like the sound of using code that searches faster and I had a quick look at this last night. The problem is that it wanted the same month/year entered twice (I'm assuming so it had a start and end date of the month).

My figures are created on a once a week basis (a full date format week commencing date is used). Using a pivot table I can then display by week, month, quarter or year which is ideal. I then used a query to sum the activities in each week commencing date and show them as percentages. Now what I want is to create a form that allows a user to easly compare two reports and see the difference between the two. So the percentages for last January were this, this january are this and the difference between the two is +/- whatever.

So I need one form that asks for a month and year (to run query 1 'this january') and another to run query 2 last January or whatever month you want to compare against. The result is query three which shows the +/- figures on a pivot form.

I appreciate your help so far

Cheers

 

[Galaxiom]

So I need one form that asks for a month and year (to run query 1 'this january')

Just change the criteria to take both the monthyear values from the same textbox.

BETWEEN CDate("1/" & Forms!formname.searchmonthyear) AND DateAdd("m",1,CDate("1/" & Forms!formname.searchmonthyear))-1

If it is asking for parameters to be entered then it is not finding the textbox on the form.

If you want January this year as a fixed query then:

BETWEEN CDate("1/1/" & Year(Date())) AND CDate("31/1/" & Year(Date()))

BTW Another way to build dates is the DateSerial function.

 

[guinness]

Unfortunately I can't try this this week because I'm busy with other stuff. Don't think I've given up on it though or that I don't appreciate your help. I'll be back on this after this week.

Cheers