Building an SQL query string to include "&" as part of string

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I am trying to build an SQL query string which
includes "&" as part of the string. The "&" is used as
part of an expression in the query. It keeps erroring out
at the "CurrentDb.QueryDefs" statement.

As it appears in query:

Level 2 Owner Chain: IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) Or [Ownership Table - All
Relationships]![Entity ID]="",Null,[Ownership Structure
Chains B-UP - TEMP]![Level 1 Owner Chain] & "." &
[Ownership Table - All Relationships]![Entity Owned Count])


The SQL query string build:

ConcatPeriodStr = "' & ""."" & '"

IIf(IsNull([Ownership Table - All Relationships]![Entity
ID]) " _
& "Or [Ownership Table - All Relationships]![Entity ID]
= """" ,Null," _
& "[Ownership Structure Chains B-UP - TEMP]![Level " &
CurrentLevel - 1 & " Owner Chain]" & ConcatPeriodStr
& "[Ownership Table - All Relationships]![Entity Owned
Count]) " _
& "AS [Level " & CurrentLevel & " Owner Chain]


ConcatPeriodStr then equals: "' & "." & '"

Which returns: ' & "." & ' to the query expression, I
need it to appear as: & "." &

I don't want the single quotes.

No matter how I change "ConcatPeriodStr", I can not get
& "." & returned to the query expression.


HELP
 
The suggest strings below do not return the correct
strings in the query expression: The first one
returns: '.' and the second returns: "."

Below is the full subroutine:

Private Sub Build_Bottom_Up_Ownership_Chains_Test()

Dim DB_Ownership As Database
Dim Table_Own_Relation, Table_Struct_Chains_TEMP As
DAO.Recordset
Dim QueryName, strSQLPS_Q_FieldNameXRef,
strWHEREPS_Q_FieldNameXRef, SearchField As String
Dim strQuery, strINSERT, strSELECT, strWHERE,
strFROM, strORDERBY As String
Dim ZeroLenStr, ConcatPeriodStr, CurrentLevel,
LevelNo, LevelNoLess_1 As String


On Error GoTo Err_Build_Bottom_Up_Ownership_Chains_Click

Set DB_Ownership = CurrentDb()
Set Table_Own_Relation = DB_Ownership.OpenRecordset
("Ownership Table - All Relationships", dbOpenTable)

Set Table_Struct_Chains_TEMP =
DB_Ownership.OpenRecordset("Ownership Structure Chains B-
UP - TEMP", dbOpenTable)

CurrentLevel = 2

If CurrentLevel < 10 Then LevelNo = "0" & Trim(Str
(CurrentLevel)) _
Else LevelNo = Trim(Str(CurrentLevel))

If (CurrentLevel - 1) < 10 Then LevelNoLess_1 = "0" &
Trim(Str(CurrentLevel - 1)) _
Else LevelNoLess_1 = Trim(Str(CurrentLevel - 1))

ConcatPeriodStr = " & ""."" & "

strSELECT = "SELECT [Ownership Structure Chains B-UP -
TEMP].*, [Ownership Table - All Relationships].[Direct
Owner ID] AS [Level " & LevelNo & " ID], " _
& "[Ownership Table - All Relationships].
[Direct Owner Name] AS [Level " & LevelNo & " Name], " _
& "[Ownership Table - All
Relationships].EffOwnPct AS [Level " & LevelNo & " %],
0.000000001 AS [Level " & LevelNo & " - Up %], " _
& "0.000000001 AS [Level " & LevelNo & " - Down
%], IIf(IsNull([Ownership Table - All Relationships]!
[Entity ID]) " _
& "Or [Ownership Table - All Relationships]!
[Entity ID]= """" ,Null," _
& "[Ownership Structure Chains B-UP - TEMP]!
[Level " & LevelNoLess_1 & " Owner Chain]" &
ConcatPeriodStr & "[Ownership Table - All Relationships]!
[Entity Owned Count]) " _
& "AS [Level " & LevelNo & " Owner Chain] INTO
[Ownership Structure Chains B-UP]"

strFROM = "FROM [Ownership Structure Chains B-UP -
TEMP] LEFT JOIN [Ownership Table - All Relationships]
ON " _
& "[Ownership Structure Chains B-UP - TEMP].
[Level " & LevelNoLess_1 & " ID] = [Ownership Table - All
Relationships].[Entity ID]"

