using fixed length strings

Question

Hello. I'm trying to place four fields (coming in from a query) into fixed positions on a single fixed length string. I can tell Access VB to load the first field into the string, but I don't know how to put the second field star into the string starting in the 26th (as an example) character position, and the third field into the 32nd, and the fourth into the 39th. Assistance would be greatly appreciated. Maybe I'm even thinking of this as the wrong way- not using a fixed length string but an array of 50 single character elements, but again I'm not getting that to work either. I apologize if its answered before, but I can't seem to find the question in searching. This is not an output to a text file application, so its not an export thing.

Thanks, George

Answer 1

Assuming your form has 2 textboxes named s1 and s2 respectively and then you add another textbox with a control source of =GenFStr([s1],[s2])

it works just fine.  That said, you'd need to make the function smarter so you don't get #Error when missing values....

Answer 2

Again, thanks. the problem appears to be in the type of field I'm trying to put the solution in. I'm getting an Error 438 "Object doesn't support this property or method". So its the result field rather than the function itself that is the problem. The example of Print or msgbox works fine, but if I try to put the result into a field usable on a form or a report, I can't seem to find an object type that will allow field = function(s1,s2...

Answer 3

Not sure why you are having problems.  Both of the following work without any issues:

Function GenFStr(sFirstString As String, sSecondString As String) As String

    'usage example: ? "'" & GenFStr("something","try") & "'"

    GenFStr = Left(sFirstString + Space(25), 25) & Left(sSecondString + Space(6), 6)

End Function

Sub GenFString()

    Dim sFirstString    As String

    Dim sSecondString   As String

    Dim sGenString      As String

    sFirstString = "something"

    sSecondString = "try"

    sGenString = Left(sFirstString + Space(25), 25) & Left(sSecondString + Space(6), 6)

    Debug.Print "'" & sGenString & "'"

End Sub

Answer 4

Thanks, but I can't seem to get that to work only because I don't know what kind of variable to use. It won't let me use a string or a text box or a label or an array, so what type is x in "x=left(sFirstString+space(25),25)...

Thanks again. George

Answer 5

Something like

left(sFirstString+space(25),25) & left(sSecondString+space(6),6)...

You get the general idea.