Query by Form

[uwponine]

Thanks in advance for your help! Questions are written in bold. I am not familiar with code so if it must be added, please let know where exactly it must be added (on click, on exit, etc.)

I have a tbl [tblTasksReceived]. Key fields include: [task id], [task requestor], [task start date]. There isn't any field to mark tasks as incomplete; it is assumed that the data in the table is incomplete.

There is another tbl [tblTasksCompleted]. Key fields include: [completed task id], [task id] (same as task id as above), [task completed by], [date completed]. I have set up a behind the scene field [task completed] (which doesn't appear on reports) as a yes/no field that marks all items in the table as completed. Incomplete data is determined if the Task ID in this tbl IsNull.

I then merge the data based on [task id] to give me the full data. I use the [task completed] to query out (in another query) the completed items. And a separate query for incomplete.

I have the reports driven by a form. The query is linked to the form by [forms][formname][field] to select the "main" query field [task requestor]. That works. How do I end a date prompt in the form/query so that the dates entered in the form will show up on the report? This is important to the main user.

Is there a way to design the form/corresponding data so that the incomplete tasks and completed tasks can be shown as options on the form to select which data set? I already have the date range and [task requestor] fields I also need to be able to select both sets of data for a combinded report where the completed tasks would be one group and incomplete tasks another group. Thanks a bunch!

 

[Swillsy]

Hiya i've just been generating a few reports could you upload your db so i can see what you need.

James

 

[skaas]

Q1

You Should find this useful

http://www.fontstuff.com/access/acctut03.htm

Q2

Create a quiery to perform your dates calculations as above

Then create a Look-Up queiry that will use the calculations queiry as its data source.

Select from the calculations quiery to display completed and incompleted tasks under two fields.

Add a new combo box for your form and open its properties, in the box that says "Control Source" select the Look-Up queiry and select incompleted tasks from the list.

Do the same with another combo box for Completed tasks

You will then have two drop down boxes one listing all Incomplete tasks and one showing all completed tasks

Take a deep breath

 

[skaas]

You may also find this sample useful

http://www.rogersaccesslibrary.com/Access2000Samples/ComboChoosesRecord2k.zip

 

[uwponine]

Follow up-Query by Form Printing Issues w/ Results

I have my frm working to generate the report via a qry (everything is linked in the criteria fields in the qry using the [forms]![form name]![field] method). I have 3 criteria the user enters on the forms user name (from a cbo box, generated from a table), start of date range date, and end of date range. I can get the criteria to show up on the rpt (the selection form closes automatically after the user hits the preview report button). However, my problem is that only the user name prints out--not the date range. I created 2 unbound text boxes to link the start date and end date to the values displayed from the qry (note: for the date range I used BETWEEN [forms]![frm name]![start date] AND [forms]![frm name]![end date]). The only thing I can sort of contemplate is that it doesn't work b/c there isn't a pre-set list of date values in a tbl so that it serves as a look-up field (if that sentence makes any sense!)

Thanks for your help.

 

[skaas]

Would it be feasable to create a new table showing the date values?

You should be able to to get your query to export the values into a new table that you could use purely for the purpose of creating the Look-Up

 

[uwponine]

How would I....

Is there a way so that in the main table the [start date] that is entered automatically feeds into a separate date table for the lookup part? Any website you can point me to?

Thank you kindly.

 

[uwponine]

update........

I tried creating a table which lists all dates in the calendar but still can't get the results to print out on the report. Any other suggestions?

Part of the problem might be that I have only one date in the query but 2 look up fields to pull that one date.

 

[skaas]

If I understand correct, the dates are entered automatically into the table?

If so, how is this done? Do the dates come from another table?

If the data is object active then it cannot be bound from the table it is being output to, you would need to create a relationship link to wherever the original data for you dates is coming from.

You want data here --> That comes from here --> That comes from here

It is far easier to do this if you can pull the data direct from its original source otherwise you may have all sorts of problems.

Is there anyway you could post a sample of your work?

It is much easier to solve a problem with an example of the method you are using.