strWHERE = "WHERE ((([Ownership Table - All
Relationships].[Entity ID]) <> ""zz_NON-AFF"" And
([Ownership Table - All Relationships].[Entity ID])
<> ""zz_AFF_Quest"" " _
& "And ([Ownership Table - All Relationships].
[Entity ID]) <> "".LACPOP"" And ([Ownership Table - All
Relationships].[Entity ID]) <> ""EOUTRS0459""))" _
& "OR ((([Ownership Table - All Relationships].
[Entity ID]) Is Null))"

strORDERBY = "ORDER BY [Ownership Structure Chains B-
UP - TEMP].[Entity ID], IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) " _
& "Or [Ownership Table - All Relationships]!
[Entity ID]="""",Null," _
& "[Ownership Structure Chains B-UP - TEMP]!
[Level " & LevelNoLess_1 & " Owner Chain]" & """."""
& "[Ownership Table - All Relationships]![Entity Owned
Count])"


CurrentDb.QueryDefs("Build Own Structure BUP - Levels
GT 01").sql = strSELECT & strFROM _
& strWHERE & strORDERBY & ";"

DoCmd.SetWarnings False
QueryName = "Build Own Structure BUP - Levels GT 01"
DoCmd.OpenQuery QueryName, acNormal, acReadOnly

Table_Own_Relation.Close
Table_Struct_Chains_TEMP.Close


Exit_Build_Bottom_Up_Ownership_Chains_Click:
DoCmd.SetWarnings True
Exit Sub

Err_Build_Bottom_Up_Ownership_Chains_Click:
MsgBox Err.Description
Resume Exit_Build_Bottom_Up_Ownership_Chains_Click

End Sub
-----Original Message-----
Try
" & '.' & "
Or
" & ""."" & "

I just tried the following and it appeared to do what
Click to expand...
you want if I used either of the
above as the value of the constant ConcatPeriodStr.

Function Test3() As String
Const CurrentLevel = 222
Const ConcatPeriodStr = " & ""."" & "
Test3 = "IIf(IsNull([Ownership Table - All
Click to expand...
Relationships]![Entity ID]) " _
& "Or [Ownership Table - All Relationships]![Entity ID] = """" ,Null," _
& "[Ownership Structure Chains B-UP - TEMP]![Level " &
Click to expand...
CurrentLevel - 1 & " Owner Chain]"
& ConcatPeriodStr & "[Ownership Table - All
Click to expand...
Relationships]![Entity Owned Count]) " _
& "AS [Level " & CurrentLevel & " Owner Chain]"
End Function

--
Wayne Morgan
Microsoft Access MVP


I am trying to build an SQL query string which
includes "&" as part of the string. The "&" is used as
part of an expression in the query. It keeps erroring out
at the "CurrentDb.QueryDefs" statement.

As it appears in query:

Level 2 Owner Chain: IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) Or [Ownership Table - All
Relationships]![Entity ID]="",Null,[Ownership Structure
Chains B-UP - TEMP]![Level 1 Owner Chain] & "." &
[Ownership Table - All Relationships]![Entity Owned Count])


The SQL query string build:

ConcatPeriodStr = "' & ""."" & '"

