Code Help

[alastair69]

I hope you all have recovered from Christmas and the New Year.

I have the following code and would like a way of copping with null values in a table the code i have was constructed by Raskew.

The code is to list the Rooms in one row like:-
1/055 , Above 1/198 , G/116 , G/134 , G/179

I would also lie the list above to show like this:-
1/055, Above 1/198, G/116, G/134, G/179

The Code that is used is as follows:-
Function StringEm(ptblName As String, pfldName As String, _
Optional xx As String) As String
'**********************************************************
'Purpose: Build a string of field values
' displayed in a record set
'Coded by: raskew
'To test: From Northwind debug window
' ? StringEm("Employees", "LastName", ", ")
'**********************************************************

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strhold As String
Dim n As Integer
Dim i As Integer

xx = IIf(xx = " ", " ", xx)
Set db = CurrentDb
' strSQL = "SELECT distinct " & pfldName & " FROM " & ptblName _
' & " ORDER BY " & pfldName & "; "
strSQL = "SELECT distinct " & pfldName & " FROM " & ptblName & "; "

Set rs = db.OpenRecordset(strSQL)
rs.MoveLast
n = rs.RecordCount
rs.MoveFirst

If n > 0 Then
For i = 1 To n
strhold = strhold & rs(pfldName) & xx
rs.MoveNext
Next i
strhold = Left(strhold, Len(Trim(strhold)) - Len(Trim(xx)))
StringEm = strhold
End If

rs.Close
db.Close
Set db = Nothing

End Function

Thanks Inadvance

Alastair

UPDATE:
*******************************RESOLVED THANKS TO david.brent & BAT17*****************************

 

[Bat17]

Couldn't actualy see the difference between what you had and what you wanted Guessing at
strhold = strhold & NZ(rs(pfldName)," ") & xx

Peter

 

[alastair69]

strhold = strhold & rs(pfldName) & xx

Should i replace this part with your bit of code not should i go some where eles.

Alastair

 

[Bat17]

Yes, replace the line

Peter

 

[alastair69]

I replaced the line of code you asked me to, but the problem is still happening if the table has no records then the code falls over, i would like there to be a standard lable if no records exist of "No Records Fond".

If anybody can help i would be very greaful.

Alastair

 

[Bat17]

Sorry I thought that you were meaning no data in the field rather than no records returned.
just add
StringEm = "No Records To Show"
before
xx = IIf(xx = " ", " ", xx)

HTH

Peter

 

[alastair69]

It is still coming up with error 3021, any other ideas. i know why its doing it the is due to there been no records in the query, just do not know how to sort it out.

Alastair

 

[david.brent]

I would check to see if there is any records first...


Set rs = db.OpenRecordset(strSQL)
If rs.recordcount <> 0 Then
rs.MoveLast
n = rs.RecordCount
rs.MoveFirst
etc

This will work because before you move last the .recordcount value will be 0 for no records and 1 (or the actual number of records) if there are any number of records.

Take care.

 

[Bat17]

try it this way.

Function StringEm(ptblName As String, pfldName As String, _
Optional xx As String = " ") As String
'************************************************* *********
'Purpose: Build a string of field values
' displayed in a record set
'Coded by: raskew
'To test: From Northwind debug window
' ? StringEm("Employees", "LastName", ", ")
'************************************************* *********
Dim rst As Recordset
Dim strSQL As String
Dim strhold As String

strSQL = "SELECT distinct " & pfldName & " FROM " & ptblName & "; "
Set rst = CurrentDb.OpenRecordset(strSQL)
If Not (rst.EOF And rst.BOF) Then
    rst.MoveFirst
    Do While Not rst.EOF
        strhold = strhold & rst(pfldName) & xx
        rst.MoveNext
    Loop
    strhold = Left(strhold, Len(Trim(strhold)) - Len(Trim(xx)))
    StringEm = strhold
Else
    StringEm = "No Records Found"
End If
Set rst = Nothing
End Function

Peter

 

[alastair69]

Thanks you it worked very well.

Alastair