Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A simple Calling function (instead of query) (Access 2000)

    Its usually the easiest things that trip me up.
    Previously, the below small function was called from a query as such GetSize([ImportExcel].[F3]) AS [Size]

    --------------------
    Public Function GetSize(Description As String) As String
    Dim iStPos As Integer, iEndPos As Integer
    iStPos = InStr(1, Description, """", vbTextCompare)
    If iStPos = 0 Then
    GetSize = ""
    Else
    iEndPos = iStPos - 1
    Do While iStPos > 0
    If Mid(Description, iStPos, 1) = " " Then
    iStPos = iStPos + 1
    Exit Do
    End If
    iStPos = iStPos - 1
    Loop
    If iStPos = 0 Then
    GetSize = ""
    Else
    GetSize = Mid(Description, iStPos, iEndPos - iStPos + 1)
    End If
    End If
    End Function
    --------------------------

    Now how would I call this function from within the below sql statements so the function can execute and insert the 'size' value into a field called "Size".
    Thankyou in advance


    If Not blErr Then
    sSql = "INSERT INTO tblSheets ( ItemNo, QtyReqd, Description, MatlGrade, Length,"
    sSql = sSql & " [Length plus CTF], Width, WeightFactor, Weight, SAreaFactor, SArea, MrNo, FreeIssue, Remarks, SBMICostCodes,"
    sSql = sSql & " ExcelSheetName )"

    sSql = sSql & " SELECT F" & iItemNo & ", F" & iQtyReqd & ", " & sDescription & ", F" & iMatlGrade & ","
    sSql = sSql & " F" & iLength & ", F" & iLengthCtf & ", F" & iWidth & ", F" & iWeightFactor & ",F" & iWeight & ","
    sSql = sSql & " F" & iSAreaFactor & ",F" & iSArea & ",F" & iMrNo & ",F" & iFreeIssue & ","
    sSql = sSql & " F" & iRemarks & ",F" & iSbm & ", '" & strMatch & "'"
    sSql = sSql & " FROM ImportExcel"
    sSql = sSql & " WHERE (((IsNumeric(Left([F" & iColSt & "],2)))=True));"
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "qryUpdatetblSheets"
    On Error GoTo Err_CommandImport_Click
    Set qdf = dbs.CreateQueryDef("qryUpdatetblSheets", sSql)
    sSql = "qryUpdatetblSheets"
    DoCmd.OpenQuery sSql
    sSql = "UPDATE [tblSheets] SET RefDWG='" & strRefDWG & "', [DocuNo]='" & strDocuNo & "'"
    sSql = sSql & ", ConstPart='" & strConstPart & "', REV='" & strRev & "'"
    'sSql = sSql & ", GetMod='" & Left(strconstunit, 7) & "'"
    sSql = sSql & " WHERE RefDWG is Null;"
    DoCmd.RunSQL sSql
    End If

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A simple Calling function (instead of query) (Access 2000)

    Hi Mark,

    Try this SQL statement:

    sSql = "INSERT INTO tblSheets ( ItemNo, QtyReqd, Description, MatlGrade, Length,"
    sSql = sSql & " [Length plus CTF], Width, WeightFactor, Weight, SAreaFactor, SArea, MrNo, FreeIssue, Remarks, SBMICostCodes,"
    sSql = sSql & " ExcelSheetName<big>, Size</big> )"

    sSql = sSql & " SELECT F" & iItemNo & ", F" & iQtyReqd & ", " & sDescription & ", F" & iMatlGrade & ","
    sSql = sSql & " F" & iLength & ", F" & iLengthCtf & ", F" & iWidth & ", F" & iWeightFactor & ",F" & iWeight & ","
    sSql = sSql & " F" & iSAreaFactor & ",F" & iSArea & ",F" & iMrNo & ",F" & iFreeIssue & ","
    sSql = sSql & " F" & iRemarks & ",F" & iSbm & ", '" & strMatch & "'<big>, GetSize(F3)</big>"
    sSql = sSql & " FROM ImportExcel"
    sSql = sSql & " WHERE (((IsNumeric(Left([F" & iColSt & "],2)))=True));"

    PS Do you have a specific reason for saving the first SQL statement as a query (and executing it), and only executing the second (using RunSQL)?

  3. #3
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A simple Calling function (instead of query) (Access 2000)

    Very Beautiful Hans, worked like a charm. Thanks heaps
    The aim of the runsql code is to check the fields in an excel sheet to verify if they match the names in the headings. If you look at my previous post, Pat helped me out with this one.
    ps Is the dual use of query and run sql not good practice?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A simple Calling function (instead of query) (Access 2000)

    Hi Mark,

    There is nothing against using either a query or RunSQL or a combination of both. I was just wondering if you had a special purpose, since you could as well have used RunSQL for both. But it's not important at all; if it works, there is no reason to change it.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: A simple Calling function (instead of query) (Access 2000)

    Hans version will work if the field you are getting the size from is F3, however the size is in field "F" & iDescription, so change Hans solution from:
    GetSize(F3)
    to:
    "GetSize(F" & iDescription & ")"

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A simple Calling function (instead of query) (Access 2000)

    Thanks, Pat, the variable iDescription doesn't feature in Mark's post, and I haven't been following the other threads about this database in sufficient detail to know it.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: A simple Calling function (instead of query) (Access 2000)

    I hope you didn't take offence at that, you weren't to know about iDescription.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A simple Calling function (instead of query) (Access 2000)

    Pat, I didn't take offense at all!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •