Results 1 to 9 of 9

Thread: VBA (2000)

  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA (2000)

    Hi - I have a table tbl_Normalized which stores field Id, Period and Value. Each Id may have several hundred periods/values. Can anyone help me with VBA coding the following two requirements a)find Ids which have the same value for many periods, which then changes for a number of other periods [img]/forums/images/smilies/cool.gif[/img] find Ids which may have either null values or the same value for many periods, followed by one value, followed by more nulls. What I'm trying to do is find depreciation values which represent impairments or method changes, if there are any depreciation gurus out there.

    All suggestions gratefully received!
    InaFix

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

    Re: VBA (2000)

    What is "many" (in "for many periods")? More than 100, or more than 10, or more than 1?

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA (2000)

    Hi Hans - More than 6 would do it probably.
    cheers
    Ina

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

    Re: VBA (2000)

    This could become quite complicated, but see the attached database for a start. I created a sample table and a module basCode with a procedure you can run by clicking anywhere inside it and pressing F5.
    When it has finished, open the table to see which records have a check mark (or look at the query qryMarked).

    Note: the code requires a reference to the Microsoft DAO 3.6 Object Library in Tools | References...

    Test thoroughly!

  5. #5
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA (2000)

    Hans, thank you very much!

    Will do.

    Merry xmas. Ina

  6. #6
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA (2000)

    Hi Hans, this is giving me the error "Syntax Error in Update" - obviously bit of a module virgin so can't see anything obviously wrong. Object library is selected, field names etc. all correct - can you help?

    Merry Xmas

    Ina

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

    Re: VBA (2000)

    I think we'd have to see (a stripped down copy of) the database. See <post#=401925>post 401925</post#> for instructions.

  8. #8
    Star Lounger
    Join Date
    Jul 2006
    Posts
    96
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA (2000)

    Happy new Year Hans. Please find the table and the module you suggested - as stated, this is giving a syntax error return. Kind regards.

    Ina

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

    Re: VBA (2000)

    Happy New Year to you too.

    In the line
    <code>
    strSQL = "UPDATE SET Marked = FALSE"
    </code>
    not far from the beginning of the code, you have forgotten to specify the table name. It should be
    <code>
    strSQL = "UPDATE tblNormalisedMark SET Marked = FALSE"
    </code>
    Further on in the code, you have tblNormalizedMark twice, this should be changed to tblNormalisedMark.

    In your table, ID is a text field, so you must enclose values in quotes: change
    <code>
    Set rst2 = dbs.OpenRecordset("SELECT * FROM tblNormalisedMark " & _
    "WHERE ID = " & rst1!ID & " ORDER BY Period", dbOpenDynaset)
    </code>
    to
    <code>
    Set rst2 = dbs.OpenRecordset("SELECT * FROM tblNormalisedMark " & _
    "WHERE ID = " & Chr(34) & rst1!ID & Chr(34) & " ORDER BY Period", dbOpenDynaset)
    </code>
    Chr(34) is the double quote character ".

Posting Permissions

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