Trouble getting string function to work correctly

[socomfort]

Hi:

I have a public function that is supposed to concatenate several field values into one string. For example if:

JobNo = 12345
Jobtype = priority, hold
PartNo = 67
Shape = round

Then I want to concatenate as follows:

12345/priority/67/round
12345/hold/67/round

There should be two rows because there are two different Jobtypes for the same JobNo. The code follows:

Public Function GetJobType(strMyString As String) As _ String

'Declare variables
   Dim Db As DAO.Database, rs As DAO.Recordset, sql As _ String

Set db = CurrentDb
sql = "SELECT tblSchedule.JobNo, tblSchedule.JobType, "
sql = sql & "tblSchedule.PartNo, tblSchedule.Shape "
sql = sql & "FROM tblSchedule"

Set rs = db.OpenRecordset(sql, dbOpenDynaset, _
dbSeeChanges)

   rs.MoveFirst
   strMyString = ""
   Do Until rs.EOF
      strMyString = rs.Fields("JobNo") & "/"
      strMyString = strMyString & rs.Fields("JobType")
      strMyString = strMyString & "/" & rs.Fields("PartNo")
      strMyString = strMyString & "/" & rs.Fields(Shape)
      strMyString = strMyString & vbCrLf
   rs.MoveNext
   Loop

GetJobType = Format(strMyString, Text)

db.Close
Set rs = Nothing

End Function

Originally, I could not get the function to build the string. I added:

GetJobType = Format(strMyString, Text)

to the function above just to see what it would do. It works - sort of.

When I add:

, GetJobType([strMyString]) AS JobTypeData, "

to the SQL statement that actually calls the function, the function runs but a parameter box pops up and I have to populate it with text. I know this is not running correctly, and I am not sure why.

Right now, the code returns 1 record only after I enter the ambiguous text in the parameter box. Then it fails to put the second row of data together. Instead, it is repeating the first concatenation.

I appreciate any help you guys can provide.

thanks,

Ben

 

[SkipVought]

Hi,

The string argument does not seem to do ANYTING for this function...

Public Function GetJobType(sJobNO As String) As String[b]
 'assuming that JobNo is CHAR[/b]
    'Declare variables
    Dim Db As DAO.Database, rs As DAO.Recordset, sql As String
    
    Set Db = CurrentDb
    sql = "SELECT JobNo, JobType, "
    sql = sql & "PartNo, Shape "
    sql = sql & "FROM tblSchedule "
    sql = sql & "Where JobNo='" & sJobNO & "'"
    
    Set rs = Db.OpenRecordset(sql, dbOpenDynaset, _
    dbSeeChanges)
    
       rs.MoveFirst
    [b]   
  'why would this get only TWO rows, UNLESS you executed the query for a SINGLE JobNo???[/b]
       Do Until rs.EOF
          GetJobType = rs.Fields("JobNo") & "/"
          GetJobType = GetJobType & rs.Fields("JobType")
          GetJobType = GetJobType & "/" & rs.Fields("PartNo")
          GetJobType = GetJobType & "/" & rs.Fields("Shape")
          GetJobType = GetJobType & vbCrLf
          rs.MoveNext
       Loop
       
    rs.Close
    Db.Close
    
    Set rs = Nothing
    Set Db = Nothing
End Function

Skip,
[sub]
[red][/red]
[/sub]

 

[HarleyQuinn]

Might sound like a silly question but why not just have your SQL concatenate the fields within the function?

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[socomfort]

Hello Skip and HarleyQuinn:

Skip, thanks for your input. I will look at your code straight away. One question: when you say that, "The string argument does not seem to do ANYTING for this function..." are you referring to the first 'As String' argument within the function prototype: "GetJobType(strMyString As String)"?

I realized I could not make it work without actually setting the function equal to something. That is why I 'tested it' using: "GetJobType = Format(strMyString, Text)", not that this does anything except set the definition. I notice that you actually set GetJobType equal to the recordset concatenation.

Btw, I thought I had to build the string first as strMyString using the recordset, etc. and then pass it to the function.

Harley, I know I have likely confused the issue b/c I myself am not adept at VBA coding just yet.

Let me explain what I am trying to do:

You are right, it would be a lot easier to concatenate the fields in SQL. So originally I wrote a SQL Select statement that returned 14 fields from a table. Normally, this represents 1 record (row).

One of those fields is JobNo. In fact, what I want to do is return all records for each JobNo in the table. However, the problem is that JobNo is not discrete; each one can have multiple rows of data b/c one field (JobType) can have many instances for each JobNo.

As output, I can only have 1 row for each JobNo (that is the requirement). That is why I was trying to concatenate the fields as above. What I was trying to do was to "replace" the fields in question with one field containing the concatenation of each and separated by "/".

I don't know if this makes sense. The following four fields were originally part of the SQL statement pulling the 14 fields:

1.
JobNo = 12345
Jobtype = priority
PartNo = 67
Shape = round

2.
JobNo = 12345
Jobtype = hold
PartNo = 67
Shape = round

So that what the function concatenates is:

12345/priority/67/round
12345/hold/67/round

in ONE string.

This result is rendered within a larger query such that it all fits on ONE row. Perhaps there is another way to do it all in one shot. But I thought this would be more efficient.

I am already using 3 IIf statements within the principal query and I notice that it does run a little slower b/c of it.

Anyway, thank you so much for your responses. When I grow up, I want to be a good coder like you guys!

 

[PHV]

Have a look here: faq701-4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886