Rpt...Expression typed incorrectly....or too complex

[arpeggione]

reference thread703-12139

I am using some code in a report text box as follows:

=Concatenate("SELECT [PDNNum] FROM [tblTransPCBData] WHERE [Transmission Line Name Counter] = " & Val(Reports!rptTransLineListAll!txtTransLineID))

Note that sometimes [PDNNum] can be a null or empty field.
I am getting that "Expression typed incorrectly....or too complex..." msg. can anyone help me get rid of it? I tried "SELECT Nz[PDNNum]...", but that didn't work either...

thank you,

Arpeggione

The Concatenate function below (borrowed from and created by Duane Hookum):

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, _
'adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rst
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rst = 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

 

[dhookom]

What are the data types of [Transmission Line Name Counter] and txtTransLineID?


Duane
Hook'D on Access
MS Access MVP

 

[arpeggione]

Thank you for your email...by the way, I inherited this dbase (and am working on updating the nomenclature). "Transmission Line Name Counter" is a key field and is an AutoNumber type. "txtTransLineID" is the name of a text box.

thanks again for looking at this...

Arpeggione

 

[dhookom]

I expect the text box is in the report "rptTransLineListAll". Try use a control source of:

=Concatenate("SELECT [PDNNum] FROM [tblTransPCBData] WHERE [Transmission Line Name Counter] = " & Nz(txtTransLineID,0))

Duane
Hook'D on Access
MS Access MVP

 

[arpeggione]

Thank you Duane...this is embarrassing, but my original code works...the error was that in the query I had referred to the val(old name of the text box).

thank you for taking the time to look at this. and thx for posting the Concatenate function. It is just what I need.

Best,

Karen