Passing a variable into a SQL statement using Like "*" & varCurrentForm& "*"));

[CES]

All,
I know the issue is in the number of quotation marks that surround the variable however with every attempt to solve the problem I continue to get and "Enter Parameter Value" dialog box or the sequel returns no records.

SELECT [_Print].[printID], [_Print].[printTitle], [_Print].[printExceptsDateVar] FROM _Print WHERE ((([_Print].[printForms]) Like "*" & varCurrentForm & "*"));

And unfortunately I can't seem, up with the right combination of quotation marks. Any help on this would be greatly appreciated, Thanks in advance. - CES

 

[Guest]

Is that a query you are trying to run?

If so, you can't pass variables to a query, you have to do that using a
function
===========================
Create a function i a module:
Function GetParameter()
GetParameter= varCurrentForm
End Function
===========================
In the SQL:
SELECT [_Print].[printID], [_Print].[printTitle],
[_Print].[printExceptsDateVar] FROM _Print WHERE ((([_Print].[printForms])
Like "*" & GetParameter() & "*"));

 

[Marshall Barton]

I know the issue is in the number of quotation marks
that surround the variable however with every
attempt to solve the problem I continue to get and
"Enter Parameter Value" dialog box or the sequel
returns no records.

SELECT [_Print].[printID], [_Print].[printTitle],
[_Print].[printExceptsDateVar]
FROM _Print WHERE ((([_Print].[printForms])
Like "*" & varCurrentForm & "*"));

If that's a saved query, you can not do this because queries
are unaware of VBA variables. You can either put the value
in a form text box and use the text box reference instead.
Alternatively, you can create a Public function in a
standard module that returns the value of the variable.

If you are constructing the SQL statement in a VBA
procedure, then the code would be like:

strSQL = "SELECT [_Print].[printID], " _
& "[_Print].[printTitle], " _
& "[_Print].[printExceptsDateVar] " _
& "FROM _Print " _
& "WHERE [_Print].[printForms] " _
& "Like ""*" & varCurrentForm & "*"""

 

[CES]

I know the issue is in the number of quotation marks
that surround the variable however with every
attempt to solve the problem I continue to get and
"Enter Parameter Value" dialog box or the sequel
returns no records.

SELECT [_Print].[printID], [_Print].[printTitle],
[_Print].[printExceptsDateVar]
FROM _Print WHERE ((([_Print].[printForms])
Like "*" & varCurrentForm & "*"));

If that's a saved query, you can not do this because queries
are unaware of VBA variables. You can either put the value
in a form text box and use the text box reference instead.
Alternatively, you can create a Public function in a
standard module that returns the value of the variable.

If you are constructing the SQL statement in a VBA
procedure, then the code would be like:

strSQL = "SELECT [_Print].[printID], " _
& "[_Print].[printTitle], " _
& "[_Print].[printExceptsDateVar] " _
& "FROM _Print " _
& "WHERE [_Print].[printForms] " _
& "Like ""*" & varCurrentForm & "*"""

Sorry... I'm using the SQL as a forms rowSource. Unfortunately the above dosen't work - CES

 

[Marshall Barton]

I know the issue is in the number of quotation marks
that surround the variable however with every
attempt to solve the problem I continue to get and
"Enter Parameter Value" dialog box or the sequel
returns no records.

SELECT [_Print].[printID], [_Print].[printTitle],
[_Print].[printExceptsDateVar]
FROM _Print WHERE ((([_Print].[printForms])
Like "*" & varCurrentForm & "*"));

If that's a saved query, you can not do this because queries
are unaware of VBA variables. You can either put the value
in a form text box and use the text box reference instead.
Alternatively, you can create a Public function in a
standard module that returns the value of the variable.

If you are constructing the SQL statement in a VBA
procedure, then the code would be like:

strSQL = "SELECT [_Print].[printID], " _
& "[_Print].[printTitle], " _
& "[_Print].[printExceptsDateVar] " _
& "FROM _Print " _
& "WHERE [_Print].[printForms] " _
& "Like ""*" & varCurrentForm & "*"""

Sorry... I'm using the SQL as a forms rowSource. Unfortunately the above dosen't work - CES

You really should make a bigger effort to provide us with
useful information. "dosen't work" doesn't cut it.

Tell us more about what you are doing.

Where is this variable being set?

