SQL in VBA

[PC User]

I always get confused in converting SQL to VBA and I'm looking for an
easier way to do it. The problem is writing SQL as a string. I
usually try to break it down into parts, but sometimes even the parts
are complicated. For example:
Dim strForm As String
Dim strSQL As String, strWhere As String
Dim strSelect As String, strFrom As String, strJoin As String

strSelect = "SELECT tblMainData.* "
strFrom = "FROM tblMainData "
strJoin = "INNER JOIN tsubPermissionList ON
tblMainData.ResponsibleParty = tsubPermissionList.FullName "

strWhere = "WHERE (((tblMainData.WorkOrder) Like " & "" * "" & "'""
& [Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" & _
"OR (((tblMainData.ActionDescription) Like " & "" * "" & "'"" &
[Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" & _
"OR (((tblMainData.Facility) Like " & "" * "" & "'"" &
[Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" & _
"OR (((tblMainData.ResponsibleParty) Like " & "" * "" & "'"" &
[Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" & _
"OR (((tblMainData.Status) Like " & "" * "" & "'"" &
[Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*"
strSQL = strSelect & strFrom & strJoin & strWhere

The original "WHERE" statement in SQL is:

WHERE (((tblMainData.WorkOrder) Like "*" &
[Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*"))
OR (((tblMainData.ActionDescription) Like "*" &
[Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*"))
OR (((tblMainData.Facility) Like "*" &
[Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*"))
OR (((tblMainData.ResponsibleParty) Like "*" &
[Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*"))
OR (((tblMainData.Status) Like "*" &
[Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*"));

I need help converting the "WHERE" statement into stings - line by
line. Please help.

Thanks,
PC

 

[John Vinson]

I always get confused in converting SQL to VBA

That's because IT CANNOT BE DONE.

SQL is one language. VBA is a different language.

A SQL query is - to Access - just a meaningless string variable which
can be passed to an external program (the JET engine) for processing.

and I'm looking for an
easier way to do it. The problem is writing SQL as a string. I
usually try to break it down into parts, but sometimes even the parts
are complicated. For example:
Dim strForm As String
Dim strSQL As String, strWhere As String
Dim strSelect As String, strFrom As String, strJoin As String

strSelect = "SELECT tblMainData.* "
strFrom = "FROM tblMainData "
strJoin = "INNER JOIN tsubPermissionList ON
tblMainData.ResponsibleParty = tsubPermissionList.FullName "

strWhere = "WHERE (((tblMainData.WorkOrder) Like " & "" * "" & "'""
& [Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" & _
"OR (((tblMainData.ActionDescription) Like " & "" * "" & "'"" &
[Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" & _
"OR (((tblMainData.Facility) Like " & "" * "" & "'"" &
[Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" & _
"OR (((tblMainData.ResponsibleParty) Like " & "" * "" & "'"" &
[Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*" & _
"OR (((tblMainData.Status) Like " & "" * "" & "'"" &
[Forms]![frmMainEntry]![txtCurrentWorkSearch2] " & "'" & "*"
strSQL = strSelect & strFrom & strJoin & strWhere

Well, first off, concatenating an empty string constant "" to a string
does ABSOLUTELY NOTHING.

Secondly, you don't need to concatenate every quotemark and every
asterisk as its own independent boilerplate.

Thirdly, you can concatenate the *value* of the Forms references.
Fourthly, you can use the line continuation characters
blank-underscore to do the concatenation in one long statement, rather
than repeating strSQL = strSQL & on every line.

Try the interlineation below: I'm discarding some of Access
superfluous parentheses.

The original "WHERE" statement in SQL is:

WHERE (((tblMainData.WorkOrder) Like "*" &

strWHERE = "tblMainData.WorkOrder Like *" _

[Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*"))

& [Forms]![frmMainEntry]![txtCurrentWorkSearch2] _

OR (((tblMainData.ActionDescription) Like "*" &

& "* OR (((tblMainData.ActionDescription) Like *" _

[Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*"))

& [Forms]![frmMainEntry]![txtCurrentWorkSearch2] _

OR (((tblMainData.Facility) Like "*" &

& "* OR tblMainData.Facility Like *" _

[Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*"))

& [Forms]![frmMainEntry]![txtCurrentWorkSearch2] _

OR (((tblMainData.ResponsibleParty) Like "*" &

& "* OR tblMainData.ResponsibleParty LIKE *" _

[Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*"))

& [Forms]![frmMainEntry]![txtCurrentWorkSearch2] _

OR (((tblMainData.Status) Like "*" &

& "* OR tblMainData.Status LIKE *" _

[Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*"));

& [Forms]![frmMainEntry]![txtCurrentWorkSearch2] & "*;"

I need help converting the "WHERE" statement into stings - line by
line. Please help.

John W. Vinson[MVP]

 

[PC User]

Thank you, John.

I'll study those 4 guidelines closely. At least the problem
not completely nebulous as it was, but I can see that the conversion
does take some practice. The first thing that I noticed is that all
the parenthesis are eliminated and the quotation marks around the
asterisk are gone.

Thanks again,
PC

 

[Joan Wild]

You might want to try Query Shuttle
http://www.kirsol.com/qryshuttle.php

 

[Bill Smith]

Thank you, Joan. I'll try Query Shuttle this week. It might be just
what I'm looking for.

PC

 

[PC User]

Joan,

I can't get the demo for Query Shuttle to work. It's certainly
a good idea. But I'm wondering of an alternative method. I'm not very
good at converting a query into VBA, so in another situation I'm
looking for a way to reference two update queries that select and
deselect checkboxes. I want to change the recordsource on a subform by
using command buttons on the parent form. I get an error on the
recordsource statement. Is this the right approach?

Dim strSQL As String
Dim Frm As Form
Dim sfN As Form 'Program Notification SubForm
Set Frm = Forms!frmMainEntry.Form
Set sfN = Frm.[fctlNotifications]
strSQL = "qselNotificationSelectionYes"
sfN.RecordSource = strSQL
sfN.Requery

In otherwords, can I reference a query directly without converting it
into SQL?

Thanks,
PC

 

[Joan Wild]

I want to change the recordsource on a subform
by using command buttons on the parent form. I get an error on the
recordsource statement. Is this the right approach?

In otherwords, can I reference a query directly without converting it
into SQL?

No you can't reference a query in this manner. Instead use
Forms!fctlNotifications.RecordSource = "qselNotificationSelectionYes"

 

[PC User]

Thank you, Joan. I did find an answer.
==============================
Dim db As Database
Dim strSQL As String
Dim Frm As Form
Dim sfN As Form 'Program Notification SubForm
Set db = CurrentDb()
Set Frm = Forms!frmMainEntry.Form
Set sfN = Frm.[fctlNotifications].Form
strSQL = "qselNotificationSelectionYes"
CurrentDb.Execute strSQL
sfN.Requery
===============================
However, sometimes in making a recordset, I need to write out an SQL
string for the function. Is there a way to reference the query in the
strSQL, rather than covert the query into VBA? Does QueryDef help with
this?

Thanks again,
PC

 

[Joan Wild]

However, sometimes in making a recordset, I need to write out an SQL
string for the function. Is there a way to reference the query in the
strSQL, rather than covert the query into VBA?

No. John gave you some good pointers earlier in the thread.