IIf(IsNull([Ownership Table - All Relationships]! [Entity
ID]) " _
& "Or [Ownership Table - All Relationships]![Entity ID]
= """" ,Null," _
& "[Ownership Structure Chains B-UP - TEMP]![Level " &
CurrentLevel - 1 & " Owner Chain]" & ConcatPeriodStr
& "[Ownership Table - All Relationships]![Entity Owned
Count]) " _
& "AS [Level " & CurrentLevel & " Owner Chain]


ConcatPeriodStr then equals: "' & "." & '"

Which returns: ' & "." & ' to the query expression, I
need it to appear as: & "." &

I don't want the single quotes.

No matter how I change "ConcatPeriodStr", I can not get
& "." & returned to the query expression.


HELP
Click to expand...


.
Click to expand...
 
Ok, I guess I don't understand what you are wanting. I thought you wanted it
to look like your example:

As it appears in query:

Level 2 Owner Chain: IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) Or [Ownership Table - All
Relationships]![Entity ID]="",Null,[Ownership Structure
Chains B-UP - TEMP]![Level 1 Owner Chain] & "." &
[Ownership Table - All Relationships]![Entity Owned Count])

which has & "." &

--
Wayne Morgan
Microsoft Access MVP


Paul said:
The suggest strings below do not return the correct
strings in the query expression: The first one
returns: '.' and the second returns: "."

Below is the full subroutine:

Private Sub Build_Bottom_Up_Ownership_Chains_Test()

Dim DB_Ownership As Database
Dim Table_Own_Relation, Table_Struct_Chains_TEMP As
DAO.Recordset
Dim QueryName, strSQLPS_Q_FieldNameXRef,
strWHEREPS_Q_FieldNameXRef, SearchField As String
Dim strQuery, strINSERT, strSELECT, strWHERE,
strFROM, strORDERBY As String
Dim ZeroLenStr, ConcatPeriodStr, CurrentLevel,
LevelNo, LevelNoLess_1 As String


On Error GoTo Err_Build_Bottom_Up_Ownership_Chains_Click

Set DB_Ownership = CurrentDb()
Set Table_Own_Relation = DB_Ownership.OpenRecordset
("Ownership Table - All Relationships", dbOpenTable)

Set Table_Struct_Chains_TEMP =
DB_Ownership.OpenRecordset("Ownership Structure Chains B-
UP - TEMP", dbOpenTable)

CurrentLevel = 2

If CurrentLevel < 10 Then LevelNo = "0" & Trim(Str
(CurrentLevel)) _
Else LevelNo = Trim(Str(CurrentLevel))

If (CurrentLevel - 1) < 10 Then LevelNoLess_1 = "0" &
Trim(Str(CurrentLevel - 1)) _
Else LevelNoLess_1 = Trim(Str(CurrentLevel - 1))

ConcatPeriodStr = " & ""."" & "

strSELECT = "SELECT [Ownership Structure Chains B-UP -
TEMP].*, [Ownership Table - All Relationships].[Direct
Owner ID] AS [Level " & LevelNo & " ID], " _
& "[Ownership Table - All Relationships].
[Direct Owner Name] AS [Level " & LevelNo & " Name], " _
& "[Ownership Table - All
Relationships].EffOwnPct AS [Level " & LevelNo & " %],
0.000000001 AS [Level " & LevelNo & " - Up %], " _
& "0.000000001 AS [Level " & LevelNo & " - Down
%], IIf(IsNull([Ownership Table - All Relationships]!
[Entity ID]) " _
& "Or [Ownership Table - All Relationships]!
[Entity ID]= """" ,Null," _
& "[Ownership Structure Chains B-UP - TEMP]!
[Level " & LevelNoLess_1 & " Owner Chain]" &
ConcatPeriodStr & "[Ownership Table - All Relationships]!
[Entity Owned Count]) " _
& "AS [Level " & LevelNo & " Owner Chain] INTO
[Ownership Structure Chains B-UP]"

strFROM = "FROM [Ownership Structure Chains B-UP -
TEMP] LEFT JOIN [Ownership Table - All Relationships]
ON " _
& "[Ownership Structure Chains B-UP - TEMP].
[Level " & LevelNoLess_1 & " ID] = [Ownership Table - All
Relationships].[Entity ID]"

strWHERE = "WHERE ((([Ownership Table - All
Relationships].[Entity ID]) <> ""zz_NON-AFF"" And
([Ownership Table - All Relationships].[Entity ID])
<> ""zz_AFF_Quest"" " _
& "And ([Ownership Table - All Relationships].
[Entity ID]) <> "".LACPOP"" And ([Ownership Table - All
Relationships].[Entity ID]) <> ""EOUTRS0459""))" _
& "OR ((([Ownership Table - All Relationships].
[Entity ID]) Is Null))"

strORDERBY = "ORDER BY [Ownership Structure Chains B-
UP - TEMP].[Entity ID], IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) " _
& "Or [Ownership Table - All Relationships]!
[Entity ID]="""",Null," _
& "[Ownership Structure Chains B-UP - TEMP]!
[Level " & LevelNoLess_1 & " Owner Chain]" & """."""
& "[Ownership Table - All Relationships]![Entity Owned
Count])"


CurrentDb.QueryDefs("Build Own Structure BUP - Levels
GT 01").sql = strSELECT & strFROM _
& strWHERE & strORDERBY & ";"

DoCmd.SetWarnings False
QueryName = "Build Own Structure BUP - Levels GT 01"
DoCmd.OpenQuery QueryName, acNormal, acReadOnly

Table_Own_Relation.Close
Table_Struct_Chains_TEMP.Close


Exit_Build_Bottom_Up_Ownership_Chains_Click:
DoCmd.SetWarnings True
Exit Sub

Err_Build_Bottom_Up_Ownership_Chains_Click:
MsgBox Err.Description
Resume Exit_Build_Bottom_Up_Ownership_Chains_Click

End Sub
-----Original Message-----
Try
" & '.' & "
Or
" & ""."" & "

I just tried the following and it appeared to do what
Click to expand...
you want if I used either of the
above as the value of the constant ConcatPeriodStr.

Function Test3() As String
Const CurrentLevel = 222
Const ConcatPeriodStr = " & ""."" & "
Test3 = "IIf(IsNull([Ownership Table - All
Click to expand...
Relationships]![Entity ID]) " _
& "Or [Ownership Table - All Relationships]![Entity ID] = """" ,Null," _
& "[Ownership Structure Chains B-UP - TEMP]![Level " &
Click to expand...
CurrentLevel - 1 & " Owner Chain]"
& ConcatPeriodStr & "[Ownership Table - All
Click to expand...
Relationships]![Entity Owned Count]) " _
& "AS [Level " & CurrentLevel & " Owner Chain]"
End Function

