DLOOKUP FOR NEXT

[Guest]

My intention is to extract from my table; base, lot, seq and next seq from
the sample data below. I've included my DLOOKUP query below, which results
in no value from the DLookup. What am I missing? TIA

BASE LOT SEQ
31892 10 10
31892 10 25
31892 10 30
31892 10 35
31892 10 40
31892 10 45
31892 11 10
31892 11 25
31892 11 30
31892 11 35
31892 11 40
31892 11 45

NEXT: DLookUp("[SEQ]","T_DATA","[BASE]= " & [BASE] And "[LOT]= " & [LOT]+1)

 

[John Spencer]

Perhaps the following will work, however I am not sure that will give you
what you want

NEXT: DLookUp("[SEQ]","T_DATA","[BASE]= " & [BASE] & " And [LOT]= " &
[LOT]+1)

given that
Base = 31892 and Lot = 10
this should return 10 from the row 31892 11 10 in your sample data
Base =31892 Lot=11 Seq=10

 

[Marshall Barton]

My intention is to extract from my table; base, lot, seq and next seq from
the sample data below. I've included my DLOOKUP query below, which results
in no value from the DLookup. What am I missing? TIA

BASE LOT SEQ
31892 10 10
31892 10 25
31892 10 30
31892 10 35
31892 10 40
31892 10 45
31892 11 10
31892 11 25
31892 11 30
31892 11 35
31892 11 40
31892 11 45

NEXT: DLookUp("[SEQ]","T_DATA","[BASE]= " & [BASE] And "[LOT]= " & [LOT]+1)

Try this:

NEXT: DMin("[SEQ]","T_DATA","[BASE]= " & [BASE]
And "[LOT] > " & [LOT])

 

[Guest]

Thank you for your help, I could not get either suggestion to work, however
after playing around a little I added a primary key to the table and changed
the dlookup to the following.

