I am creating a text string to insert into the CreateQueryDef command but the resulting query has the dropdown field values in brackets. They need to be in quotes.
(This is my first attempt at this type of thing in VB)
---------------------------------------------------------
Example:
Dim dbs As Database, IngQ As QueryDef, strSQL As String
Set dbs = CurrentDb
'Create a query string based on the form fields
strSQL = make_query_str()
'Create the qctual query in Access
Set IngQ = dbs.CreateQueryDef("CustomQuery", strSQL)
***********************************
Private Function make_query_str()
***********************************
' 15 steps here to read all the form fields and create the resulting string:
MyStr="SELECT <my fields here> INTO <my destination here> FROM <source tables here> WHERE (([ID's SPECIALTIES].SPECIALTY)=IM) AND ([NEW FINAL TABLE].TITLECODE)=30))"
------------------------------------------------------------
The two problems that occur are:
1: The resulting QueryDef puts brackets around IM and 30
resulting in [IM] and [30]. When the Query is run it askes for input from the two bracketed values. These two values are retrieved from the dropdown sellection on the form.
2: I tried modifying the resulting string to have double quotes around the IM and 30 values by using "\"" but MS VB converts them to " \ " " (spaces before and after the \) as I exit the line. How can get VB to accept the quotes? Will accepting the quotes correct the problem?
(This is my first attempt at this type of thing in VB)
---------------------------------------------------------
Example:
Dim dbs As Database, IngQ As QueryDef, strSQL As String
Set dbs = CurrentDb
'Create a query string based on the form fields
strSQL = make_query_str()
'Create the qctual query in Access
Set IngQ = dbs.CreateQueryDef("CustomQuery", strSQL)
***********************************
Private Function make_query_str()
***********************************
' 15 steps here to read all the form fields and create the resulting string:
MyStr="SELECT <my fields here> INTO <my destination here> FROM <source tables here> WHERE (([ID's SPECIALTIES].SPECIALTY)=IM) AND ([NEW FINAL TABLE].TITLECODE)=30))"
------------------------------------------------------------
The two problems that occur are:
1: The resulting QueryDef puts brackets around IM and 30
resulting in [IM] and [30]. When the Query is run it askes for input from the two bracketed values. These two values are retrieved from the dropdown sellection on the form.
2: I tried modifying the resulting string to have double quotes around the IM and 30 values by using "\"" but MS VB converts them to " \ " " (spaces before and after the \) as I exit the line. How can get VB to accept the quotes? Will accepting the quotes correct the problem?