Create table and records macro.. 1

[CRCRCR]

I'm not sure is this is really possible. I can do this manually using Excel and pasting the results back into Access, but would love to have a proper solution.

I have an Access database of (scuba) dives. I create a report of the data using Crystal. I have created a whole new table, to facilitate this.

The table provided is basically, two main fields

DIVE_NO (numeric, key) and PROFILE (string)

1 000000123012031023012345
2 000323050345034503405000454
3 003405034503405034050345030450345

Now the profile is basically a 12 charachter string (always numbers) for every 20 seconds. So an hour long dive would have 12*3*60 charachters in it. The profile string can be of any length, but still a factor of 12

The information I'd like to extract is (in Excel)

MID(Profile,1,5)
MID(Profile,13,5)
MID(Profile,25,5)
MID(Profile,37,5)
MID(Profile,49,5)

i.e the depth is 5 charachters long, found 12 charachters apart.

So from the original table, would it be possible, automagically, to create

KEY DIVE_NO TIME DEPTH
1 1 0 00000
2 1 20 00010
3 1 40 00240
4 1 60 00570
etc..
5 2 0 00000
6 2 20 00024
7 2 40 00340
8 2 60 00678
9 2 80 00890
10 2 100 01200
11 2 120 01800

etc...

Hope this is possible. I don't really know where to begin.

 

[JBinQLD]

G'day,

Not sure I follow your example but this should mimick the behaviour of your excel formula and put the records into a table:

Function ProfileToTable(strProfile As String, intDiveNo As Integer)

Dim intBreak As Integer
Dim intLength As Integer
Dim intCounter As Integer
Dim intTime As Long

Dim db As database
Dim rs As Recordset

Set db = CurrentDb
'assume output table is called "yourtable"
'with fields:
'Dive(int)
'sTime=long integer
'depth(string)

Set rs = db.openrecordset("YourTable", dbopendynaset)

intBreak = 12
intTime = 20
intLength = Len(strProfile) - intBreak

'loop through the string in blocks of intBreak(12)
For intCounter = 1 To intLength Step intBreak
    
    rs.AddNew
        rs!Dive = intDiveNo
        rs!sTime = intTime
        'get depth as per your example...
        rs!depth = Mid(strProfile, intCounter, 5)
    rs.Update
    'increment timer
    intTime = intTime + 20

Next intCounter
    
rs.Close
 
 'clean up
Set rs = Nothing
Set db = Nothing


End Function

JB

 

[CRCRCR]

Thank you so much.

Not really sure how to fire off the function (I've pasted the code into a new module so far), but I believe I can now create a macro from what you have given me. Just needed a little push in the right direction. I'll figure it out from here on.

Thanks a bunch, Kai.

 

[JBinQLD]

If you've made the table just create a form and a command button. For the onclick event of the button add (to test for now)

Call ProfileToTable("003405034503405034050345030450345",3)
msgbox("Done - check the table for output")

Should mimic the third example line you gave in your example.

I also noticed a rather obvious typo - I initialise the intTime variable to 20 which should be 0. The code is untested but as you say, I'm sure you can take it from there.

JB

 

[CRCRCR]

Works....

Had to change the variables types

DAO.database
DAO.recordset

and tick something in the references and other little fiddles...

Learning about this slowly. Just noticed you can't record macros like in Excel. So much more complicated than Excel.

Thank you so much.

I think I'll write a macro though, something I can step through, and do a bit of error trapping. I don't really understand forms and events. Functions are so much easier to understand in Excel. All right, I'll shut up about Excel.

Cheers, Kai.