MartyConnelly
martyconnelly at shaw.ca
Tue Oct 14 13:58:14 CDT 2003
Here are some Access 97 replacement functions
Not fully tested
Function Split(ByVal Expression As String, Optional Delimiter = " ", _
Optional limit As Long = -1, Optional Compare As Integer =
vbBinaryCompare _
) As Variant
'*******************************************
'Name: Split (Function)
'Purpose: Emulates the A2k/A2k2 Split function
'Author: Terry Kreft
'Date: December 13, 2001, 02:47:07
'Called by: Any
'Calls: None
'Inputs:
' Expression - The string to split
' Delimiter - The delimiter to split on
' limit - How many terms to return
' (Default -1 return all terms)
' Compare - How to make the string comparison
' for the delimiter
' This should be
' vbBinaryCompare = 0 (Default)
' vbTextCompare = 1
' vbDatabaseCompare = 2
'Output:
'*******************************************
Dim varValues As Variant
Dim lngCount As Long
Dim intInstr As Integer
Dim intLenDelim As Integer
Const ARRAY_LOW_BOUND = 0
On Error GoTo Split_err
varValues = Array()
If limit <> 0 Then
lngCount = 0
intLenDelim = Len(Delimiter)
intInstr = InStr(1, Expression, Delimiter, Compare)
Do While intInstr <> 0 And lngCount - limit + 1 <> 0
ReDim Preserve varValues(ARRAY_LOW_BOUND To lngCount)
varValues(lngCount) = Left(Expression, intInstr - 1)
Expression = Mid(Expression, intInstr + intLenDelim)
intInstr = InStr(1, Expression, Delimiter, Compare)
lngCount = lngCount + 1
Loop
If Len(Expression) <> 0 Then
ReDim Preserve varValues(ARRAY_LOW_BOUND To lngCount)
varValues(lngCount) = Expression
End If
End If
Split = varValues
Split_end:
Exit Function
Split_err:
With Err
MsgBox .Number & ": " & .Description, vbExclamation, .Source
End With
Resume Split_end
End Function
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B188007
Option Explicit
Public Function Join(source() As String, Optional _
sDelim As String = " ") As String
Dim sOut As String, iC As Integer
On Error GoTo errh:
For iC = LBound(source) To UBound(source) - 1
sOut = sOut & source(iC) & sDelim
Next
sOut = sOut & source(iC)
Join = sOut
Exit Function
errh:
Err.Raise Err.Number
End Function
Public Function ReadUntil(ByRef sIn As String, _
sDelim As String, Optional bCompare As VbCompareMethod _
= vbBinaryCompare) As String
Dim nPos As String
nPos = InStr(1, sIn, sDelim, bCompare)
If nPos > 0 Then
ReadUntil = Left(sIn, nPos - 1)
sIn = Mid(sIn, nPos + Len(sDelim))
End If
End Function
Public Function StrReverse(ByVal sIn As String) As String
Dim nC As Integer, sOut As String
For nC = Len(sIn) To 1 Step -1
sOut = sOut & Mid(sIn, nC, 1)
Next
StrReverse = sOut
End Function
Public Function InStrRev(ByVal sIn As String, sFind As String, _
Optional nStart As Long = 1, Optional bCompare As _
VbCompareMethod = vbBinaryCompare) As Long
Dim nPos As Long
sIn = StrReverse(sIn)
sFind = StrReverse(sFind)
nPos = InStr(nStart, sIn, sFind, bCompare)
If nPos = 0 Then
InStrRev = 0
Else
InStrRev = Len(sIn) - nPos - Len(sFind) + 2
End If
End Function
Public Function Replace(sIn As String, sFind As String, _
sReplace As String, Optional nStart As Long = 1, _
Optional nCount As Long = -1, Optional bCompare As _
VbCompareMethod = vbBinaryCompare) As String
Dim nC As Long, nPos As Integer, sOut As String
sOut = sIn
nPos = InStr(nStart, sOut, sFind, bCompare)
If nPos = 0 Then GoTo EndFn:
Do
nC = nC + 1
sOut = Left(sOut, nPos - 1) & sReplace & _
Mid(sOut, nPos + Len(sFind))
If nCount <> -1 And nC >= nCount Then Exit Do
nPos = InStr(nStart, sOut, sFind, bCompare)
Loop While nPos > 0
EndFn:
Replace = sOut
End Function
Oleg_123 at xuppa.com wrote:
>Thanks, I'll declare aRecord as string. Its this part I am having trouble
>with
>
>strSQL = "INSERT table value(" & aRecord(0) & "," & aRecord(8) & ")"
>con.Execute (strSQL)
>
>Where do I specify to what table, and to what fields (in this database) do
>I want records to be inserted ?
>Also, Access97 doesn't have split function ? I did that part at home on
>Access2000
>
>
>
>>Some ideas:
>>
>>I assume what you are saying is that the date is stored in your csv
>>table as yyyymmdd and access is reading it as mmddyyyy (ie american date
>>format) and thus your dates are screwed up.
>>
>>Using dim aRecord as you have will default the variable to a variant.
>>(A side note, I always explicity declare, that way I avoid unexpected
>>surprises if MS decides to change the default behaviour)
>>
>>I don't know what split does, but I am assuming it tries to guess what
>>the variable type is and then stores it as that type. I would first
>>check your PC date format, but I suspect that you will be stuck with US
>>interpretation and will have to "manually" force the date to be read the
>>correct manner.
>>
>>First suggestion would be to dim aRecord as a string. This will force
>>the date (and other fields) to be stored as they appear in the csv file.
>> Then you can split the date field using left$/right$/mid$ and datepart.
>>
>>If I am incorrectly guessing what your problem is, maybe someelse will
>>have a better idea.
>>
>>Stuart
>>
>>
>>
>>>-----Original Message-----
>>>From: accessd-bounces at databaseadvisors.com
>>>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
>>>Oleg_123 at xuppa.com
>>>Sent: Wednesday, 15 October, 2003 12:10 AM
>>>To: accessd at databaseadvisors.com
>>>Subject: [AccessD] converting csv to table
>>>
>>>
>>>Hey Group
>>>I have to convert a CSV file to Access table, with one of the records
>>>changing from yearmonthdate(19980123) to
>>>monthdateyear(01231998) format.
>>>So I gues I can't use macro for that since it doesn't recognise
>>>yearmonthdate format. I wrote the code below yesterday and
>>>tested it at
>>>home on diff table and it worked up till including Debug.Print
>>>aRecord(8)line then I had trouble sending received data to
>>>table. Now it
>>>doesn't work at all.. any suggestions ?
>>>
>>>
>>>Dim fs As New Scripting.FileSystemObject
>>>Dim ts As Scripting.TextStream
>>>Dim strLine As String
>>>Dim aRecord
>>>Dim strSQL As String
>>>Dim con As New ADODB.Connection
>>>
>>>If fs.FileExists("U:\Users\HRIS\oleg\cars testing\trainFROM.csv") Then
>>>
>>> Set ts = fs.OpenTextFile("U:\Users\HRIS\oleg\cars
>>>testing\trainFROM.csv", ForReading)
>>>
>>> Do Until ts.AtEndOfStream
>>>
>>> strLine = ts.ReadLine
>>> aRecord = split(strLine, ",")
>>>
>>> aRecord(8) = Mid(aRecord(8), 5) & Left(aRecord(8), 4)
>>>
>>> Debug.Print aRecord(8)
>>>
>>> strSQL = "INSERT table value(" & aRecord(0) & "," &
>>>aRecord(8) & ")"
>>> 'con.Execute (strSQL)
>>>
>>> Loop
>>>Else
>>> MsgBox ("file doesn't exist")
>>>End If
>>>
>>>
>>>
>>>
>>>-----------------------------------------
>>>Get Breaking News from CNN, ABC, NBC, CBS Now.
>>>http://www.xuppa.com/news/?link=webmail
>>>
>>>
>>>_______________________________________________
>>>AccessD mailing list
>>>AccessD at databaseadvisors.com
>>>http://databaseadvisors.com/mailman/listinfo/accessd
>>>Website: http://www.databaseadvisors.com
>>>
>>>
>>>
>>_______________________________________________
>>AccessD mailing list
>>AccessD at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/accessd
>>Website: http://www.databaseadvisors.com
>>
>>
>
>
>
>-----------------------------------------
>Get Breaking News from CNN, ABC, NBC, CBS Now.
>http://www.xuppa.com/news/?link=webmail
>
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>
>
--
Marty Connelly
Victoria, B.C.
Canada