Query within a query

[Ray]

I have a column within a query which reads as follows.

Defects(Occurences): Concatenate("SELECT Defect & ' (' & Count & ')' FROM
tblOlga2 WHERE WorkOrdNum =""" & [WorkOrdNum] & """ & ' AND ' & AuditDate
=""" & [AuditDate] & """")

I expect to see a result in this column which looks like the following:

Shrink Wrap (2), Top Spine (3)

All works fine until I add the last section of the code .....

& ' AND ' & AuditDate =""" & [AuditDate] & """")

AuditDate in the table and query are both defined as date/time

I am sure some # belong somewhere but I am unable to figure out exactly where.

Here is the module for the Concatinate function if that helps

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003

Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

 

[Marshall Barton]

I have a column within a query which reads as follows.

Defects(Occurences): Concatenate("SELECT Defect & ' (' & Count & ')' FROM
tblOlga2 WHERE WorkOrdNum =""" & [WorkOrdNum] & """ & ' AND ' & AuditDate
=""" & [AuditDate] & """")

I expect to see a result in this column which looks like the following:

Shrink Wrap (2), Top Spine (3)

All works fine until I add the last section of the code .....

& ' AND ' & AuditDate =""" & [AuditDate] & """")

AuditDate in the table and query are both defined as date/time

Try using something more like:

Concatenate("SELECT Defect & ' (' & Count(*) & ')' FROM
tblOlga2 WHERE WorkOrdNum =""" & [WorkOrdNum] & """ AND
AuditDate = " & Format(AuditDate, "\#yyyy-m-d\#))

 

[Ray]

I get a message about an invalid string. Then the "\#yyyy-m-d\#)) section
is highlighted.

I have a column within a query which reads as follows.

Defects(Occurences): Concatenate("SELECT Defect & ' (' & Count & ')' FROM
tblOlga2 WHERE WorkOrdNum =""" & [WorkOrdNum] & """ & ' AND ' & AuditDate
=""" & [AuditDate] & """")

I expect to see a result in this column which looks like the following:

Shrink Wrap (2), Top Spine (3)

All works fine until I add the last section of the code .....

& ' AND ' & AuditDate =""" & [AuditDate] & """")

AuditDate in the table and query are both defined as date/time

Try using something more like:

Concatenate("SELECT Defect & ' (' & Count(*) & ')' FROM
tblOlga2 WHERE WorkOrdNum =""" & [WorkOrdNum] & """ AND
AuditDate = " & Format(AuditDate, "\#yyyy-m-d\#))

 

[Marshall Barton]

Sorry, that was suposed to be
& Format(AuditDate, "\#yyyy-m-d\#"))
--
Marsh
MVP [MS Access]

I get a message about an invalid string. Then the "\#yyyy-m-d\#)) section
is highlighted.

I have a column within a query which reads as follows.

Defects(Occurences): Concatenate("SELECT Defect & ' (' & Count & ')' FROM
tblOlga2 WHERE WorkOrdNum =""" & [WorkOrdNum] & """ & ' AND ' & AuditDate
=""" & [AuditDate] & """")

I expect to see a result in this column which looks like the following:

Shrink Wrap (2), Top Spine (3)

All works fine until I add the last section of the code .....

& ' AND ' & AuditDate =""" & [AuditDate] & """")

AuditDate in the table and query are both defined as date/time

Try using something more like:

Concatenate("SELECT Defect & ' (' & Count(*) & ')' FROM
tblOlga2 WHERE WorkOrdNum =""" & [WorkOrdNum] & """ AND
AuditDate = " & Format(AuditDate, "\#yyyy-m-d\#))

 

[Ray]

Worked great. Thanks

Sorry, that was suposed to be
& Format(AuditDate, "\#yyyy-m-d\#"))
--
Marsh
MVP [MS Access]

I get a message about an invalid string. Then the "\#yyyy-m-d\#)) section
is highlighted.

Ray wrote:

I have a column within a query which reads as follows.

Defects(Occurences): Concatenate("SELECT Defect & ' (' & Count & ')' FROM
tblOlga2 WHERE WorkOrdNum =""" & [WorkOrdNum] & """ & ' AND ' & AuditDate
=""" & [AuditDate] & """")

I expect to see a result in this column which looks like the following:

Shrink Wrap (2), Top Spine (3)

All works fine until I add the last section of the code .....

& ' AND ' & AuditDate =""" & [AuditDate] & """")

AuditDate in the table and query are both defined as date/time


Try using something more like:

Concatenate("SELECT Defect & ' (' & Count(*) & ')' FROM
tblOlga2 WHERE WorkOrdNum =""" & [WorkOrdNum] & """ AND
AuditDate = " & Format(AuditDate, "\#yyyy-m-d\#))

.