Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    6

    Unanswered: Dynamic Select Statement in Query

    Hi,

    I have a database consisting of economics data where the the user defines what data from the database they would like to see by using three different multiselect listboxes which include (country, years, and economic indicators).

    I have (through much assistance from this and other forums) been able to have my multiselect listboxes capture the users selections and then display them in associated textboxes. I am now writing a query that filters the database using the users selections. The database columns are structured as (country, year, gdp, inflation, etc.). There are roughly 80 columns of economic indicators. I have written code so that the database can filter the country and year columns by the users selections just fine. What I am having trouble with is integrating the economic indicators selections into the sql query (as I am assuming it has to be done in the select statement). Basically I would like to know how, if it possible at all, to be able to dynamically query only selected fields using sql code.

    This is what code I have right now that filters the country and year columns:

    *************************************************
    SELECT [Master Data].Country, [Master Data].Year
    FROM [Master Data]
    WHERE (((InParam([Master Data]![Year],[Forms]![Review Selections]![txtyearslist]))=True) AND ((InParam([Master Data]![Country],[Forms]![Review Selections]![txtcountrylist]))=True));
    *************************************************

    The above works just fine to filter the database to include only the user selected years and countries. But I am not quite sure how to include the economic indicator columns as well that the user selected....

    I tried the below, but it didn't work, possibly because it didnt recognize the ....

    *************************************************
    SELECT [Master Data].Country, [Master Data].Year, [Master Data].[Forms]![Review Selections]![txtindicatorslist]
    FROM [Master Data]
    WHERE (((InParam([Master Data]![Year],[Forms]![Review Selections]![txtyearslist]))=True) AND ((InParam([Master Data]![Country],[Forms]![Review Selections]![txtcountrylist]))=True));
    *************************************************

    The query will eventually export data to excel or csv to be used with stata and other statistical and economic modelling softwares.

    Any tips or guidance would be most greatly appreciated!

    Cheers!

  2. #2
    Join Date
    Sep 2009
    Posts
    6
    From some online searches I have been able to get this working and wanted to share it. Still working out some of the kinks, but hopefully I am on the right track. Just basically used trial and error, so if anybody sees anything terribly wrong, please do let me know! thanks!!

    Here is the code for others:

    Private Sub Command173_Click()

    Dim qdfCurr As DAO.QueryDef
    Dim strSQL As String
    Dim indicatorslist As String

    indicatorslist = Forms![Review Selections]!Text176


    strSQL = "SELECT [Master Data].Country, Year, " & "" & indicatorslist & "" & " " & _
    "From [Master Data] " & _
    "WHERE (((InParam([Master Data]!Year,Forms![Review Selections]!Text179))=True) And ((InParam([Master Data]!Country,Forms![Review Selections]!Text180))=True) AND ((InParam([Master Data]!Country,Forms![Review Selections]!Text180))=True)) "

    Set qdfCurr = CurrentDb.QueryDefs("TempQuery")
    qdfCurr.SQL = strSQL
    DoCmd.OpenQuery "TempQuery"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •