combine fields in sentence

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 5 fields all with answers "Y" or "N". the fields are swelling,
locking, grinding, givingway, and clicking.
I want to combine only the fields with a "Y" answer into a grammatically
correct sentence in my report so it should read something like:
Mr. Smith complained of locking, grinding, and giving way.

The problem i am having is
1. placing the "and" in the correct spot in the sentence
2. not having a comma at the end of the sentence if "clicking" is not my
last word

how do i fix this? is there a public function i can use, like a loop?
all help is greatly appreciated.

thank you
russ
 
Russ said:
I have 5 fields all with answers "Y" or "N". the fields are swelling,
locking, grinding, givingway, and clicking.
I want to combine only the fields with a "Y" answer into a grammatically
correct sentence in my report so it should read something like:
Mr. Smith complained of locking, grinding, and giving way.

The problem i am having is
1. placing the "and" in the correct spot in the sentence
2. not having a comma at the end of the sentence if "clicking" is not my
last word

how do i fix this? is there a public function i can use, like a loop?
all help is greatly appreciated.
Click to expand...


First construct a comma separated string:
s0 = s0 & IIf(locking = "Y", ", locking", "")
s0 = s0 & IIf(grinding= "Y", ", grinding", "")
. . .

Once you have a string that looks like ", AA, BB, CC, DD"
you can use a short sequence like this:

s1 = Mid(s0, 3) 'get rid of first comma
p = InStrRev(s1, ",") 'find last comma
If p = 0 Then
s2 = s1 'only one item (no comma)
Else
'replace last comma with " and"
s2 = Left(s1, p -1) & " and" & Mid(s1, p+1)
End If
 
Your field names raise a red flag that your table is not normalized. Rather
than creating 5 y/n fields, it might be better to create a related table of
Services. If you can't, consider normalizing with a union query:

Select JobID, "swelling" as Service
From tblJobs
Where Swelling = True
UNION ALL
SELECT JobID, "locking"
FROM tblJobs
WHERE Locking = True
UNION ALL
SELECT JobID, "grinding"
FROM tblJobs
WHERE Grinding = True
UNION ALL
SELECT JobID, "givingway"
FROM tblJobs
WHERE givingway= True
UNION ALL
SELECT JobID, "clicking"
FROM tblJobs
WHERE clicking= True;

Then use the generic concatenate function from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane to join
the services (or whatever) together with commas.
 
thank you but i am having a little trouble.
the public function i wrote is:
Public Function ListComplaints() As String
'First construct a comma separated string:
Dim complaintlist As String
Dim complaintlist1 As String
Dim complaintlist2 As String
Set rst = CurrentDb.OpenRecordset("Select Swelling, Locking,
GrindingStiffness, GivingWay, ClickingPopping FROM Scope")
With rst
complaintlist = complaintlist & IIf(Locking = "Y", ", locking", "")
complaintlist = complaintlist & IIf(GrindingStiffness = "Y", ", grinding
and stiffness", "")
complaintlist = complaintlist & IIf(GivingWay = "Y", ", giving way", "")
complaintlist = complaintlist & IIf(ClickingPopping = "Y", ", clicking
and popping", "")
End With
complaintlist = complaintlist
'Once you have a string that looks like ", AA, BB, CC, DD"
'you can use a short sequence like this:

complaintlist1 = Mid(complaintlist, 3) 'get rid of first comma
p = InStrRev(complaintlist1, ",") 'find last comma
If p = 0 Then
complaintlist2 = complaintlist1 'only one item (no comma)
Else 'replace last comma with " and"
complaintlist2 = Left(complaintlist1, p - 1) & " and" &
Mid(complaintlist1, p + 1)
End If
ListComplaints = complaintlist2
End Function

this gives me the error: Too few parameters. Expected 5.

if i leave the recordset part out of the function i just get a blank field.
where am i going wrong?
thank you,
russ
 
Russ said:
thank you but i am having a little trouble.
the public function i wrote is:
Public Function ListComplaints() As String
'First construct a comma separated string:
Dim complaintlist As String
Dim complaintlist1 As String
Dim complaintlist2 As String
Set rst = CurrentDb.OpenRecordset("Select Swelling, Locking,
GrindingStiffness, GivingWay, ClickingPopping FROM Scope")
With rst
complaintlist = complaintlist & IIf(Locking = "Y", ", locking", "")
complaintlist = complaintlist & IIf(GrindingStiffness = "Y", ", grinding
and stiffness", "")
complaintlist = complaintlist & IIf(GivingWay = "Y", ", giving way", "")
complaintlist = complaintlist & IIf(ClickingPopping = "Y", ", clicking
and popping", "")
End With
complaintlist = complaintlist
'Once you have a string that looks like ", AA, BB, CC, DD"
'you can use a short sequence like this:

complaintlist1 = Mid(complaintlist, 3) 'get rid of first comma
p = InStrRev(complaintlist1, ",") 'find last comma
If p = 0 Then
complaintlist2 = complaintlist1 'only one item (no comma)
Else 'replace last comma with " and"
complaintlist2 = Left(complaintlist1, p - 1) & " and" &
Mid(complaintlist1, p + 1)
End If
ListComplaints = complaintlist2
End Function

this gives me the error: Too few parameters. Expected 5.

if i leave the recordset part out of the function i just get a blank field.
where am i going wrong?
Click to expand...


That messages indicates that the table Scope does not have
fields with the names you used in the Select clause.

I noticed that you did not check for Swelling and that none
of the field names in your If statements was preceeded by a
! to access the recordset fields (if they existed).

The missing !s should have generated several error nessages
about undefined names in your code. The fact that you
didn't see any of these messages implies that you do not
have an OPTION EXPLICIT statement at the top of the module.
It is a very good idea to include that statement in every
module so that mispelled or misused names are flaged
immediately. You can set the Require Variable Declaration
option in VBA Tools - Options to have that statement
automatically included in every new module, but you really
should enter it in all existing modules.

Have you given serious consideration to Duane's suggestion?
 
Back
Top