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

Posted on 2012-08-10
Last Modified: 2012-08-14
I asked a question about using a list of field names as the source in Comboboxes and moving this to a query.

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.
Question by:Scotto123
    LVL 39

    Expert Comment

    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 Comment

    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.
    LVL 39

    Accepted Solution

    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 Closing Comment

    Works great!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now