Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# MS Access convert a bitwise string into multiple rows

Posted on 2016-08-04
Medium Priority
56 Views
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
Question by:Nick67
• 3

LVL 49

Assisted Solution

Dale Fye earned 2000 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
``````
Then use a query:

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

LVL 26

Accepted Solution

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)
!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
``````

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

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*****
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

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

## Featured Post

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month12 days, 5 hours left to enroll