Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010

    Question Unanswered: Filling Rows with value

    Hi! Please reference attached "Access_Question."

    I'm importing some data from a text file. In short, I'm trying to make this report (which is typically paper-based) database ready. The only problem I'm having is populating a value in this one column (Acct Unit).

    I've used a mid query to extract data from column "Inv Code 1" into a temp field. This value is applied to certain lines and changes throughout the course of the data (ex "" eventually becomes so as I populate down into "Acct Unit" field. I always need it to grab the last known value.

    I was thinking that a DLast/DMax query might do the trick but I'm a little fuzzy on implementing it.

    Any ideas please?
    Attached Thumbnails Attached Thumbnails Access_Question.jpg  

  2. #2
    Join Date
    Oct 2004
    Oxfordshire, UK
    Split it and increment it in a function which returns the new value:

    Public Function IncrementNumericPart(ByVal Expression As Variant, Seperator
    As String, Optional PartIndex As Long) As String
    On Error Resume Next
    Dim varPart As Variant
    Dim varParts As Variant

    If Nz(Expression, "")="" Then Exit Function

    varParts = Split(varParts, Seperator)
    'Exit if the part doesn't exist.
    If (PartIndex < LBound(varParts)) Or (PartIndex > UBound(varParts)) Then Exit Function

    'Exit if part isn't a number.
    If Not IsNmmeric(varParts(PartIndex)) Then Exit Function

    'Increment the specified part.
    varParts(PartIndex) = CLng(varParts(PartIndex)) + 1

    'Concatonate the parts.
    Expression = ""
    For Each varPart In varParts
    If Not Expression = "" Then Expression = Expression & Seperator
    Expression = Expression & varPart
    Next varPart

    'Return function value.
    IncrementNumericPart = Expression

    End Function

    In this case:
    NewNumber = IncrementNumericPart(OldNumber, ".", 2)

    You could use this in a query or code.

Posting Permissions

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