Solved

MS Access convert a bitwise string into multiple rows

Posted on 2016-08-04
5
23 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Full details of the approach are in my comment.
Dale suggested what was needed
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now