Can someone please explain this VB?

[John Ortt]

Hi there,

I am trying to understand a section of VB code used to update our database
and the following line is confusing me:

Coop_SQL = "SELECT * FROM COOP_Purchase_Orders_Table WHERE [COL_Link] =""" &
Import_Query![Col_link] & """ ORDER BY [Firm_Date]"

I can't understand why there are 3 quotation marks or even what it is trying
to do exactly.

I understand that it is trying to run an SQL select query on the
Purchase_Orders_Table and that it is ordering by Firm_Date but aside from
that I'm lost (i.e what is the Import_Query bit all about?)

Any help would be greatly appreciated.

Thankyou,

John


P.S. I will enclose the full Function below to give an idea of context

Function Update_Details_With_COOP_Data()
Dim MyDB As Database, Import_Query As Recordset, MyWrk As Workspace
Dim strSQL As String, Coop_SQL As String, COOP_Data As Recordset
Set MyWrk = DBEngine.Workspaces(0)
Set MyDB = CurrentDb
strSQL = "SELECT * FROM Details_Table"
Set Import_Query = MyDB.OpenRecordset(strSQL)
With Import_Query
..MoveFirst
Do Until .EOF
Coop_SQL = "SELECT * FROM COOP_Purchase_Orders_Table WHERE
[COL_Link] =""" & Import_Query![Col_link] & """ ORDER BY [Firm_Date]"
Set COOP_Data = MyDB.OpenRecordset(Coop_SQL)
If COOP_Data.RecordCount > 0 Then
.Edit
![AQUISITION] = COOP_Data![AQUISITION]
If COOP_Data![Firm_Date] <> "" Then ![Firm_Date] =
COOP_Data![Firm_Date]
![PO_Number] = COOP_Data![PO_Number]
![System] = COOP_Data![System]
![MMS_Link] = COOP_Data![MMS_Link]
.Update
End If

.MoveNext
Loop
End With
DoEvents
End Function

 

[Andi Mayer]

Hi there,

I am trying to understand a section of VB code used to update our database
and the following line is confusing me:

Coop_SQL = "SELECT * FROM COOP_Purchase_Orders_Table WHERE [COL_Link] =""" &
Import_Query![Col_link] & """ ORDER BY [Firm_Date]"

I can't understand why there are 3 quotation marks or even what it is trying
to do exactly.

this is easy:
you have single Quotes and Double Quotes

the SQL string has to be inclosed in doubleQuotes, but you need also
quotes around the Value from COL_Link, therefore you either use a
single quote or you double the doubleQuote, to tell the compiler you
need a quote.

I would write: WHERE [COL_Link] ='" & Import_Query![Col_link] & "' OR

this spares my to write two " and i can easy see that
Import_Query![Col_link] is in single quotes (because it is a string)

do I have you confused even more?

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

 

[John Ortt]

Thankyou Manfred,

That has cleared things up a lot. I was getting confused as I had some
strings with the single quotation marks and others with double.

Now I know that they are interchangable I will use the simpler single double
syntax.

Thankyou once again,

John

Hi there,

I am trying to understand a section of VB code used to update our database
and the following line is confusing me:

Coop_SQL = "SELECT * FROM COOP_Purchase_Orders_Table WHERE [COL_Link] =""" &
Import_Query![Col_link] & """ ORDER BY [Firm_Date]"

I can't understand why there are 3 quotation marks or even what it is trying
to do exactly.

this is easy:
you have single Quotes and Double Quotes

the SQL string has to be inclosed in doubleQuotes, but you need also
quotes around the Value from COL_Link, therefore you either use a
single quote or you double the doubleQuote, to tell the compiler you
need a quote.

I would write: WHERE [COL_Link] ='" & Import_Query![Col_link] & "' OR

this spares my to write two " and i can easy see that
Import_Query![Col_link] is in single quotes (because it is a string)

do I have you confused even more?

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

 

[MacDermott]

Let's take a simpler example:
Suppose you built a simple form with a single textbox and a button.
You put the text John in the textbox, and push the button.
A message pops up, saying Hello, John!
The code behind the button might look like this:
dim MyStr as String
MyStr="Hello, " & Text1
msgbox MyStr

See how you've built the string MyStr by concatenating the value in Text1
with a static string?
That's the sort of thing your code is doing - it's mostly static text
"SELECT ...", but it needs to include a value with is stored dynamically.
The value to be added is stored in Import_Query![Col_link] , which
should be defined earlier in the code. (My best guess is that Import_Query
refers to a recordset which is defined earlier.)

As for the triple quotations, let's go back to the simple example.
Suppose, instead of
Hello, John
you wanted the message to say
Hello, "John"

If you wrote
MyStr="Hello, "" & Text1 & """
Access could parse out
"Hello, "
as the first static string, and throw an error because it didn't know what
to do with the next ".
To avoid that, there's a convention that if you put "" together, that gets
treated as a single " within the string.
So actually, if you used the syntax above, what you'd get in your messagebox
would be
Hello, " & Text1 "
No matter what you put in Text1.
The reason being that when Access sees "", it doesn't think it's at the end
of a string, the way it does when it sees a single ". So the third " tells
it that this is the end of the string.

Don't know if this is any clearer than the previous post, but at least it
puts things a bit differently.

Hi there,

