In Query, how do I take previous field value and plug in current field 1

[Odis]

I've looked for this and can't find exactly what I want. I don't know VB and would like to try and do this with the Build Expression.

First record has value "CC234" in Field A and next record is Null in Field A. I want to take "CC234" from previous record Field A and plug it into the current record Field A.

BEFORE
Record 1 Field A = CC234
Record 2 Field A =

AFTER
Record 1 Field A = CC234
Record 2 Field A = CC234

Thanks!

 

[Golom]

Suprisingly, there's no such thing as a "previous record" in a relational database unless your SQL involves an ORDER BY clause that produces a unique ordering of the records.

When you create an SQL SELECT statement the records will of course, be returned with some apparent ordering but that ordering is not guaranteed. Thus, when looking at a specific record, there is no way to tell what the "previous record" was or the "next record" will be.

Do you have an "Order By" clause that will result in a unique ordering of the records?

Also ...

What would you want to happen if you had
[tt]
Record 1 Field A = CC234
Record 2 Field A = NULL
Record 3 Field A = NULL
Record 4 Field A = NULL
[/tt]
?

 

[Odis]

I do have an Order BY on a Seq number field and the records you show with null values is exactly what I am getting. I want the null fields to contain the previous field that had a value in it like CC234.

 

[BigRed1212]

Are there multiple values which field A could be depending on their "order" or is it the case that you simply want every record in the database to have CC234 as its value for Field A?

 

[PHV]

You may try this SQL:
UPDATE yourTable
SET [Field A] = DLookUp("[Field A]","yourTable","[Seq number]=" & DMax("[Seq number]","yourTable","[Seq number]<" & [Seq number] & " AND Not([Field A] Is Null)"))
WHERE [Field A] Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Odis]

I tried plugging my values into the code you provided, but I'm getting a syntax error about not enough operators. I checked my code very carefully (could still have missed something). Could you verify the code you gave and check it for amy syntax? I pretty much put my values in between the quotes.

 

[lespaul]

could you post what you tried?

Leslie

Have you met Hardy Heron?

 

[Odis]

Table Name = Financial Req Ver 2
Field Name = Consolidate Fin Req V2



UPDATE [Financial Req Ver 2]
SET [Consolidate Fin Req V2] = DLookUp([Consolidate Fin Req V2],[Financial Req Ver 2], (Seq= & DMax Seq,[Financial Req Ver 2], Seq< & Seq & AND Not([Consolidate Fin Req V2] Is Null)))
WHERE [Consolidate Fin Req V2] Is Null

 

[lespaul]

I'm pretty sure you need all the " that were in the original sample:

SET [Field A] = DLookUp("[Field A]","yourTable","[Seq number]=" & DMax("[Seq number]","yourTable","[Seq number]<" & [Seq number] & " AND Not([Field A] Is Null)"))
WHERE [Field A] Is Null

your code fixed would be:

UPDATE [Financial Req Ver 2]
SET [Consolidate Fin Req V2] = DLookUp("[Consolidate Fin Req V2]", "[Financial Req Ver 2]", "Seq=" & DMax ("Seq", "[Financial Req Ver 2]", "Seq<" & Seq & " AND Not([Consolidate Fin Req V2] Is Null)"))
WHERE [Consolidate Fin Req V2] Is Null

Leslie

Have you met Hardy Heron?

 

[Odis]

Thanks Sooo much! I cut and pasted your exact code and it worked perfectly! I need to do another similar lookup, but I'm going to give it a shot by tweaking your code.

Thanks again!