Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Greater Date

  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good Day everyone!

    I am having an issue with code I am running.

    Do While Not rst1.EOF
    ' Open records for this ID, sorted by Date.
    strSQL2 = "SELECT * FROM TBL1 WHERE ID=" & rst1!ID & " ORDER BY Date"
    Set rst2 = dbs.OpenRecordset(strSQL2, dbOpenDynaset)

    It use to work for another report. However for current one I need to start from the greater date and I can't find way to set it up.
    All of my records are having multiply rows.
    I need to start from Max Date row.
    I had tried ORDER BY Date desc but same error - no record available...
    Can you help? Thanks

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MS_fan' post='770528' date='14-Apr-2009 22:54'][/quote]
    Date is a reserved word in Access. You shouldn't name a field Date. Rename your field to something else and see if that solve the problem. If not, post back.
    Francois

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Nope! It says 'no current record'.
    I thought making it sort desc will not solve the issue because I will be still going up where there is no record.
    Maybe I need to change dbOpenForwardOnly to something?

    Thanks

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MS_fan' post='770635' date='15-Apr-2009 17:27']Nope! It says 'no current record'.[/quote]
    The field ID is it Text Or Numeric ?
    If it is text you should use:
    Code:
    strSQL2 = "SELECT * FROM TBL1 WHERE ID='" & rst1!ID & "' ORDER BY Date"
    Francois

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ID is numeric.

    Now it says 'too few parameters - need 1' at the
    Set rst2 = dbs.OpenRecordset(strSQL2, dbOpenDynaset)
    line.


    Thanks

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MS_fan' post='770653' date='15-Apr-2009 19:13']ID is numeric.[/quote]
    If it is numeric then your line was right.
    Code:
    strSQL2 = "SELECT * FROM TBL1 WHERE ID=" & rst1!ID & " ORDER BY Date"
    .
    Can you post a stripped, compacted and zipped version of your DB, so we can search for the error ?
    Francois

  7. #7
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I hoped you would ask :-)

    It seems I can't upload 220KB database. Is there anything wrong with an upload feature?
    Will try again. Thanks

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MS_fan' post='770667' date='15-Apr-2009 20:11']I hoped you would ask :-)[/quote]
    There is no attachment.
    Francois

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MS_fan' post='770667' date='15-Apr-2009 20:11']I hoped you would ask :-)

    It seems I can't upload 220KB database. Is there anything wrong with an upload feature?
    Will try again. Thanks[/quote]
    No problem with 220KB db. You have to browse to your file, then click UPLOAD. That's all.
    Francois

  10. #10
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Got it - Zips only!!!!!
    Attached Files Attached Files

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MS_fan' post='770672' date='15-Apr-2009 20:22']Got it - Zips only!!!!![/quote]
    1 you have modify Date to Dates in the table Grades, but not in your code.
    2 you have comment out the line with rst2.findfirst and not those that follow with If rst2.NoMatch.
    3 In the Insert lines you have value rst2!Action that don't exist.
    4 Did you remove the field Action it the table Grades
    Francois

  12. #12
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    1 you have modify Date to Dates in the table Grades, but not in your code.
    Done
    2 you have comment out the line with rst2.findfirst and not those that follow with If rst2.NoMatch.
    Done
    3 In the Insert lines you have value rst2!Action that don't exist.
    What to do to correct it?
    4 Did you remove the field Action it the table Grades
    I did.

    Still get 'too few parameters' though...
    Thanks

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MS_fan' post='770688' date='15-Apr-2009 21:18']What to do to correct it?[/quote]
    Do you need it ? if no, then remove it, if yes, thenyou have to create an Action field in the table Grades.

    Can you explain what the code should accomplish ? I can't find the logic.
    Francois

  14. #14
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I got it - removed!
    However 'too few parameters' occures far before that at second line of below:

    strSQL2 = "SELECT * FROM GRADES WHERE ID=" & rst1!ID & " ORDER BY Date"
    Set rst2 = dbs.OpenRecordset(strSQL2, dbOpenDynaset)

    The Logic:
    Find Max Date per ID and check the SalGrade.
    Find Previous to the MaxDate and check IF SalGrade is different.
    If so - write into table.

    It use to be
    Find Action PRO per ID and check the SalGrade etc...

    Thanks for helping me. I am miffed because it works fine for the old Grade table with only difference that it has Action field.
    What is wrong with my new one?
    Maybe when it was looking for Action=PRO - it was looking at the single record, now it is grabbing whole bunch and says 'too many - need 1' - so this is why I want to have Max Date but it is not reading it right.

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MS_fan' post='770699' date='15-Apr-2009 21:49']However 'too few parameters' occures far before that at second line of below:

    strSQL2 = "SELECT * FROM GRADES WHERE ID=" & rst1!ID & " ORDER BY Date"
    Set rst2 = dbs.OpenRecordset(strSQL2, dbOpenDynaset)[/quote]
    too few parameters you have because of the ORDER BY Date. It should be ORDER BY DATES
    Francois

Page 1 of 2 12 LastLast

Posting Permissions

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