Building a String from Multiple records 2

[Hattrick16]

I need to build a string from multiple records in a table that share common elements. Here is a sample of the data

Data:

Name Adress SS # Date Sales Code
Nick 123 State 123-45-6789 1/2/2005 100.00 A
Nick 123 State 123-45-6789 1/3/2005 200.00 B
Nick 123 State 123-45-6789 1/4/2005 300.00 C
Nick 123 State 123-45-6789 1/5/2005 400.00 D

I need an ouput that will Look like this :

Name Address SS # Date Sales Code Date2 Sales2 Code2 ....
up to 5 transactions per Name Address and SS # Each field has a fixed length. This will ultimately exported into a text file to be imported into a 3rd party application.

 

[PHV]

using fields that contain apostrophes as criteria is a pain
Really ?
The SQL way to play with such fields is to double each single quote:
strSQL = "SELECT blah blah ..." _
& " WHERE somefield=[tt]'"[/tt] & Replace([criteria variable], [tt]"'", "''"[/tt]) & [tt]"'"[/tt]

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

 

[Tranman]

Hattrick,
PHV is right about the double-single quote thing, but I still believe that you're better off without the single quotes (apostrophes) in there if you can do without them.

As to your undefined function error, it sounds like you may have a blown/missing reference (maybe the Scripting Runtime???) I'm not sure what library the Replace function lives in. Look in Tools|References for one that says "Missing".

Why not post your SQL and give us a look at it?

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain