Split in access 97

Posted on 2005-03-01
Medium Priority
Last Modified: 2008-01-09
I have converted my access 2003 project to access 97.And the following code breaks

Me.RecordSource = "SELECT .... WHERE (((Titel.TitelID)=" & Split(puStrReportParams, "-")(0) & ") AND ..."

at the Split()

Sub or function not defined.

any ideas on how to make split work?
Question by:DaFou
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 46

Assisted Solution

tbsgadi earned 200 total points
ID: 13436921
The functions here by Roman Koch do the trick

Good Luck!

LVL 54

Assisted Solution

nico5038 earned 200 total points
ID: 13437059
Checkout this question:

Or use the Left() function like
WHERE (((Titel.TitelID)=" & Split(puStrReportParams, "-")(0) & ") AND ..."
as you need just the first part before the "-"

WHERE (((Titel.TitelID)=" & trim(Left(puStrReportParams, Instr(puStrReportParams,"-")-1)) & ") AND ..."

The trim is used to remove leading/trailing blanks from the string you get and Instr(puStrReportParams,"-") returns the position of the first "-" in the field.

Clear ?


Author Comment

ID: 13437754
Split in access 2003 return a variant type variable that in essence acts as an array.

I will need to split a string on a delimiter and cycle trough the array.

How can something like this be done in access 97?
LVL 44

Accepted Solution

Arthur_Wood earned 1600 total points
ID: 13438395
here is the source code for an Access 97 version of the Split function, that behaves just like the version that is built into Access 2000 and newer:

Place this code in a standard MODULE (not a Class Module):

Public Function Split(Source As String, Splitter As String) As Variant
    Dim strInput As String
    Dim iloc As Integer
    Dim iCount As Integer
    Dim tempArray() As String
    strInput = Source
    iloc = InStr(strInput, Splitter)
    Do While iloc > 0
        iCount = iCount + 1
        ReDim Preserve tempArray(iCount)
        tempArray(iCount) = Left$(strInput, iloc - 1)
        strInput = Mid$(strInput, iloc + Len(Splitter))
        iloc = InStr(strInput, Splitter)
    If Len(strInput) > 0 Then
        iCount = iCount + 1
        ReDim Preserve tempArray(iCount)
        tempArray(iCount) = strInput
    End If
    Split = tempArray
End Function

LVL 44

Expert Comment

ID: 13448373
glad to be of assistance.


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

800 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