I am trying to understand a section of VB code used to update our database
and the following line is confusing me:

Coop_SQL = "SELECT * FROM COOP_Purchase_Orders_Table WHERE [COL_Link] =""" &
Import_Query![Col_link] & """ ORDER BY [Firm_Date]"

I can't understand why there are 3 quotation marks or even what it is trying
to do exactly.

I understand that it is trying to run an SQL select query on the
Purchase_Orders_Table and that it is ordering by Firm_Date but aside from
that I'm lost (i.e what is the Import_Query bit all about?)

Any help would be greatly appreciated.

Thankyou,

John


P.S. I will enclose the full Function below to give an idea of context

Function Update_Details_With_COOP_Data()
Dim MyDB As Database, Import_Query As Recordset, MyWrk As Workspace
Dim strSQL As String, Coop_SQL As String, COOP_Data As Recordset
Set MyWrk = DBEngine.Workspaces(0)
Set MyDB = CurrentDb
strSQL = "SELECT * FROM Details_Table"
Set Import_Query = MyDB.OpenRecordset(strSQL)
With Import_Query
.MoveFirst
Do Until .EOF
Coop_SQL = "SELECT * FROM COOP_Purchase_Orders_Table WHERE
[COL_Link] =""" & Import_Query![Col_link] & """ ORDER BY [Firm_Date]"
Set COOP_Data = MyDB.OpenRecordset(Coop_SQL)
If COOP_Data.RecordCount > 0 Then
.Edit
![AQUISITION] = COOP_Data![AQUISITION]
If COOP_Data![Firm_Date] <> "" Then ![Firm_Date] =
COOP_Data![Firm_Date]
![PO_Number] = COOP_Data![PO_Number]
![System] = COOP_Data![System]
![MMS_Link] = COOP_Data![MMS_Link]
.Update
End If

.MoveNext
Loop
End With
DoEvents
End Function

 

[John Ortt]

Very helpful, Thanks.

I thought I had the gist of it from Andi's post but this has helped to
reinforce the knowledge.

Thanks MacDermott

Let's take a simpler example:
Suppose you built a simple form with a single textbox and a button.
You put the text John in the textbox, and push the button.
A message pops up, saying Hello, John!
The code behind the button might look like this:
dim MyStr as String
MyStr="Hello, " & Text1
msgbox MyStr

See how you've built the string MyStr by concatenating the value in Text1
with a static string?
That's the sort of thing your code is doing - it's mostly static text
"SELECT ...", but it needs to include a value with is stored dynamically.
The value to be added is stored in Import_Query![Col_link] , which
should be defined earlier in the code. (My best guess is that Import_Query
refers to a recordset which is defined earlier.)

As for the triple quotations, let's go back to the simple example.
Suppose, instead of
Hello, John
you wanted the message to say
Hello, "John"

If you wrote
MyStr="Hello, "" & Text1 & """
Access could parse out
"Hello, "
as the first static string, and throw an error because it didn't know what
to do with the next ".
To avoid that, there's a convention that if you put "" together, that gets
treated as a single " within the string.
So actually, if you used the syntax above, what you'd get in your messagebox
would be
Hello, " & Text1 "
No matter what you put in Text1.
The reason being that when Access sees "", it doesn't think it's at the end
of a string, the way it does when it sees a single ". So the third " tells
it that this is the end of the string.

Don't know if this is any clearer than the previous post, but at least it
puts things a bit differently.

Hi there,

I am trying to understand a section of VB code used to update our database
and the following line is confusing me:

Coop_SQL = "SELECT * FROM COOP_Purchase_Orders_Table WHERE [COL_Link]

="""

&
Import_Query![Col_link] & """ ORDER BY [Firm_Date]"

I can't understand why there are 3 quotation marks or even what it is trying
to do exactly.

I understand that it is trying to run an SQL select query on the
Purchase_Orders_Table and that it is ordering by Firm_Date but aside from
that I'm lost (i.e what is the Import_Query bit all about?)

Any help would be greatly appreciated.

Thankyou,

John


P.S. I will enclose the full Function below to give an idea of context

Function Update_Details_With_COOP_Data()
Dim MyDB As Database, Import_Query As Recordset, MyWrk As Workspace
Dim strSQL As String, Coop_SQL As String, COOP_Data As Recordset
Set MyWrk = DBEngine.Workspaces(0)
Set MyDB = CurrentDb
strSQL = "SELECT * FROM Details_Table"
Set Import_Query = MyDB.OpenRecordset(strSQL)
With Import_Query
.MoveFirst
Do Until .EOF
Coop_SQL = "SELECT * FROM COOP_Purchase_Orders_Table WHERE
[COL_Link] =""" & Import_Query![Col_link] & """ ORDER BY [Firm_Date]"
Set COOP_Data = MyDB.OpenRecordset(Coop_SQL)
If COOP_Data.RecordCount > 0 Then
.Edit
![AQUISITION] = COOP_Data![AQUISITION]
If COOP_Data![Firm_Date] <> "" Then ![Firm_Date] =
COOP_Data![Firm_Date]
![PO_Number] = COOP_Data![PO_Number]
![System] = COOP_Data![System]
![MMS_Link] = COOP_Data![MMS_Link]
.Update
End If

.MoveNext
Loop
End With
DoEvents
End Function