Looking up a previous record based on criteria

[CindyR]

Hi everyone,

I have a textbox in my form that needs to display the value of another
textbox on the form, but from the previous record, given two criteria:

1. that the value of "ModNumber" (on the current record) is > 1 and
2. that the value of "TaskName" on the current record matches the "TaskName"
of the previous record


More Details:

My database contains task orders, and each task order can have many Mods.
This question was about the ModForm, which updates the ModTable. TaskName is
a foreign key from the TaskTable (no two TaskNames will be identical), and
ModAutoNumber is the primary key of the ModTable. ModNumber is a
user-defined field that will have integer values > 0.

My rationale for the two criteria: (1)if ModNumber = 1, then there is no
previous Mod, and therefore no field needs to be referenced; (2)If TaskName
doesn't match
the previous record's TaskName, then nothing should be looked up because the
Mods are for different tasks.

Thank you so much!

Cindy

 

[KARL DEWEY]

Try this --
SELECT YourTable.*, IIF(Nz(SELECT T.[SomeTextBox] FROM YourTable AS T WHERE
(T.ModNumber)+1 = YourTable.ModNumber AND T.TaskName =
YourTable.TaskName),"First Record") AS Last_Record
FROM YourTable;

 

[CindyR]

Karl,

Thank you for your reply!

Unfortunately, I got a "subquery syntax is incorrect" error when trying to
use your suggestion. I put it in the following location: OutputTextbox >
right-click > build event (in expression builder). I also substituted
ModTable for "YourTable", and T.[ModLaborFinal] (the texbox to be looked up)
for "T.[SomeTextbox]". Is there something I'm missing?

Also, the purpose of this is to make my ModForm cumulative--if the total of
a Task has been updated by a previous Mod for that task, then I would like
the current Mod to take the previous Mod's changes into account. So by
displaying the previous Mod's final total, I can then use this total with the
new Mod's "delta" amount to create a new final total. I would like the
TaskTable to still contain the original (before any Mods) Task total, however.

Is there a better way of doing this in a form?

Thanks,

Cindy

Try this --
SELECT YourTable.*, IIF(Nz(SELECT T.[SomeTextBox] FROM YourTable AS T WHERE
(T.ModNumber)+1 = YourTable.ModNumber AND T.TaskName =
YourTable.TaskName),"First Record") AS Last_Record
FROM YourTable;

--
Build a little, test a little.

Hi everyone,

I have a textbox in my form that needs to display the value of another
textbox on the form, but from the previous record, given two criteria:

1. that the value of "ModNumber" (on the current record) is > 1 and
2. that the value of "TaskName" on the current record matches the "TaskName"
of the previous record


More Details:

My database contains task orders, and each task order can have many Mods.
This question was about the ModForm, which updates the ModTable. TaskName is
a foreign key from the TaskTable (no two TaskNames will be identical), and
ModAutoNumber is the primary key of the ModTable. ModNumber is a
user-defined field that will have integer values > 0.

My rationale for the two criteria: (1)if ModNumber = 1, then there is no
previous Mod, and therefore no field needs to be referenced; (2)If TaskName
doesn't match
the previous record's TaskName, then nothing should be looked up because the
Mods are for different tasks.

Thank you so much!

Cindy