--
Wayne Morgan
Microsoft Access MVP


I am trying to build an SQL query string which
includes "&" as part of the string. The "&" is used as
part of an expression in the query. It keeps erroring out
at the "CurrentDb.QueryDefs" statement.

As it appears in query:

Level 2 Owner Chain: IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) Or [Ownership Table - All
Relationships]![Entity ID]="",Null,[Ownership Structure
Chains B-UP - TEMP]![Level 1 Owner Chain] & "." &
[Ownership Table - All Relationships]![Entity Owned Count])


The SQL query string build:

ConcatPeriodStr = "' & ""."" & '"

IIf(IsNull([Ownership Table - All Relationships]! [Entity
ID]) " _
& "Or [Ownership Table - All Relationships]![Entity ID]
= """" ,Null," _
& "[Ownership Structure Chains B-UP - TEMP]![Level " &
CurrentLevel - 1 & " Owner Chain]" & ConcatPeriodStr
& "[Ownership Table - All Relationships]![Entity Owned
Count]) " _
& "AS [Level " & CurrentLevel & " Owner Chain]


ConcatPeriodStr then equals: "' & "." & '"

Which returns: ' & "." & ' to the query expression, I
need it to appear as: & "." &

I don't want the single quotes.

No matter how I change "ConcatPeriodStr", I can not get
& "." & returned to the query expression.


HELP
Click to expand...


.
Click to expand...
Click to expand...
 
Yes, I do want it to look like the example, but it needs
to look like this in the query that is created with
the "QueryDefs" statement (see just below).

CurrentDb.QueryDefs("Build Own Structure BUP - Levels
GT 01").sql = strSELECT & strFROM _
& strWHERE & strORDERBY & ";"

When this line of code is executed it errors out and shows
the errored string expression. Thats how I know what is
being returned. This is how it looks in the error message:
Level 2 Owner Chain: IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) Or [Ownership Table - All
Relationships]![Entity ID]="",Null,[Ownership Structure
Chains B-UP - TEMP]![Level 1 Owner Chain]"."[Ownership
Click to expand...
Table - All Relationships]![Entity Owned Count])

When using your suggestion:
" & ""."" & "
Click to expand...

I would agree, that when I place a watch
on "ConcatPeriodStr" it looks correct, but it does not
appear correctly when the "QueryDefs" statement is
executed.

CurrentDb.QueryDefs("Build Own Structure BUP - Levels
GT 01").sql = strSELECT & strFROM _
& strWHERE & strORDERBY & ";"
-----Original Message-----
Ok, I guess I don't understand what you are wanting. I thought you wanted it
to look like your example:

As it appears in query:

Level 2 Owner Chain: IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) Or [Ownership Table - All
Relationships]![Entity ID]="",Null,[Ownership Structure
Chains B-UP - TEMP]![Level 1 Owner Chain] & "." &
[Ownership Table - All Relationships]![Entity Owned Count])

which has & "." &

--
Wayne Morgan
Microsoft Access MVP


Paul said:
The suggest strings below do not return the correct
strings in the query expression: The first one
returns: '.' and the second returns: "."

Below is the full subroutine:

Private Sub Build_Bottom_Up_Ownership_Chains_Test()

Dim DB_Ownership As Database
Dim Table_Own_Relation, Table_Struct_Chains_TEMP As
DAO.Recordset
Dim QueryName, strSQLPS_Q_FieldNameXRef,
strWHEREPS_Q_FieldNameXRef, SearchField As String
Dim strQuery, strINSERT, strSELECT, strWHERE,
strFROM, strORDERBY As String
Dim ZeroLenStr, ConcatPeriodStr, CurrentLevel,
LevelNo, LevelNoLess_1 As String


On Error GoTo Err_Build_Bottom_Up_Ownership_Chains_Click

Set DB_Ownership = CurrentDb()
Set Table_Own_Relation = DB_Ownership.OpenRecordset
("Ownership Table - All Relationships", dbOpenTable)

Set Table_Struct_Chains_TEMP =
DB_Ownership.OpenRecordset("Ownership Structure Chains B-
UP - TEMP", dbOpenTable)

CurrentLevel = 2

If CurrentLevel < 10 Then LevelNo = "0" & Trim(Str
(CurrentLevel)) _
Else LevelNo = Trim(Str(CurrentLevel))

If (CurrentLevel - 1) < 10 Then LevelNoLess_1 = "0" &
Trim(Str(CurrentLevel - 1)) _
Else LevelNoLess_1 = Trim(Str(CurrentLevel - 1))

ConcatPeriodStr = " & ""."" & "

strSELECT = "SELECT [Ownership Structure Chains B- UP -
TEMP].*, [Ownership Table - All Relationships].[Direct
Owner ID] AS [Level " & LevelNo & " ID], " _
& "[Ownership Table - All Relationships].
[Direct Owner Name] AS [Level " & LevelNo & " Name], " _
& "[Ownership Table - All
Relationships].EffOwnPct AS [Level " & LevelNo & " %],
0.000000001 AS [Level " & LevelNo & " - Up %], " _
& "0.000000001 AS [Level " & LevelNo & " - Down
%], IIf(IsNull([Ownership Table - All Relationships]!
[Entity ID]) " _
& "Or [Ownership Table - All Relationships]!
[Entity ID]= """" ,Null," _
& "[Ownership Structure Chains B-UP - TEMP]!
[Level " & LevelNoLess_1 & " Owner Chain]" &
ConcatPeriodStr & "[Ownership Table - All Relationships]!
[Entity Owned Count]) " _
& "AS [Level " & LevelNo & " Owner Chain] INTO
[Ownership Structure Chains B-UP]"

