DLOOKUP FOR NEXT

S

shar

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)
 
J

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
 
M

Marshall Barton

shar said:
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)
Click to expand...


Try this:

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

shar

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.

Marshall Barton said:
shar said:
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)
Click to expand...


Try this:

NEXT: DMin("[SEQ]","T_DATA","[BASE]= " & [BASE]
And "[LOT] > " & [LOT])
Click to expand...
 
M

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.

Marshall Barton said:
shar said:
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)
Click to expand...


Try this:

NEXT: DMin("[SEQ]","T_DATA","[BASE]= " & [BASE]
And "[LOT] > " & [LOT])
Click to expand...
Click to expand...
 
S

shar

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.

Marshall Barton said:
"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.

Marshall Barton said:
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])
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

shar said:
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;
Click to expand...


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
 
S

shar

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.

Marshall Barton said:
shar said:
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;
Click to expand...


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
Click to expand...
 
M

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.

Marshall Barton said:
shar said:
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;
Click to expand...


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
Click to expand...
Click to expand...
 
S

shar

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

Marshall Barton said:
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.

Marshall Barton said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IF then SUM 5
Help!! find two number recurring numbers in row?? 1
Lost in time! 5
query to sort data 3
Can this be done with access 2007 2
Help need to complete query 0
sum of row when duplicate entries of date, difference between 2 ta 2
Date query problem 6

Top