Greater Date

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

Nope! It says ‘no current record’.

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"

Reply | Quote

ID is numeric.

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

Thanks

Reply | Quote

ID is numeric.

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 ?

Reply | Quote

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

Reply | Quote

I hoped you would ask

There is no attachment.

Reply | Quote

Got it – Zips only!!!!!

Reply | Quote

Got it – Zips only!!!!!

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

Reply | Quote

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

Reply | Quote

What to do to correct it?

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.

Reply | Quote

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.

Reply | Quote

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)

too few parameters you have because of the ORDER BY Date. It should be ORDER BY DATES

Reply | Quote

It is better now – however I can’t seem to make it work.

If Not rst2.BOF Then
rst2.MovePrevious

On MovePrevious – error no record.

Thanks

Reply | Quote

It is better now – however I can’t seem to make it work.

I add a line:
If rst2.BOF Then rst2.MoveNext
after :
rst2.MovePrevious

Does this do what you want ?

Reply | Quote

My assignment just had changed.

Now I am suppose to be reading each ID, Date and Grade.
When Grade has changed – write into table.

Thanks I will try and if I need help I will holler.

Reply | Quote

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

No problem with 220KB db. You have to browse to your file, then click UPLOAD. That’s all.

Reply | Quote