Thread: Form that creates Query
05-31-06, 20:53 #1Registered User
- Join Date
- Nov 2004
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 21:20.
06-01-06, 01:40 #2Stuck on my opinions...
- Join Date
- Nov 2003
Here is a simple sample of one way....
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 SQLStrgEnvironment:
Self Taught In ALL Environments.....And It Shows!
06-01-06, 02:19 #3Cavalier King Charles
- Join Date
- Dec 2002
- Préverenges, Switzerland
one way to avoid the spare AND/space:
strSQL = "SELECT * FROM blah WHERE 1=1"
if something then
strSQL = strSQL & " And This = That"
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)
izycurrently using SS 2008R2