SQL string in VBA

[Robert5833]

Good day all;

I'm having a problem with an SQL string built in VBA. On my forms I'm using
the value in a Combo Box (e.g., cboSelChkList) as part of the SQL string to
filter the form’s recordset. The SQL created in VBA replaces the form’s
Record Source. This works fine in that scenario, but does not work in
another. The subroutine to build the SQL is as follows;

Public Sub SelectRecords()
Dim varWhereClause As Variant
Dim strAND As String

varWhereClause = Null
strAND = " AND "

If cboSelChkList & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
"tblWorkEventWork.fldChecklistHeader = """ & _
cboSelChkList & """"
End If

varWhereClause = " WHERE " + varWhereClause
Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)

The above subroutine works in combination with two global procedures; to
build the SQL; and to replace the Record Source string of the calling object.

On the form where this procedure does work, when I watch the code in break
mode, the varWhereClause statement returns the value (built SQL string)
showing the concatenated referenced control value. In another application of
this procedure that does not work, the varWhereClause statement only returns
the source table in the SQL, but not the value of the referenced control in
the built SQL. The controls on both forms are Combo Boxes, with SQL strings
looking up a value in another table. The value in those controls are looking
up a primary key (integer), but displaying a second value (text; field name).

Where this procedure does work, I’m using the VBA produced SQL to filter the
records for that form. Where the procedure does not work is when I’m trying
to use the form's control values to filter the records for a report.

I’ve incorporated the subroutine into the report’s On Open function, but
when I run the report it doesn’t return any records (obviously because the
SQL isn’t completed). As a work around I’ve managed to filter the report with
a macro, but I prefer to use code.

I don't get any error codes or numbers; just a blank report.

Any suggestions would be greatly appreciated.

Robert

 

[Microsoft News]

Hey Robert. Kind of tough to diagnose without the code for the other
functions being called. Code does appear to be more complex than necessary.
What happens if you change:

Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)

to

Me.RecordSource = "SELECT [FieldNames] FROM [TableName] " & varWhereClause


RJ
Database Whiz Consulting
www.databasewhiz.com

 

[Robert5833]

Hi RJ,

Thank you for the reply. I had considered posting the related procedures,
but they are very lengthy.

I tried what you suggested, and it works fine!

Also, I was able to determine that the two routines that build the SQL and
replace the Record Source were working correctly. The problem was that in one
of the applications I had a table field data type set to Number (three tables
removed in a Look Up control), so the SQL failed (though the procedure had
built the statement correctly).

The statement you suggested though will help me in a lot of other instances.

On a related note; the problem I stated in the original post that turned out
to be a data type mismatch, I could use some advice on the following:

The global procedure I'm using to build the Select, From, and Where clauses
of the SQL will accept any “valid†SQL statement, presumably on either Text
or Number data types. I notice that the string being passed to that global
procedure where the SQL wouldn’t return a value is written to return a Text
string, as noted by the quotation marks surrounding the form’s controls.

Here’s the original statement:

If cboSelChkList & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
"tblWorkEventWork.fldChecklistHeader = """ & _
cboSelChkList & """"
End If

After correcting the data type mismatch, the above routine works fine. But
there are instances where I would like it to return a Number type, but I’m
struggling with the placement and use of quotation marks. If I alter the
statement to this, would it return a Number instead?

If cboSelChkList & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
tblWorkEventWork.fldChecklistHeader = & _
cboSelChkList

If my understanding (newly acquired I might add) is correct that an SQL
Select statement in the VBA environment does not require enclosing quotation
marks to return a Number data type, the above statement should pass a Number
value to my SQL Build procedure.

Any suggestions would be appreciated.

Thank you!
Robert

Hey Robert. Kind of tough to diagnose without the code for the other
functions being called. Code does appear to be more complex than necessary.
What happens if you change:

Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)

to

Me.RecordSource = "SELECT [FieldNames] FROM [TableName] " & varWhereClause


RJ
Database Whiz Consulting
www.databasewhiz.com

Good day all;

I'm having a problem with an SQL string built in VBA. On my forms I'm
using
the value in a Combo Box (e.g., cboSelChkList) as part of the SQL string
to
filter the form's recordset. The SQL created in VBA replaces the form's
Record Source. This works fine in that scenario, but does not work in
another. The subroutine to build the SQL is as follows;

Public Sub SelectRecords()
Dim varWhereClause As Variant
Dim strAND As String

varWhereClause = Null
strAND = " AND "

If cboSelChkList & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
"tblWorkEventWork.fldChecklistHeader = """ & _
cboSelChkList & """"
End If

varWhereClause = " WHERE " + varWhereClause
Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)

The above subroutine works in combination with two global procedures; to
build the SQL; and to replace the Record Source string of the calling
object.

On the form where this procedure does work, when I watch the code in break
mode, the varWhereClause statement returns the value (built SQL string)
showing the concatenated referenced control value. In another application
of
this procedure that does not work, the varWhereClause statement only
returns
the source table in the SQL, but not the value of the referenced control
in
the built SQL. The controls on both forms are Combo Boxes, with SQL
strings
looking up a value in another table. The value in those controls are
looking
up a primary key (integer), but displaying a second value (text; field
name).

Where this procedure does work, I'm using the VBA produced SQL to filter
the
records for that form. Where the procedure does not work is when I'm
trying
to use the form's control values to filter the records for a report.

I've incorporated the subroutine into the report's On Open function, but
when I run the report it doesn't return any records (obviously because the
SQL isn't completed). As a work around I've managed to filter the report
with
a macro, but I prefer to use code.

I don't get any error codes or numbers; just a blank report.

Any suggestions would be greatly appreciated.

Robert

 

[Ray Jefferson]

If you change it in that manner, you'll get a syntax error. What you really
would want to do is this if tblWorkEventWork.fldCheckListHeader is a
numerical data type:

If cboSelChkList & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
"tblWorkEventWork.fldChecklistHeader = " & _
cboSelChkList
End If


You would do it this way if tblWorkEventWork.fldCheckListHeader is a date
data type.

If cboSelChkList & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
"tblWorkEventWork.fldChecklistHeader = #" & _
cboSelChkList & "#"
End If


In your scenario though, you only have 1 criteria item, cboSelChkList so I'm
having trouble understanding the complexity of your code and the need to
call lenghty procedures.

If IsNull(cboSelChkList) OR cboSelChkList = "<all>" Then
Me.RecordSource = "SELECT [FieldNames] FROM [TableName]"
ELSE
Me.RecordSource = "SELECT [FieldNames] FROM [TableName] WHERE
tblWorkEventWork.fldChecklistHeader = """ & cboSelChkList & """"
END IF


Let me know if this helps.


RJ
Database Whiz Consulting
www.databasewhiz.com



The "tblWorkEventWork.fldChecklistHeader = " needs to be interpreted
literally as is, and thus must be contained in quotes.

Hi RJ,

Thank you for the reply. I had considered posting the related procedures,
but they are very lengthy.

I tried what you suggested, and it works fine!

Also, I was able to determine that the two routines that build the SQL and
replace the Record Source were working correctly. The problem was that in
one
of the applications I had a table field data type set to Number (three
tables
removed in a Look Up control), so the SQL failed (though the procedure had
built the statement correctly).

The statement you suggested though will help me in a lot of other
instances.

On a related note; the problem I stated in the original post that turned
out
to be a data type mismatch, I could use some advice on the following:

The global procedure I'm using to build the Select, From, and Where
clauses
of the SQL will accept any "valid" SQL statement, presumably on either
Text
or Number data types. I notice that the string being passed to that global
procedure where the SQL wouldn't return a value is written to return a
Text
string, as noted by the quotation marks surrounding the form's controls.

Here's the original statement:

If cboSelChkList & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
"tblWorkEventWork.fldChecklistHeader = """ & _
cboSelChkList & """"
End If

After correcting the data type mismatch, the above routine works fine. But
there are instances where I would like it to return a Number type, but I'm
struggling with the placement and use of quotation marks. If I alter the
statement to this, would it return a Number instead?

If cboSelChkList & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
tblWorkEventWork.fldChecklistHeader = & _
cboSelChkList

If my understanding (newly acquired I might add) is correct that an SQL
Select statement in the VBA environment does not require enclosing
quotation
marks to return a Number data type, the above statement should pass a
Number
value to my SQL Build procedure.

Any suggestions would be appreciated.

Thank you!
Robert

Hey Robert. Kind of tough to diagnose without the code for the other
functions being called. Code does appear to be more complex than
necessary.
What happens if you change:

Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)

to

Me.RecordSource = "SELECT [FieldNames] FROM [TableName] " &
varWhereClause


RJ
Database Whiz Consulting
www.databasewhiz.com

Good day all;

I'm having a problem with an SQL string built in VBA. On my forms I'm
using
the value in a Combo Box (e.g., cboSelChkList) as part of the SQL
string
to
filter the form's recordset. The SQL created in VBA replaces the form's
Record Source. This works fine in that scenario, but does not work in
another. The subroutine to build the SQL is as follows;

Public Sub SelectRecords()
Dim varWhereClause As Variant
Dim strAND As String

varWhereClause = Null
strAND = " AND "

If cboSelChkList & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
"tblWorkEventWork.fldChecklistHeader = """ & _
cboSelChkList & """"
End If

varWhereClause = " WHERE " + varWhereClause
Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)

The above subroutine works in combination with two global procedures;
to
build the SQL; and to replace the Record Source string of the calling
object.

On the form where this procedure does work, when I watch the code in
break
mode, the varWhereClause statement returns the value (built SQL string)
showing the concatenated referenced control value. In another
application
of
this procedure that does not work, the varWhereClause statement only
returns
the source table in the SQL, but not the value of the referenced
control
in
the built SQL. The controls on both forms are Combo Boxes, with SQL
strings
looking up a value in another table. The value in those controls are
looking
up a primary key (integer), but displaying a second value (text; field
name).

Where this procedure does work, I'm using the VBA produced SQL to
filter
the
records for that form. Where the procedure does not work is when I'm
trying
to use the form's control values to filter the records for a report.

I've incorporated the subroutine into the report's On Open function,
but
when I run the report it doesn't return any records (obviously because
the
SQL isn't completed). As a work around I've managed to filter the
report
with
a macro, but I prefer to use code.

I don't get any error codes or numbers; just a blank report.

Any suggestions would be greatly appreciated.

Robert

 

[Robert5833]

Ray,
Thank you so much for your help and clarification on this. I have used your
suggested code strings and my procedure is working great now! And my code is
a lot simpler too, and that's a good thing!

Thanks again!

Robert

If you change it in that manner, you'll get a syntax error. What you really
would want to do is this if tblWorkEventWork.fldCheckListHeader is a
numerical data type:

If cboSelChkList & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
"tblWorkEventWork.fldChecklistHeader = " & _
cboSelChkList
End If


You would do it this way if tblWorkEventWork.fldCheckListHeader is a date
data type.

If cboSelChkList & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
"tblWorkEventWork.fldChecklistHeader = #" & _
cboSelChkList & "#"
End If


In your scenario though, you only have 1 criteria item, cboSelChkList so I'm
having trouble understanding the complexity of your code and the need to
call lenghty procedures.

If IsNull(cboSelChkList) OR cboSelChkList = "<all>" Then
Me.RecordSource = "SELECT [FieldNames] FROM [TableName]"
ELSE
Me.RecordSource = "SELECT [FieldNames] FROM [TableName] WHERE
tblWorkEventWork.fldChecklistHeader = """ & cboSelChkList & """"
END IF


Let me know if this helps.


RJ
Database Whiz Consulting
www.databasewhiz.com



The "tblWorkEventWork.fldChecklistHeader = " needs to be interpreted
literally as is, and thus must be contained in quotes.

Hi RJ,

Thank you for the reply. I had considered posting the related procedures,
but they are very lengthy.

I tried what you suggested, and it works fine!

Also, I was able to determine that the two routines that build the SQL and
replace the Record Source were working correctly. The problem was that in
one
of the applications I had a table field data type set to Number (three
tables
removed in a Look Up control), so the SQL failed (though the procedure had
built the statement correctly).

The statement you suggested though will help me in a lot of other
instances.

On a related note; the problem I stated in the original post that turned
out
to be a data type mismatch, I could use some advice on the following:

The global procedure I'm using to build the Select, From, and Where
clauses
of the SQL will accept any "valid" SQL statement, presumably on either
Text
or Number data types. I notice that the string being passed to that global
procedure where the SQL wouldn't return a value is written to return a
Text
string, as noted by the quotation marks surrounding the form's controls.

Here's the original statement:

If cboSelChkList & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
"tblWorkEventWork.fldChecklistHeader = """ & _
cboSelChkList & """"
End If

After correcting the data type mismatch, the above routine works fine. But
there are instances where I would like it to return a Number type, but I'm
struggling with the placement and use of quotation marks. If I alter the
statement to this, would it return a Number instead?

If cboSelChkList & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
tblWorkEventWork.fldChecklistHeader = & _
cboSelChkList

If my understanding (newly acquired I might add) is correct that an SQL
Select statement in the VBA environment does not require enclosing
quotation
marks to return a Number data type, the above statement should pass a
Number
value to my SQL Build procedure.

Any suggestions would be appreciated.

Thank you!
Robert

Hey Robert. Kind of tough to diagnose without the code for the other
functions being called. Code does appear to be more complex than
necessary.
What happens if you change:

Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)

to

Me.RecordSource = "SELECT [FieldNames] FROM [TableName] " &
varWhereClause


RJ
Database Whiz Consulting
www.databasewhiz.com


Good day all;

I'm having a problem with an SQL string built in VBA. On my forms I'm
using
the value in a Combo Box (e.g., cboSelChkList) as part of the SQL
string
to
filter the form's recordset. The SQL created in VBA replaces the form's
Record Source. This works fine in that scenario, but does not work in
another. The subroutine to build the SQL is as follows;

Public Sub SelectRecords()
Dim varWhereClause As Variant
Dim strAND As String

varWhereClause = Null
strAND = " AND "

If cboSelChkList & "" <> "<all>" Then
varWhereClause = (varWhereClause + strAND) & _
"tblWorkEventWork.fldChecklistHeader = """ & _
cboSelChkList & """"
End If

varWhereClause = " WHERE " + varWhereClause
Me.RecordSource = ReplaceWhereClause(Me.RecordSource, varWhereClause)

The above subroutine works in combination with two global procedures;
to
build the SQL; and to replace the Record Source string of the calling
object.

On the form where this procedure does work, when I watch the code in
break
mode, the varWhereClause statement returns the value (built SQL string)
showing the concatenated referenced control value. In another
application
of
this procedure that does not work, the varWhereClause statement only
returns
the source table in the SQL, but not the value of the referenced
control
in
the built SQL. The controls on both forms are Combo Boxes, with SQL
strings
looking up a value in another table. The value in those controls are
looking
up a primary key (integer), but displaying a second value (text; field
name).

Where this procedure does work, I'm using the VBA produced SQL to
filter
the
records for that form. Where the procedure does not work is when I'm
trying
to use the form's control values to filter the records for a report.

I've incorporated the subroutine into the report's On Open function,
but
when I run the report it doesn't return any records (obviously because
the
SQL isn't completed). As a work around I've managed to filter the
report
with
a macro, but I prefer to use code.

I don't get any error codes or numbers; just a blank report.

Any suggestions would be greatly appreciated.

Robert