Passing variables from Form to Query

Posted on 2012-08-20
Medium Priority
Last Modified: 2012-08-20
I would like to have a form with fields in which I can enter strings (which will be used as criteria in queries) and when a button below is clicked, it will step through a number of queries (macro) inserting the appropriate variables into the appropriate query criteria.

Any answers/links/tutorials/keywords to search for would help.

Question by:dgran
  • 2
LVL 49

Accepted Solution

Dale Fye earned 2000 total points
ID: 38312925
Depending on what you have in mind, the simplest method is to refer directly to the form in your query.  For example, you have a control txt_StartDate on your form, then in your query you could use a syntax like:

SELECT * FROM yourTable WHERE [StartDate] > Forms!YourFormName!txt_StartDate

Problem is that this syntax might not work because Access/Jet might interpret the form value as a string, rather than a date, so you might need to explicitly declare the variable.

PARAMETERS [Forms]![YourFormName]![txt_StartDate] DateTime;
SELECT * FROM yourTable WHERE [StartDate] > Forms!YourFormName!txt_StartDate

Or you might want to simply evaluate the value of the textbox:

SELECT * FROM yourTable WHERE [StartDate] > Eval(Forms!YourFormName!txt_StartDate)

Author Comment

ID: 38313671
I'm getting an error on this SQL:

SELECT TOP [Forms]![Form1]![Quantity] * INTO DataPull
FROM [Table1]

I'm trying to subsitute the 5100 below with a variable from the form but keep getting an error.

SELECT TOP 5100 * INTO DataPull
FROM [Table1]

Error States: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

I've triple checked the spelling and it is correct.  Any other ideas?
LVL 49

Expert Comment

by:Dale Fye
ID: 38314406
Sorry, that is one place that you cannot use a variable.

If you want to do something like that, you are going to have to write the SQL on the fly, in the click event of a button or something like that.

Private sub cmd_QueryUpdate_Click

    dim strSQL as string

    strSQL = "SELECT TOP " & me.Quantity & " * INTO DataPull " _
               & "FROM [Table1] ORDER BY Rnd([ID]);"
    currentdb.Execute strSQL, dbFailOnError

End Sub

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

850 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