strFROM = "FROM [Ownership Structure Chains B-UP -
TEMP] LEFT JOIN [Ownership Table - All Relationships]
ON " _
& "[Ownership Structure Chains B-UP - TEMP].
[Level " & LevelNoLess_1 & " ID] = [Ownership Table - All
Relationships].[Entity ID]"

strWHERE = "WHERE ((([Ownership Table - All
Relationships].[Entity ID]) <> ""zz_NON-AFF"" And
([Ownership Table - All Relationships].[Entity ID])
<> ""zz_AFF_Quest"" " _
& "And ([Ownership Table - All Relationships].
[Entity ID]) <> "".LACPOP"" And ([Ownership Table - All
Relationships].[Entity ID]) <> ""EOUTRS0459""))" _
& "OR ((([Ownership Table - All Relationships].
[Entity ID]) Is Null))"

strORDERBY = "ORDER BY [Ownership Structure Chains B-
UP - TEMP].[Entity ID], IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) " _
& "Or [Ownership Table - All Relationships]!
[Entity ID]="""",Null," _
& "[Ownership Structure Chains B-UP - TEMP]!
[Level " & LevelNoLess_1 & " Owner Chain]" & """."""
& "[Ownership Table - All Relationships]![Entity Owned
Count])"


CurrentDb.QueryDefs("Build Own Structure BUP - Levels
GT 01").sql = strSELECT & strFROM _
& strWHERE & strORDERBY & ";"

DoCmd.SetWarnings False
QueryName = "Build Own Structure BUP - Levels GT 01"
DoCmd.OpenQuery QueryName, acNormal, acReadOnly

Table_Own_Relation.Close
Table_Struct_Chains_TEMP.Close


Exit_Build_Bottom_Up_Ownership_Chains_Click:
DoCmd.SetWarnings True
Exit Sub

Err_Build_Bottom_Up_Ownership_Chains_Click:
MsgBox Err.Description
Resume Exit_Build_Bottom_Up_Ownership_Chains_Click

End Sub
-----Original Message-----
Try
" & '.' & "
Or
" & ""."" & "

I just tried the following and it appeared to do what
Click to expand...
you want if I used either of the
above as the value of the constant ConcatPeriodStr.

Function Test3() As String
Const CurrentLevel = 222
Const ConcatPeriodStr = " & ""."" & "
Test3 = "IIf(IsNull([Ownership Table - All
Click to expand...
Relationships]![Entity ID]) " _
& "Or [Ownership Table - All Relationships]![Entity ID] = """" ,Null," _
& "[Ownership Structure Chains B-UP - TEMP]![Level " &
Click to expand...
CurrentLevel - 1 & " Owner Chain]"
& ConcatPeriodStr & "[Ownership Table - All
Click to expand...
Relationships]![Entity Owned Count]) " _
& "AS [Level " & CurrentLevel & " Owner Chain]"
End Function

