Previous Record in Access

[rwn]

I have this formula that works great in Crystal and I need to convert (Previous) to Access.

if previous({Job_Operation.Status}) = "C" and {Job_Operation.Inside_Oper} = NO
then "Needs PO"

 

[dhookom]

Can you tell us about your table structure and what the Crystal "Previous" actually calculates?

Duane
Hook'D on Access
MS Access MVP

 

[rwn]

I can, and it is in the formula in the post and here to again. Here you can see the table and the fields.

............if previous({Job_Operation.Status}) = "C" and {Job_Operation.Inside_Oper} = NO then "Needs PO"..............

What I need is how do you write the expression Previous in access for the ?({Job_Operation.Status})?

 

[dhookom]

I read your formula. You didn't need to post the same expression again.

Does "previous" refer to the previous record that most recently printed in the report? Or, is there some other meaning?

There is nothing built into an Access report that references the most previously printed record (if that is what you are looking for). I expect you could create a variable in the report's code that can store the value as it is printed. When you format/print the next record, you can reference the value of the variable and then set it.

If you mean the previous record, you should also be able to add this value to the record source of the report. This would depend on the tables and fields and sort order of the record source.

Duane
Hook'D on Access
MS Access MVP

 

[boblarson]

Where did Crystal get the data? If from an Access database table then there MUST have been some sort of QUERY with a sort order to enforce an order to it or else you are subject to randomness. I say that because Access doesn't store the data in any particular order within its structure. It can APPEAR like it is ordered but it really isn't and you have to apply order to it in order to be consistent and know exactly what the previous record was.

So, you need an ordered set of records first. Then, you can use something like this:

http://allenbrowne.com/subquery-01.html#AnotherRecord

to get the previous record.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[rwn]

I see the confusion. Yes, crystal does have the ability to look at the "previous record" and perform and action.

So I thought that "Previous Record" in some sort could be used in Access.

Thanks.

 

[boblarson]

Yes, crystal does have the ability to look at the "previous record" and perform and action.

But again I ask - was it from Access that you were getting the data for Crystal? If so, and you were just using the table then you were at high risk of it not returning the proper information because Access does not store data in the tables in a first in, first out type of format. You have to apply an EXPLICIT order to it via a QUERY in order to guarantee that order (and you have to either use a date/time stamp or some other numbering method - and autonumber is NOT a good way to number them either as that isn't necessarily going to be sequential).



Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[rwn]

My formula was from Crystal and trying to use in Access.
I will try another option as you indicated.

Thank you!

 

[BNPMike]

Access and SQL are relational. Relations are unordered sets. That's intentional to stop order hiding meaning - you must be able to retrieve meaning from data values, and nothing else. That's why SQL doesn't refer to 'previous'

 

[danvlas]

I'm almost positive that Duane spotted the issue, so I'd suggest focusing on his questions...




Daniel Vlas
Systems Consultant

 

[rwn]

I do agree with Duane's suggestion too. But am unsure how to create a variable to refer to the previous record that most recently printed in the report. So that it could store the value as it is printed and then format/print the next record, you can reference the value of the variable and then set it.

Any examples or suggestions would be great!

 

[dhookom]

rwn,
Can you simply provide the record source of the report like:

SELECT Job_Operation.Status, Job_Operation.Inside_Oper, FieldX, FieldY
FROM Job_Operation

What describes the order of the records in the report?Sample records with the desired calculated value and what determines this value.

Duane
Hook'D on Access
MS Access MVP

 

[danvlas]

Try something like:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
'Dim a static variable so that it keeps its value between calls. It will be a zero-length string for the first record displayed in the report.

Static PrevValue As String

'Do whatever you want with that value. I just displayed it in an unbound textbox in the Detail section, but almost anything is possible.

Me.txtTest = PrevValue

'Store the current value in the variable so that next time a detail prints, this value is remembered.
PrevValue = Me.NameOfTheBoxContainingTheValue

'Good Luck
End Sub

Now this being sorted out (you control the 'previous report record'), you must make sure that the order of your records is the right one, otherwise you'll get very funny results:

1. Order your query as necessary
2. Make sure the 'Order By' property of the report does not conflict with the query order. Report's property takes precedence. Better not to set it at all in this case.
3. Make sure the grouping in the report does not conflict with the query order. In this case, check if you need to reset the variable for each group start.


HTH



Daniel Vlas
Systems Consultant

 

[dhookom]

The On Print event of your report section is too late to change the properties of any control in your report (I think).

I never set the sort order in the query when it is the Record Source of a report. I always use the sorting and grouping in the report design.

I still prefer to resolve this in the query record source.

Duane
Hook'D on Access
MS Access MVP

 

[danvlas]

1. No, it's not too late. I tested it. I also tried the On Format event, but it populated even the box in the first detail with the value of the last detail in the report. That's why I moved the code to On Print.

2. As I said, I referred to the current case. I never say never

3. So do I, however there are times when the query becomes too complicated or very slow because of grouping and repeateadly querying the same dataset. All for just a simple inspection of a value printed above the current record in the report.

In my opinion, it all depends on what's needed in every particular situation.


Daniel Vlas
Systems Consultant

 

[dhookom]

I stand corrected regarding the On Print event being too late. Maybe it was other properties that can't be set in the On Print event.

Duane
Hook'D on Access
MS Access MVP