• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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

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.
0
Scotto123
Asked:
Scotto123
  • 2
  • 2
1 Solution
 
als315Commented:
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;
0
 
Scotto123Author 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.
0
 
als315Commented:
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

0
 
Scotto123Author Commented:
Works great!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now