NEXT: DLookUp("[SEQ]","T_DATA","[REC#]= " & [REC#]+1)

It works to an extent, I'm hoping for sugguestions with the following:

The last occurance within the BASE and LOT family is pulling the NEXT SEQ
from the next set of BASE and LOT family. ex:

BASE LOT SEQ NEXT
31892 10 10 25
31892 10 25 30
31892 10 30 35
31892 10 35 40
31892 10 40 45
31892 10 45 10

31892 11 10 25
31892 11 25 30
31892 11 30 35
31892 11 35 40
31892 11 40 45
31892 11 45 10

What criteria do I need to add to pull in a null value when a change in BASE
and LOT?

Thank you.

My intention is to extract from my table; base, lot, seq and next seq from
the sample data below. I've included my DLOOKUP query below, which results
in no value from the DLookup. What am I missing? TIA

BASE LOT SEQ
31892 10 10
31892 10 25
31892 10 30
31892 10 35
31892 10 40
31892 10 45
31892 11 10
31892 11 25
31892 11 30
31892 11 35
31892 11 40
31892 11 45

NEXT: DLookUp("[SEQ]","T_DATA","[BASE]= " & [BASE] And "[LOT]= " & [LOT]+1)

Try this:

NEXT: DMin("[SEQ]","T_DATA","[BASE]= " & [BASE]
And "[LOT] > " & [LOT])

 

[Marshall Barton]

"could not get either suggestion to work" really doesn't
provide much in the way of clues to work with. You should
tell us what you tried and what actually happened.

The DMin function I posted will return Null for the "last"
record for each base. I don't know what didn't work, maybe
I need some more details, maybe you made a mistake in using
it ot maybe something else, but I do know that the idea is
valid.

Your DLookup based on an autonumber field will not do what
you want. Don't waste your time trying to make it work.
--
Marsh
MVP [MS Access]

Thank you for your help, I could not get either suggestion to work, however
after playing around a little I added a primary key to the table and changed
the dlookup to the following.

NEXT: DLookUp("[SEQ]","T_DATA","[REC#]= " & [REC#]+1)

It works to an extent, I'm hoping for sugguestions with the following:

The last occurance within the BASE and LOT family is pulling the NEXT SEQ
from the next set of BASE and LOT family. ex:

BASE LOT SEQ NEXT
31892 10 10 25
31892 10 25 30
31892 10 30 35
31892 10 35 40
31892 10 40 45
31892 10 45 10

31892 11 10 25
31892 11 25 30
31892 11 30 35
31892 11 35 40
31892 11 40 45
31892 11 45 10

What criteria do I need to add to pull in a null value when a change in BASE
and LOT?

Thank you.

My intention is to extract from my table; base, lot, seq and next seq from
the sample data below. I've included my DLOOKUP query below, which results
in no value from the DLookup. What am I missing? TIA

BASE LOT SEQ
31892 10 10
31892 10 25
31892 10 30
31892 10 35
31892 10 40
31892 10 45
31892 11 10
31892 11 25
31892 11 30
31892 11 35
31892 11 40
31892 11 45

NEXT: DLookUp("[SEQ]","T_DATA","[BASE]= " & [BASE] And "[LOT]= " & [LOT]+1)

Try this:

NEXT: DMin("[SEQ]","T_DATA","[BASE]= " & [BASE]
And "[LOT] > " & [LOT])

 

[Guest]

This is the output I get with the DMin function:

BASE LOT SEQ NEXT
31892 10 10 10
31892 10 25 10
31892 10 30 10
31892 10 35 10
31892 10 40 10
31892 10 45 10
31892 11 10 10
31892 11 25 10
31892 11 30 10
31892 11 35 10
31892 11 40 10
31892 11 45 10

Here is the SQL query:
SELECT T_DATA.BASE, T_DATA.LOT, T_DATA.SEQ, DMin("[SEQ]","T_DATA","[BASE]= "
& [BASE] And "[LOT] > " & [LOT]) AS [NEXT]
FROM T_DATA;

Thank you, I appreciate your help with this.

"could not get either suggestion to work" really doesn't
provide much in the way of clues to work with. You should
tell us what you tried and what actually happened.

The DMin function I posted will return Null for the "last"
record for each base. I don't know what didn't work, maybe
I need some more details, maybe you made a mistake in using
it ot maybe something else, but I do know that the idea is
valid.

Your DLookup based on an autonumber field will not do what
you want. Don't waste your time trying to make it work.
--
Marsh
MVP [MS Access]

Thank you for your help, I could not get either suggestion to work, however
after playing around a little I added a primary key to the table and changed
the dlookup to the following.

NEXT: DLookUp("[SEQ]","T_DATA","[REC#]= " & [REC#]+1)

It works to an extent, I'm hoping for sugguestions with the following:

The last occurance within the BASE and LOT family is pulling the NEXT SEQ
from the next set of BASE and LOT family. ex:

BASE LOT SEQ NEXT
31892 10 10 25
31892 10 25 30
31892 10 30 35
31892 10 35 40
31892 10 40 45
31892 10 45 10

31892 11 10 25
31892 11 25 30
31892 11 30 35
31892 11 35 40
31892 11 40 45
31892 11 45 10

What criteria do I need to add to pull in a null value when a change in BASE
and LOT?

Thank you.

shar wrote:

My intention is to extract from my table; base, lot, seq and next seq from
the sample data below. I've included my DLOOKUP query below, which results
in no value from the DLookup. What am I missing? TIA

BASE LOT SEQ
31892 10 10
31892 10 25
31892 10 30
31892 10 35
31892 10 40
31892 10 45
31892 11 10
31892 11 25
31892 11 30
31892 11 35
31892 11 40
31892 11 45

NEXT: DLookUp("[SEQ]","T_DATA","[BASE]= " & [BASE] And "[LOT]= " & [LOT]+1)


Try this:

NEXT: DMin("[SEQ]","T_DATA","[BASE]= " & [BASE]
And "[LOT] > " & [LOT])

 

[Marshall Barton]

This is the output I get with the DMin function:

BASE LOT SEQ NEXT
31892 10 10 10
31892 10 25 10
31892 10 30 10
31892 10 35 10
31892 10 40 10
31892 10 45 10
31892 11 10 10
31892 11 25 10
31892 11 30 10
31892 11 35 10
31892 11 40 10
31892 11 45 10

Here is the SQL query:
SELECT T_DATA.BASE, T_DATA.LOT, T_DATA.SEQ, DMin("[SEQ]","T_DATA","[BASE]= "
& [BASE] And "[LOT] > " & [LOT]) AS [NEXT]
FROM T_DATA;

I seem to have missed part of the situation and gotten mixed
up over the Lot and Seq fields. Let's try this:

SELECT BASE, LOT, SEQ,
DMin("SEQ", "T_DATA", "BASE=" & BASE &
" And LOT=" & LOT & " And Seq>" & Seq) As NEXT
FROM T_DATA

 

[Guest]

Thank you for your help Marshall, I paste your query in SQL View, removed the
carriage returns, the query follows;

SELECT BASE, LOT, SEQ,
DMin("SEQ", "T_DATA", "BASE=" & BASE &" And LOT=" & LOT & " And Seq>" & Seq)
As NEXT
FROM T_DATA

and come up with Data Type Mismatch and the following: The fields are text
data type.

BASE LOT SEQ NEXT
31892 10 10 #Error
31892 10 25 #Error
31892 10 30 #Error
31892 10 35 #Error
31892 10 40 #Error
31892 10 45 #Error
31892 11 10 #Error
31892 11 25 #Error
31892 11 30 #Error
31892 11 35 #Error
31892 11 40 #Error
31892 11 45 #Error

I'm grateful for your help.

This is the output I get with the DMin function:

BASE LOT SEQ NEXT
31892 10 10 10
31892 10 25 10
31892 10 30 10
31892 10 35 10
31892 10 40 10
31892 10 45 10
31892 11 10 10
31892 11 25 10
31892 11 30 10
31892 11 35 10
31892 11 40 10
31892 11 45 10

Here is the SQL query:
SELECT T_DATA.BASE, T_DATA.LOT, T_DATA.SEQ, DMin("[SEQ]","T_DATA","[BASE]= "
& [BASE] And "[LOT] > " & [LOT]) AS [NEXT]
FROM T_DATA;

I seem to have missed part of the situation and gotten mixed
up over the Lot and Seq fields. Let's try this:

SELECT BASE, LOT, SEQ,
DMin("SEQ", "T_DATA", "BASE=" & BASE &
" And LOT=" & LOT & " And Seq>" & Seq) As NEXT
FROM T_DATA

 

[Marshall Barton]

Text? All of the fields are Text fields?? Well, that does
make a difference in that the values need to be enclosed in
quotes:

If the Seq value is always two digits, you can get away
with:
DMin("SEQ", "T_DATA", "BASE='" & BASE &"' And LOT='" & LOT &
"' And Seq>'" & Seq & "'")

But if the Seq field might be any old number, then use:
DMin("SEQ", "T_DATA", "BASE='" & BASE &"' And LOT='" & LOT &
"' And Val(Seq) > " & Val(Seq))

But, even this will not work if the Seq field might contain
a non-numeric character.
--
Marsh
MVP [MS Access]

Thank you for your help Marshall, I paste your query in SQL View, removed the
carriage returns, the query follows;

SELECT BASE, LOT, SEQ,
DMin("SEQ", "T_DATA", "BASE=" & BASE &" And LOT=" & LOT & " And Seq>" & Seq)
As NEXT
FROM T_DATA

and come up with Data Type Mismatch and the following: The fields are text
data type.

BASE LOT SEQ NEXT
31892 10 10 #Error
31892 10 25 #Error
31892 10 30 #Error
31892 10 35 #Error
31892 10 40 #Error
31892 10 45 #Error
31892 11 10 #Error
31892 11 25 #Error
31892 11 30 #Error
31892 11 35 #Error
31892 11 40 #Error
31892 11 45 #Error

I'm grateful for your help.

This is the output I get with the DMin function:

BASE LOT SEQ NEXT
31892 10 10 10
31892 10 25 10
31892 10 30 10
31892 10 35 10
31892 10 40 10
31892 10 45 10
31892 11 10 10
31892 11 25 10
31892 11 30 10
31892 11 35 10
31892 11 40 10
31892 11 45 10

Here is the SQL query:
SELECT T_DATA.BASE, T_DATA.LOT, T_DATA.SEQ, DMin("[SEQ]","T_DATA","[BASE]= "
& [BASE] And "[LOT] > " & [LOT]) AS [NEXT]
FROM T_DATA;

I seem to have missed part of the situation and gotten mixed
up over the Lot and Seq fields. Let's try this:

SELECT BASE, LOT, SEQ,
DMin("SEQ", "T_DATA", "BASE=" & BASE &
" And LOT=" & LOT & " And Seq>" & Seq) As NEXT
FROM T_DATA

 

[Guest]

Thank you I appreciate your help, the syntax worked and gave me exactly what
I was looking for.

Text? All of the fields are Text fields?? Well, that does
make a difference in that the values need to be enclosed in
quotes:

If the Seq value is always two digits, you can get away
with:
DMin("SEQ", "T_DATA", "BASE='" & BASE &"' And LOT='" & LOT &
"' And Seq>'" & Seq & "'")

But if the Seq field might be any old number, then use:
DMin("SEQ", "T_DATA", "BASE='" & BASE &"' And LOT='" & LOT &
"' And Val(Seq) > " & Val(Seq))

But, even this will not work if the Seq field might contain
a non-numeric character.
--
Marsh
MVP [MS Access]

Thank you for your help Marshall, I paste your query in SQL View, removed the
carriage returns, the query follows;

SELECT BASE, LOT, SEQ,
DMin("SEQ", "T_DATA", "BASE=" & BASE &" And LOT=" & LOT & " And Seq>" & Seq)
As NEXT
FROM T_DATA

and come up with Data Type Mismatch and the following: The fields are text
data type.

BASE LOT SEQ NEXT
31892 10 10 #Error
31892 10 25 #Error
31892 10 30 #Error
31892 10 35 #Error
31892 10 40 #Error
31892 10 45 #Error
31892 11 10 #Error
31892 11 25 #Error
31892 11 30 #Error
31892 11 35 #Error
31892 11 40 #Error
31892 11 45 #Error

I'm grateful for your help.

shar wrote:

This is the output I get with the DMin function:

BASE LOT SEQ NEXT
31892 10 10 10
31892 10 25 10
31892 10 30 10
31892 10 35 10
31892 10 40 10
31892 10 45 10
31892 11 10 10
31892 11 25 10
31892 11 30 10
31892 11 35 10
31892 11 40 10
31892 11 45 10

Here is the SQL query:
SELECT T_DATA.BASE, T_DATA.LOT, T_DATA.SEQ, DMin("[SEQ]","T_DATA","[BASE]= "
& [BASE] And "[LOT] > " & [LOT]) AS [NEXT]
FROM T_DATA;


I seem to have missed part of the situation and gotten mixed
up over the Lot and Seq fields. Let's try this:

SELECT BASE, LOT, SEQ,
DMin("SEQ", "T_DATA", "BASE=" & BASE &
" And LOT=" & LOT & " And Seq>" & Seq) As NEXT
FROM T_DATA