I know this is probably a basic question- but Im not finding a clear answer here.
Basically- I have a value that I want to select from a drop down box on a form (not created yet). That value will get inserted into my query for a calculation I am doing. The form will pop up the results of the query in a table/dataset.
How do I designate the variable in the query that is being inserted from the form? I am using Access 2002- is there a way to visually perform this task (ie- drag/drop type thing)?
Thanks guys!
If you right click in the query design and choose "Build", you'll get the expression builder which will let you create a form reference. It will probably look like:
This is dynamic SQL, and it's easier than you may think. There are a lot of ways to do this, but I'll describe the simplest.
1) Make a table that holds your SQL. Call it t_SQL (or something equally descriptive so you know what it is). In that table, make these fields:
SQL_Name (Text)
SQL_Text (Memo)
No primary key is necessary.
2) Make your query in the QBE and get it functional. For example:
Code:
SELECT * FROM YourTable WHERE Date = #9/3/2007#;
All you want is the SQL from that, so go to SQL view and copy it out. Save the query as "q_Template" or something that will let you know it's a template query. Note that you won't need this query for anything -- you can delete it if you want.
3) Go back to t_SQL and come up with a name for the query. Let's say you want to select the date from a drop-down, so call the query "Date_Filter" (put that in the SQL_Name field) and paste the SQL into the SQL_Text field.
4) Now, edit the SQL in the SQL_Text field, and replace the date with a placeholder, like this:
Code:
SELECT * FROM YourTable WHERE Date = #~INSERT_DATE_HERE~#;
5) Now, let's say you have a form where the user enters a date to filter on. On that form, you have a "Get Results" or some other obvious button that will build and execute our query. In that button's OnClick event, you would put something like this:
Code:
Dim strSQL As String
strSQL = DLookup("SQL_Text","t_SQL","t_SQL_Name='Date_Filter'")
strSQL = Replace(strSQL, "~INSERT_DATE_HERE~",YourFormsDateValue)
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
So what did we just do? Look at the steps in the code:
- strSQL uses a DLookup to get the SQL it will need to run. That SQL comes from our t_SQL table, where we have put in a placeholder for the value you want to replace (~INSERT_DATE_HERE~).
- We replace the placeholder value with the value from the form (the Replace command)
- We execute the query in code.
Note that you can also use this instead of the three DoCmd options:
Code:
CurrentDb.Execute strSQL
However, using CurrentDb.Execute will fail if you are using a select query because that only works with action queries (UPDATE, DELETE, APPEND, etc.).
Now you have a structure for dynamic SQL. To add more queries or more placeholders, just go into t_SQL and edit your SQL there. You can copy/paste in SQL from the QBE or just type in new SQL into the SQL_Text field. Just make sure that each SQL_Name is unique (set it to primary key if you want). Then, each time you need dynamic SQL, you use a DLookup on the SQL_Name to get the SQL you need, replace the placeholder(s) with the fields from your form, and then run the SQL. That's it.
SQL statements are limited to 64K (~65,000 characters), and that fits just fine into a Memo. Remember that in Access, you don't have to specify field sizes for text-based fields (text and memo). You can to limit them, but there's no need to for space purposes.
For example, if your numbers are always between 0-255, use a byte. It's one byte instead of two for an Integer, four for a Long Integer, etc.
However, if you leave a text field at its default size (255) and you never store more than 40-50 characters in it (an address, for example), it's not storing 200 or so spaces in there with it to fill out the 255 field size. It will only store the characters needed, and the size will adjust as the content of the field changes. (This is one reason to compact/repair, btw.) You use a specific size on text fields to limit, not to control the size of the DB.
Note that other DBs handle this differently. DB2 will pad with spaces, and I think SQL Server does as well. The auto-adjusting space thing is unique to Access.
my first impulse was to create a memo field. but i've been paranoid about memo fields after reading that they can cause problems with something or other. (how's that for detail? (i think it has to do with upsizing...)) anyway, this has caused me to avoid them somewhat so i created a text field and noted again that my text fields default to size 50 which is what prompted me to ask. i guess i'll use a memo and fget about it.
Would you actually use this technique on non-dynamic queries, as in your example? I can understand the desire to avoid unnecessary objects in the database, but between the SQL not being compiled and having to run a DLookup function plus a Replace function for every parameter, I can't imagine this will run as efficiently as a stored query. Plus:
(i think it has to do with upsizing...)) anyway, this has caused me to avoid them somewhat so i created a text field and noted again that my text fields default to size 50 which is what prompted me to ask. i guess i'll use a memo and fget about it.
