Putting generated strings into reports

[galorin]

I use the following code to generate a production number from values stored in a MySQL database. What I want to do is to use this generated number within an acces report. How can I get this arbitrary value into a report, short of server-side stuff, which is proving to be harder than I anticipated.

SQLout = "SELECT h.ht_id, h.softver, h.House_type"
SQLout = SQLout & " , b.ShortCode AS builder_ShortCode"
SQLout = SQLout & " , q.design_level"
SQLout = SQLout & " , h.Floor"
SQLout = SQLout & " , h.Revision"
SQLout = SQLout & " , d.ShortCode AS dist_ShortCode"
SQLout = SQLout & " FROM HouseType AS h"
SQLout = SQLout & " INNER JOIN quotations as q on q.quote_id = h.quote_id"
SQLout = SQLout & " INNER JOIN CompShort as b ON b.Comp_ID = q.build_id"
SQLout = SQLout & " INNER JOIN CompShort as d ON d.Comp_ID = q.dist_id"
SQLout = SQLout & " WHERE h.quote_id = " & Me.se
SQLout = SQLout & " and deleted = 0"
SQLout = SQLout & " ORDER BY House_Type"

Call connectDB
Call getData

If Not rst.EOF Then
        rst.MoveFirst
        Do
        'loop through resultset
        f(0) = rst.Fields(0)
        f(1) = rst.Fields(1)
        For I = rst.Fields.Count - 1 To 2 Step -1
         If IsNull(rst.Fields(I)) Then
          f(I) = ""
         ElseIf rst.Fields(I) = "non" Then
          f(I) = ""
         ElseIf I = 6 Then [COLOR=green]'field where integer value of revision is kept[/color]
           If rst.Fields(I) = 0 Then
            f(I) = ""
           Else
            rev = rst.Fields(I)
            f(I) = "=rev-" & Chr(96 + rev) [COLOR=green]'Converts integer revision into a letter[/color]
           End If
        Else
        f(I) = "=" & rst.Fields(I)
        End If
        Next I
            field0 = rst.Fields(2)
            [COLOR=green]'field format is version=builder-region=level=housetype=floor=revision=distributor[/color]
            field1 = f(1) & f(3) & f(4) & f(2) & f(5) & f(6) & f(7)
            Me.ListPos.AddItem item:="" & f(0) & ";" & field1 & ""
        rst.MoveNext
        Loop Until rst.EOF
    End If
cn.CLose

 

[Remou]

How about a global variable and a function to return the value of that variable?

 

[galorin]

That would work except for the fact that with this being a production number, the report will contain a dozen or more different production numbers. Sorry if it's not worded well.

The report I have in front of me, that I am trying to replace lists the builder, and every design production number going out to that builder. It also lists how long each design took to finish, and when it was sent to the builder.

 

[Remou]

Is there a problem with basing the report on a query?

 

[galorin]

Sorry, what the query spits out gets passed through the code above to give me my design production number. I don't store the number in the database, just the info required to programatically generate the number. I've tried to do it server-side as a MySQL view, but so far I've had no luck, so I am looking for a client-side method.

 

[Remou]

I should have put that more clearly, you can link a table, or a query, or you can use a pass through query or In to get data from your database. An Access query is quite happy with a field set to, say:

GenRev: "Rev " & Chr(96 + [Rev]) & " " & FieldXYZ

 

[galorin]

I think we're confusing each other. The above SQL query spits out the following, as CSV:

"ht_id","softver","House_type","builder_ShortCode","design_level","Floor","Revision","dist_ShortCode"
102162,"v3.4","azure","aab","Level 2","g","0","aab"
102164,"v3.4","azure","aab","Level 2","f","1","aab"
102166,"v3.4","fuscia","aab","Level 2","g","0","aab"
102165,"v3.4","green","aab","Level 2","g","1","aab"

The string values that go into Field1 in my code wind up looking like this, given the CSV just above. Each is basically a "word" with the = sign acting as just something to separate one portion of the info from the next. It also differentiates between old-style and new-style numbers.

"v3.4=aab=Level 2=azure=g=aab"
"v3.4=aab=Level 2=azure=f=rev-a=aab"
"v3.4=aab=Level 2=fuscia=g=aab"
"v3.4=aab=Level 2=green=g=rev-a=aab"

If I understand correctly, what you have suggested will work for one field from my CSV above, and can concatenate the entire string together given no conditions. However because I am conditionally concatenating values together to create a string, I need a solution that will do this kind of concatenation within the report.

If it is possible to have interaction with the report through VBA I may be able to get this done, otherwise, I am at a loss. Thanks for your efforts so far, I know this aint an easy problem.

 

[Remou]

You can refer to a function in a query or a report. However, I think that we are confusing one another. It would, perhaps, be easier to get a meeting of minds with a sample of the data input and the expected output.

 

[galorin]

Take a look at the code I started the thread with, and I'll try to fill in the missing bits. Ignore the folowing lines of code, as they are necessary for building up a listbox.

field0 = rst.Fields(2)

Me.ListPos.AddItem item:="" & f(0) & ";" & field1 & ""

SQLout is a global variable
connectdb is a subprocedure that spawns a connection to MySQL
getdata is a subprocedure that executes the SQL on the server, and that goes into a result set, rst.

rst.Fields(0-7) contain all the info I need to build the string. The CSV shown above is an actual set of results from the query in my code from the start. As one line of sample input and output,

input: rst.fields(1-7 respectively)
"v3.4" | "azure" | "aab" | "Level 2" | "g" | "0" | "aab"

The desired output is:
"v3.4=aab=Level 2=azure=g=aab"

If you need me to explain anything about my project or anything related, just ask. Thanks for your patience so far.

 

[Remou]

I am sorry, I still do not understand why you cannot link the table and create a query. The sample output you show can easily be created with a query using a UDF if necessary.

 

[galorin]

Maybe I just don't see how to do it, as a report seems to me a completely backwards way of doing things. It's probably quite easy once I put my head on backwards and turn my chair around.

Also as I am coming to Access from a non-Microsoft progamming background, I am unfamiliar with the acronyms. What does UDF stand for?

 

[Remou]

User Defined Function.

 

[galorin]

Ta, now I can see if I can get it figured out via google, now that I know what I am looking for. Sorry it took so long to get here, I feel thick.