Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    25

    Unanswered: Form that creates Query

    I would like to create a form that allows a user to create a query by selecting items from drop down boxes, then the program works out the SQL and saves it to the database with a name etc to be used with mail merging.

    Similar to a make table query but I would prefer to make a query to conserve disk space.

    Is this possible using VBA?

    The database is for people that don't know how to use Access, and I don't want to have to create a query for them everytime they need one.

    Using MS Access 2003
    Last edited by sjbrown; 05-31-06 at 22:20.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Here is a simple sample of one way....

    Code:
    Dim StrgCriteria As String
    Dim SQLstrg As String
     
    ' Kick Start the Query String... 
    StrgCriteria = "SELECT * FROM myTableName WHERE "
     
    'Products (String Type)
    If IsNull(Me.ProductsCombo) = False Then
       StrgCriteria = StrgCriteria & "[Product]='" & Me.ProductsCombo & "' AND "
    end if
     
    ' Stores (String Type)
    If IsNull(Me.StoresCombo) = False Then
       StrgCriteria = StrgCriteria & "[Store]='" & Me.StoresCombo & "' AND "
    End If
     
    ' State/Province (String Type)
    If IsNull(Me.StateProvinceCombo) = False Then
       StrgCriteria = StrgCriteria & "[StateProvice]='" & Me.StateProvinceCombo & "' AND "
    End If
     
    ' Year (Number Type)
    If IsNull(Me.YearCombo) Then
       StrgCriteria = StrgCriteria & "[Year]=" & Me.YearCombo
    End If
     
    ' Trim Off Spaced from either end of String...
    StrgCriteria = Trim(StrgCriteria)
    ' Remove the AND (if it exists) from the end of String...
    If Right$(StrgCriteria, 4) = " AND" Then
       StrgCriteria = Left$(StrgCriteria, Len(StrgCriteria) - 4)
    End If
      
    ' Set up to save Query to a table...
    ' The UserName field in table must be a Text Type.
    ' The UsersQuery field in table must be a Memo Type.
    SQLstrg = "INSERT INTO SavedQueriesTable (UserName, UsersQuery) VALUES ('" & TheCurrentUsersName & "', '" & StrgCriteria & "'")
     
    ' Save data to the table specified in the above INSET INTO SQL statement...
    CurrentDb.Execute SQLStrg
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    one way to avoid the spare AND/space:

    strSQL = "SELECT * FROM blah WHERE 1=1"

    if something then
    strSQL = strSQL & " And This = That"
    endif

    ....etc


    the bizarre 1=1 is always true so will not hurt your query.

    it eliminates the unwanted AND/space, and it handles the case where nothing is entered by the user (which would otherwise leave you with an unwanted WHERE)

    izy
    currently using SS 2008R2

Posting Permissions

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