Solved

MS Access convert a bitwise string into multiple rows

Posted on 2016-08-04
5
39 Views
Last Modified: 2016-10-08
Ok,
I have data like so
JobID Long
ProvincialStamps Text(14)


ProvincialStamps is a string of 1's and 0's: "1100000000000" for example.
This is all very nice and I can do beautiful things like

If InStr(3, StampVal, "1") = "3" Then
   'do some stuff when the third character is a 1
End if


and

Dim myboolean As Boolean
myboolean = False
If InStr(11, StampVal, "1") = "11" Then
    myboolean = True
End If
If InStr(12, StampVal, "1") = "12" Then
    myboolean = True
End If
If InStr(13, StampVal, "1") = "13" Then
    myboolean = True
End If

If myboolean = True Then
   'do some stuff if any of 11-13 are a 1
end if


but now, sadly, I have to drive a report with one record for each place in the ProvincialStamps string.
So that involves transforming that string into 14 records.

What's a nice way of doing that?
0
Comment
Question by:Nick67
  • 3
5 Comments
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 500 total points
ID: 41743247
Nick, the way I handle this is with a table and a function.  Call it tblBitWise give it a single field (intNumber) and 14 values 1-14.  Then create this function:

Public Function fnBitPosition(Stamp as string, Position as integer) as string

    fnBitPosition = Mid(Stamp, Position, 1)

End Function

Open in new window

Then use a query:

SELECT intNumber, fnBitPosition([ProvincialStamp], intNumber) as PositionVal
FROM tblBitWise
ORDER BY intNumber
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 0 total points
ID: 41743292
I figured that a table would be needed.
And things are a bit more complex, because I need the data for a subreport.
So I need a PK/FK in the result for that to work.
So, I built a table, tempStampData
StampID Integer
JobId Long
Province Text(3)
Needed Yes/No

And code to empty/fill it

Private Sub BuildTempStampData()
Dim db As Database
Dim rs As Recordset
Dim x As Integer

Set db = CurrentDb

'kill existing records in tempStampData
DoCmd.SetWarnings False
db.Execute "DELETE tempStampData.* FROM tempStampData;", dbSeeChanges
DoCmd.SetWarnings True

Set rs = db.OpenRecordset("Select * from tempStampData;", dbOpenDynaset, dbSeeChanges)
Dim StampVal As String
StampVal = Nz(Me.ProvincialStamps, "0")
If StampVal = "0" Then
    Exit Sub
End If

With rs
    For x = 1 To Len(StampVal)
        .AddNew
        !StampID = x
        !JobID = Me.JobID
        If InStr(x, StampVal, "1") = x Then
            !needed = 1
        Else
            !needed = 0
        End If
        Select Case x
            Case 1
                !Province = "BC"
            Case 2
                !Province = "AB"
            Case 3
                !Province = "SK"
            Case 4
                !Province = "MB"
            Case 5
                !Province = "ON"
            Case 6
                !Province = "QC"
            Case 7
                !Province = "NB"
            Case 8
                !Province = "NS"
            Case 9
                !Province = "NL"
            Case 10
                !Province = "PE"
            Case 11
                !Province = "NT"
            Case 12
                !Province = "YT"
            Case 13
                !Province = "NU"
        End Select
        .Update
    Next x
End With
End Sub

Open in new window


I can drive a subreport with that data.
I was trying to build a string for a textbox, and while that can get done, with A2003 still in play, my formatting options left a lot to be desired.  With the subreport, I can break everything down to more controls and bold, underline, and change font size more granularly.

Anyone have a better/different suggestion?
0
 
LVL 26

Author Comment

by:Nick67
ID: 41826445
Busted bloody UI.
It wasn't abandoned as I had the last comment.
But no one else chimed in.

I'd accept Dale's comment as the answer, but there is now no UI to do so.
****Edit*****
F5 refresh made UI to accept previous comments available.
Now, clicking on any of them makes the bottom 'closing wizard pop up and then disappear.
****Edit****
A further refresh made the closing UI workable.
0
 
LVL 26

Author Closing Comment

by:Nick67
ID: 41834862
Full details of the approach are in my comment.
Dale suggested what was needed
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question