--
Wayne Morgan
Microsoft Access MVP


I am trying to build an SQL query string which
includes "&" as part of the string. The "&" is used as
part of an expression in the query. It keeps erroring out
at the "CurrentDb.QueryDefs" statement.

As it appears in query:

Level 2 Owner Chain: IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) Or [Ownership Table - All
Relationships]![Entity ID]="",Null,[Ownership Structure
Chains B-UP - TEMP]![Level 1 Owner Chain] & "." &
[Ownership Table - All Relationships]![Entity Owned Count])


The SQL query string build:

ConcatPeriodStr = "' & ""."" & '"

IIf(IsNull([Ownership Table - All Relationships]! [Entity
ID]) " _
& "Or [Ownership Table - All Relationships]![Entity ID]
= """" ,Null," _
& "[Ownership Structure Chains B-UP - TEMP]![Level " &
CurrentLevel - 1 & " Owner Chain]" & ConcatPeriodStr
& "[Ownership Table - All Relationships]![Entity Owned
Count]) " _
& "AS [Level " & CurrentLevel & " Owner Chain]


ConcatPeriodStr then equals: "' & "." & '"

Which returns: ' & "." & ' to the query expression, I
need it to appear as: & "." &

I don't want the single quotes.

No matter how I change "ConcatPeriodStr", I can not get
& "." & returned to the query expression.


HELP


.
Click to expand...
Click to expand...


.
Click to expand...
 
I used the following Sub to test this. What I found, for the most part, were a bunch of
missing "space" characters (i.e. table named "Ownership Table - All Relationships" was
"Ownership Table - AllRelationships").

When it comes to the period, I don't think you need to concatenate it in the query, just
pass the period. You will notice that I changed the variable to

ConcatPeriodStr = "."

I received no errors from this:

Public Sub TestQuery()
Dim strSELECT As String, strFROM As String, strWHERE As String, strORDERBY As String
Const LevelNo = 10, LevelNoLess_1 = 10, ConcatPeriodStr = "."


strSELECT = "SELECT [Ownership Structure Chains B-UP - TEMP].*, [Ownership Table - All
Relationships].[DirectOwner ID] AS [Level " & LevelNo & " ID], " _
& "[Ownership Table - All Relationships].[Direct Owner Name] AS [Level " &
LevelNo & " Name], " _
& "[Ownership Table - All Relationships].EffOwnPct AS [Level " & LevelNo & "
%],0.000000001 AS [Level " & LevelNo & " - Up %], " _
& "0.000000001 AS [Level " & LevelNo & " - Down%], IIf(IsNull([Ownership Table -
All Relationships]![Entity ID]) " _
& "Or [Ownership Table - All Relationships]![Entity ID]= """" ,Null," _
& "[Ownership Structure Chains B-UP - TEMP]![Level " & LevelNoLess_1 & " Owner
Chain] " & ConcatPeriodStr & "[Ownership Table - All Relationships]![Entity Owned Count])
" _
& "AS [Level " & LevelNo & " Owner Chain] INTO [Ownership Structure Chains B-UP]
"

strFROM = "FROM [Ownership Structure Chains B-UP - TEMP] LEFT JOIN [Ownership Table - All
Relationships] ON " _
& "[Ownership Structure Chains B-UP - TEMP].[Level " & LevelNoLess_1 & " ID] =
[Ownership Table - All Relationships].[Entity ID]"

strWHERE = " WHERE ((([Ownership Table - All Relationships].[Entity ID]) <> ""zz_NON-AFF""
And ([Ownership Table - All Relationships].[Entity ID])<> ""zz_AFF_Quest"" " _
& "And ([Ownership Table - All Relationships].[Entity ID]) <> "".LACPOP"" And
([Ownership Table - All Relationships].[Entity ID]) <> ""EOUTRS0459""))" _
& " OR ((([Ownership Table - All Relationships].[Entity ID]) Is Null))"