Are you using the form's open event to set its RecordSource
or is it a saved query?

Did you try creating the Public function? If you did, what
happened? If you didn't try it, why not?

 

[CES]

CES wrote:
I know the issue is in the number of quotation marks
that surround the variable however with every
attempt to solve the problem I continue to get and
"Enter Parameter Value" dialog box or the sequel
returns no records.

SELECT [_Print].[printID], [_Print].[printTitle],
[_Print].[printExceptsDateVar]
FROM _Print WHERE ((([_Print].[printForms])
Like "*" & varCurrentForm & "*"));

If that's a saved query, you can not do this because queries
are unaware of VBA variables. You can either put the value
in a form text box and use the text box reference instead.
Alternatively, you can create a Public function in a
standard module that returns the value of the variable.

If you are constructing the SQL statement in a VBA
procedure, then the code would be like:

strSQL = "SELECT [_Print].[printID], " _
& "[_Print].[printTitle], " _
& "[_Print].[printExceptsDateVar] " _
& "FROM _Print " _
& "WHERE [_Print].[printForms] " _
& "Like ""*" & varCurrentForm & "*"""

Sorry... I'm using the SQL as a forms rowSource. Unfortunately the above dosen't work - CES

You really should make a bigger effort to provide us with
useful information. "dosen't work" doesn't cut it.

Tell us more about what you are doing.

Where is this variable being set?

Are you using the form's open event to set its RecordSource
or is it a saved query?

Did you try creating the Public function? If you did, what
happened? If you didn't try it, why not?

I'm setting the SQL in a "Form\Design View\Property Sheet\Row Source" window. As a way of testing the SQL... placing the SQL their returns 0 records, I removed one set of quotation marks so the SQL is as follows:

SELECT [_Print].[printID], [_Print].[printTitle], [_Print].[printExceptsDateVar] FROM _Print WHERE [_Print].[printForms] Like ""*" & varCurrentForm & "*""

However I just found out that setting it within a module:

Forms(varCurrentForm).bxCboPrintReports.RowSource = "SELECT [_Print].[printID], [_Print].[printTitle], [_Print].[printExceptsDateVar] FROM _Print WHERE [_Print].[printForms] Like ""*" & varCurrentForm & "*"""

Will properly set the ComboBox... my problem is that the SQL is coming from a Table Field so I'm trying to set the SQL as follows:

Forms(varCurrentForm).bxCboPrintReports.RowSource = .Fields("sqlPrintReportsSQL")

I'm assuming the problem that I have setting the Row Source in the forms design view is the same problem when I try to set the SQL from the tables field.

- CES

 

[Marshall Barton]

I'm setting the SQL in a "Form\Design View\Property Sheet\Row Source" window. As a way of testing the SQL... placing the SQL their returns 0 records, I removed one set of quotation marks so the SQL is as follows:

SELECT [_Print].[printID], [_Print].[printTitle], [_Print].[printExceptsDateVar] FROM _Print WHERE [_Print].[printForms] Like ""*" & varCurrentForm & "*""

However I just found out that setting it within a module:

Forms(varCurrentForm).bxCboPrintReports.RowSource = "SELECT [_Print].[printID], [_Print].[printTitle], [_Print].[printExceptsDateVar] FROM _Print WHERE [_Print].[printForms] Like ""*" & varCurrentForm & "*"""

Will properly set the ComboBox... my problem is that the SQL is coming from a Table Field so I'm trying to set the SQL as follows:

Forms(varCurrentForm).bxCboPrintReports.RowSource = .Fields("sqlPrintReportsSQL")

I'm assuming the problem that I have setting the Row Source in the forms design view is the same problem when I try to set the SQL from the tables field.

Right. The problem is that VBA never sees the
concatenations and nothing else understands VBA variables.

There are a couple of things you can do, but using a
function is the easiest. You can set the SQL in the combo
box's RowSource, just change it to:

SELECT printID, printTitle, printExceptsDateVar
FROM _Print
WHERE printForms Like "*" & GetCurrentForm() & "*"

Then create the function in a standard module:

Public Function GetCurrentForm()
GetCurrentForm = thevariablereference
End Function

I still don't know where the variable is declared, but you
could make it a Public (module level) variable in the same
module as the function. If you prefer to declare the
variable in the form's module, you can reference it in the
function with the syntax: Forms!theformname.varCurrentForm