CODEWITHSUNDEEP
ms-accessvbams-access-2007
user1087661
user1087661

Reputation: 101

SQL MS Access Query to Get Text Value from Form Text Box

I have a 'very' long query written through MS Access with lot of unions (around 30) and other dynamic variables.

Now, I need to insert a 'Where' clause in all those unions, and the where clause would be constant and generated through a form (selecting some options, I have written a vb code to create a string containing the where clause.

Is there any way I could use some variables in the SQL query so that the variable gets the where clause from the text result of the form action?

Illustrative: My Query

Select a,b,c,d from
   (select x as a,b,c,d from abc <where clause> union
    select y as a,b,c,d from abc <where clause> union
    select z as a,b,c,d from abc <where clause> union
    select p as a,b,c,d from abc <where clause>)
order by a

The should be populated with the text box result which would look something like 'where b=1'

I thought of writing it in the VB code itself, but to numerous linebreaks and chaning nature of the queries, I do not want to manually keep adding " & _ at the end of each line.

Any help would be greatly appreciated.

Regards, Navs

Upvotes: 1

Views: 7583

Answers (3)

SeanC
SeanC

Reputation: 15923

You could put the Where clauses in a separate query, and not alter the original

Select * 
from MyQuery
Where (a=1 and b=7) or (c=3 and d="StackOverflow")

Upvotes: 1

user1087661
user1087661

Reputation: 101

With little homework, I found how to assign the contents of the text file to a variable. I was then able to change the variable with the code snippets 'Fthiella' had given.

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You can write the WHERE clause like this:

WHERE B=[Forms]![FormName]![FieldName]

so it gets the value from the form. But, if i understand it correctly, in the form there's not just the condition, but there's the whole where clause, that could be 'where b=1' but it could also be 'where c=4' or 'where a=2 and b=6'?

I think there's not a perfect solution to this, you can't use plain Access but you have to write some VBA code, and you can generate the query string with something like this:

$src_query = "Select a,b,c from abc <where clause> union select ..."
$src_query = Replace($src_query,
                     "<where clause>",
                     [Forms]![FormName]![WhereClause])

then you just have to modify the query programmatically:

CurrentDb.QueryDefs("QueryName").SQL = $src_query

and now your query is ready to be executed.

Upvotes: 2

Related Questions