strORDERBY = " ORDER BY [Ownership Structure Chains B-UP - TEMP].[Entity ID],
IIf(IsNull([Ownership Table - All Relationships]![Entity ID]) " _
& "Or [Ownership Table - All Relationships]![Entity ID]="""",Null," _
& "[Ownership Structure Chains B-UP - TEMP]![Level " & LevelNoLess_1 & " Owner
Chain]" & ConcatPeriodStr & "[Ownership Table - All Relationships]![Entity Owned Count])"

Debug.Print strSELECT & strFROM & strWHERE & strORDERBY & ";"

CurrentDb.QueryDefs("Build Own Structure BUP - LevelsGT 01").SQL = strSELECT & strFROM _
& strWHERE & strORDERBY & ";"

End Sub


--
Wayne Morgan
Microsoft Access MVP


Paul said:
Yes, I do want it to look like the example, but it needs
to look like this in the query that is created with
the "QueryDefs" statement (see just below).

CurrentDb.QueryDefs("Build Own Structure BUP - Levels
GT 01").sql = strSELECT & strFROM _
& strWHERE & strORDERBY & ";"

When this line of code is executed it errors out and shows
the errored string expression. Thats how I know what is
being returned. This is how it looks in the error message:
Level 2 Owner Chain: IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) Or [Ownership Table - All
Relationships]![Entity ID]="",Null,[Ownership Structure
Chains B-UP - TEMP]![Level 1 Owner Chain]"."[Ownership
Click to expand...
Table - All Relationships]![Entity Owned Count])

When using your suggestion:
" & ""."" & "
Click to expand...

I would agree, that when I place a watch
on "ConcatPeriodStr" it looks correct, but it does not
appear correctly when the "QueryDefs" statement is
executed.

CurrentDb.QueryDefs("Build Own Structure BUP - Levels
GT 01").sql = strSELECT & strFROM _
& strWHERE & strORDERBY & ";"
-----Original Message-----
Ok, I guess I don't understand what you are wanting. I thought you wanted it
to look like your example:

As it appears in query:

Level 2 Owner Chain: IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) Or [Ownership Table - All
Relationships]![Entity ID]="",Null,[Ownership Structure
Chains B-UP - TEMP]![Level 1 Owner Chain] & "." &
[Ownership Table - All Relationships]![Entity Owned Count])

which has & "." &

--
Wayne Morgan
Microsoft Access MVP


Paul said:
The suggest strings below do not return the correct
strings in the query expression: The first one
returns: '.' and the second returns: "."

Below is the full subroutine:

Private Sub Build_Bottom_Up_Ownership_Chains_Test()

Dim DB_Ownership As Database
Dim Table_Own_Relation, Table_Struct_Chains_TEMP As
DAO.Recordset
Dim QueryName, strSQLPS_Q_FieldNameXRef,
strWHEREPS_Q_FieldNameXRef, SearchField As String
Dim strQuery, strINSERT, strSELECT, strWHERE,
strFROM, strORDERBY As String
Dim ZeroLenStr, ConcatPeriodStr, CurrentLevel,
LevelNo, LevelNoLess_1 As String


On Error GoTo Err_Build_Bottom_Up_Ownership_Chains_Click

Set DB_Ownership = CurrentDb()
Set Table_Own_Relation = DB_Ownership.OpenRecordset
("Ownership Table - All Relationships", dbOpenTable)

Set Table_Struct_Chains_TEMP =
DB_Ownership.OpenRecordset("Ownership Structure Chains B-
UP - TEMP", dbOpenTable)

CurrentLevel = 2

If CurrentLevel < 10 Then LevelNo = "0" & Trim(Str
(CurrentLevel)) _
Else LevelNo = Trim(Str(CurrentLevel))

If (CurrentLevel - 1) < 10 Then LevelNoLess_1 = "0" &
Trim(Str(CurrentLevel - 1)) _
Else LevelNoLess_1 = Trim(Str(CurrentLevel - 1))

ConcatPeriodStr = " & ""."" & "

strSELECT = "SELECT [Ownership Structure Chains B- UP -
TEMP].*, [Ownership Table - All Relationships].[Direct
Owner ID] AS [Level " & LevelNo & " ID], " _
& "[Ownership Table - All Relationships].
[Direct Owner Name] AS [Level " & LevelNo & " Name], " _
& "[Ownership Table - All
Relationships].EffOwnPct AS [Level " & LevelNo & " %],
0.000000001 AS [Level " & LevelNo & " - Up %], " _
& "0.000000001 AS [Level " & LevelNo & " - Down
%], IIf(IsNull([Ownership Table - All Relationships]!
[Entity ID]) " _
& "Or [Ownership Table - All Relationships]!
[Entity ID]= """" ,Null," _
& "[Ownership Structure Chains B-UP - TEMP]!
[Level " & LevelNoLess_1 & " Owner Chain]" &
ConcatPeriodStr & "[Ownership Table - All Relationships]!
[Entity Owned Count]) " _
& "AS [Level " & LevelNo & " Owner Chain] INTO
[Ownership Structure Chains B-UP]"

