We help IT Professionals succeed at work.

Form comboboxes (Field List) used in for reporting - Question 2

Scotto123
Scotto123 asked
on
I asked a question about using a list of field names as the source in Comboboxes and moving this to a query.  

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27821747.html

At the end of the question Cap posted a working solution database.  This is great.  

Now I need the data to be in a query so I can set up reporting.  
As I said in the question, maybe the data should go to a temp table.  

I want to pull the difference between the two selected dates for all records in my database.  
Then in a report, summarize the difference and create totals and avg.
Comment
Watch Question

BRONZE EXPERT

Commented:
You can use this query in sample DB from your previous question:

SELECT BoardData.BoardID, Abs(DateDiff("d",DLookUp([Forms]![Form1]![Combo1],"BoardData","BoardID=" & [Forms]![Form1]![BoardID]),DLookUp([Forms]![Form1]![Combo2],"BoardData","BoardID=" & [Forms]![Form1]![BoardID]))) AS Diff, [Forms]![Form1]![Combo1] & "-" & [Forms]![Form1]![Combo2] AS FIelds
FROM BoardData;

Author

Commented:
This works for each record.  But I want to calculate the dates for all of the records where the form is unbound.  The form should only be used to select the date fields and the query should use the date fields to calculate the difference.
BRONZE EXPERT
Commented:
Try this:
SELECT BoardData.BoardID, Abs(DateDiff("d",DLookUp([Forms]![Form1]![Combo1],"BoardData","BoardID=" & [BoardData]![BoardID]),DLookUp([Forms]![Form1]![Combo2],"BoardData","BoardID=" & [BoardData]![BoardID]))) AS Diff, [Forms]![Form1]![Combo1] & "-" & [Forms]![Form1]![Combo2] AS FIelds
FROM BoardData;

Open in new window

Author

Commented:
Works great!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.