Max row in VBA code

[teqtaq]

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

 

[ZmrAbdulla]

Date is a reserved word in Access. Change it to something meaningful. Or try using like[tt] ORDER BY [Date][/tt]



Zameer Abdulla

 

[teqtaq]

It didn't help because it is not how my dates are sorted.
It is because when it hits the date, reads it and wants to go the the previous date - there is none.

1/1/2003
1/1/2004
1/1/2005

If it steps on the first one - there is no previous one.

If I sort descending

1/1/2005
1/1/2004
1/1/2003

it will start at 1/1/2005 and there is no previous row as well.

Thanks

 

[teqtaq]

I was trying and trying and nothing works and all I need is to make sure that rst1!ID

strSQL2 = "SELECT * FROM TBL1 WHERE ID=" & rst1!ID & " ORDER BY Date"

is taken with a Max date.

Please, help

 

[dhookom]

This is the Reports forum yet your only reference to reports is your statement "It use to work for another report" which makes no sense to me.

Do you mind sharing some background on what you are attempting to do and why?

Duane
Hook'D on Access
MS Access MVP

 

[teqtaq]

Sorry for the confusion.

My code has a line
rst2.FindFirst "Action='PRO'"

For new Report I do not have Action field anymore.
Instead I need to have:
rst2.FindFirst "Max(date) in a field called 'Dates'"
and start from there.

I am pretty sure it will work.

Thanks a lot

__________________________________________________


Details on what I want to accomplish:

ID______Date________Scale
11____1/1/2002_______112
11____1/1/2003_______112
11____1/1/2006_______111

12____1/1/2002_______080
12____1/1/2004_______080
12____1/1/2008_______080

10____1/1/2000_______040
10____1/1/2001_______050
10____1/1/2008_______060



One ID has greater date with scale 111 and previous date with scale 112.
Meaning this employee was demoted to a lower scale .

Another ID has greater date AND previos date with scale 080 - we do not care about those.
There is no promotion - no demotion.

Third ID has greater date with scale 60 and previous date with scale 50 -
meaning this person was promoted.

I am looking for people with scale change but only ONCE.
ONLY greater date and previous date. When this is reached -the rest of the recordset can be neverminded.

Thanks