strFROM = "FROM [Ownership Structure Chains B-UP -
TEMP] LEFT JOIN [Ownership Table - All Relationships]
ON " _
& "[Ownership Structure Chains B-UP - TEMP].
[Level " & LevelNoLess_1 & " ID] = [Ownership Table - All
Relationships].[Entity ID]"

strWHERE = "WHERE ((([Ownership Table - All
Relationships].[Entity ID]) <> ""zz_NON-AFF"" And
([Ownership Table - All Relationships].[Entity ID])
<> ""zz_AFF_Quest"" " _
& "And ([Ownership Table - All Relationships].
[Entity ID]) <> "".LACPOP"" And ([Ownership Table - All
Relationships].[Entity ID]) <> ""EOUTRS0459""))" _
& "OR ((([Ownership Table - All Relationships].
[Entity ID]) Is Null))"

strORDERBY = "ORDER BY [Ownership Structure Chains B-
UP - TEMP].[Entity ID], IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) " _
& "Or [Ownership Table - All Relationships]!
[Entity ID]="""",Null," _
& "[Ownership Structure Chains B-UP - TEMP]!
[Level " & LevelNoLess_1 & " Owner Chain]" & """."""
& "[Ownership Table - All Relationships]![Entity Owned
Count])"


CurrentDb.QueryDefs("Build Own Structure BUP - Levels
GT 01").sql = strSELECT & strFROM _
& strWHERE & strORDERBY & ";"

DoCmd.SetWarnings False
QueryName = "Build Own Structure BUP - Levels GT 01"
DoCmd.OpenQuery QueryName, acNormal, acReadOnly

Table_Own_Relation.Close
Table_Struct_Chains_TEMP.Close


Exit_Build_Bottom_Up_Ownership_Chains_Click:
DoCmd.SetWarnings True
Exit Sub

Err_Build_Bottom_Up_Ownership_Chains_Click:
MsgBox Err.Description
Resume Exit_Build_Bottom_Up_Ownership_Chains_Click

End Sub

-----Original Message-----
Try
" & '.' & "
Or
" & ""."" & "

I just tried the following and it appeared to do what
you want if I used either of the
above as the value of the constant ConcatPeriodStr.

Function Test3() As String
Const CurrentLevel = 222
Const ConcatPeriodStr = " & ""."" & "
Test3 = "IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) " _
& "Or [Ownership Table - All Relationships]![Entity ID]
= """" ,Null," _
& "[Ownership Structure Chains B-UP - TEMP]![Level " &
CurrentLevel - 1 & " Owner Chain]"
& ConcatPeriodStr & "[Ownership Table - All
Relationships]![Entity Owned Count]) " _
& "AS [Level " & CurrentLevel & " Owner Chain]"
End Function

--
Wayne Morgan
Microsoft Access MVP


I am trying to build an SQL query string which
includes "&" as part of the string. The "&" is used as
part of an expression in the query. It keeps erroring
out
at the "CurrentDb.QueryDefs" statement.

As it appears in query:

Level 2 Owner Chain: IIf(IsNull([Ownership Table - All
Relationships]![Entity ID]) Or [Ownership Table - All
Relationships]![Entity ID]="",Null,[Ownership Structure
Chains B-UP - TEMP]![Level 1 Owner Chain] & "." &
[Ownership Table - All Relationships]![Entity Owned
Count])


The SQL query string build:

ConcatPeriodStr = "' & ""."" & '"

IIf(IsNull([Ownership Table - All Relationships]!
[Entity
ID]) " _
& "Or [Ownership Table - All Relationships]![Entity ID]
= """" ,Null," _
& "[Ownership Structure Chains B-UP - TEMP]![Level " &
CurrentLevel - 1 & " Owner Chain]" & ConcatPeriodStr
& "[Ownership Table - All Relationships]![Entity Owned
Count]) " _
& "AS [Level " & CurrentLevel & " Owner Chain]


ConcatPeriodStr then equals: "' & "." & '"

Which returns: ' & "." & ' to the query
expression, I
need it to appear as: & "." &

I don't want the single quotes.

No matter how I change "ConcatPeriodStr", I can not get
& "." & returned to the query expression.


HELP


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top