Next record

[Gespierde velopompe]

Good afternoon,

I have a querry which I use to look up several locations where parts are stored in a warehouse. Now what I'd like to do, is when I give in certain partnumber in the querry, I'd like him to look up the previous and next location/record of the table I'm getting the data out.

I've tried to do so via Dlook up, but I get no output or an error message. Can you guys give me a litlle help on this? For example, when I enter partnumber 2233384, I would like to have the querry give me in the column of nextvalue "95210607R0", being the next row in the "total" column.

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION></CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>LOC_ID_PART</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0></TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>1606386</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>2233384</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>2476502</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>2476502</TD></TR></TBODY><TFOOT></TFOOT></TABLE><TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION></CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>total</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0>44015897b0</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>58190008 </TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>06159407R0</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>95210607R0</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>95210607R0</TD></TR></TBODY><TFOOT></TFOOT></TABLE>
<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION></CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>nextvalue</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0>#Error</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>#Error</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>#Error</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>#Error</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>#Error</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

I use below code, which I based on an example, but doesn't seem to work...

nextvalue: DLookUp("[T_test2.total]","T_test2","[T_test2.total]=" & [T_test2.total]+1)

Thanks!

 

[xenou]

What's the relationship between the Part and the total? I can't tell from your post if you are saying they are part of the same record or just the record on the next row. There is (generally speaking) no such things as rows in databases, and (generally speaking) you cannot get what is in the next row based on that fact alone.

 

[Gespierde velopompe]

Xenou,

Part and total are indeed on the same row. Total is the location of that specific partnumber of the same row.

If it is not possible via the querry itself, might it be possible via VBA? First finding out what the rownumber from a specific part is and work my way from there?

thanks,
Frederik

 

[xenou]

This is difficult for me to answer. Data in databases is not assumed to be in any particular order, though it can be sorted by a query. You *should* be able to get your data without regard to where it is in the table, so I'm not sure I understand what you are doing. At worst, you may be confusing a database application with a spreadsheet application here, and they are entirely different things.

Can you give some complete examples of the data and what you want to do with it. The data in your first post is confusing because I don't see what is in a record or how it is related to the results you want.

 

[Gespierde velopompe]

Hi Xenou,

I'll try to show you with below example:

<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION></CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>ID</TH><TH bgColor=#c0c0c0 borderColor=#000000>Name</TH><TH bgColor=#c0c0c0 borderColor=#000000>Value</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0 align=right>1</TD><TD borderColor=#c0c0c0>A</TD><TD borderColor=#c0c0c0>440158b</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0>b</TD><TD borderColor=#c0c0c0>581900a</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0>c</TD><TD borderColor=#c0c0c0>9</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>4</TD><TD borderColor=#c0c0c0>d</TD><TD borderColor=#c0c0c0>5</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>5</TD><TD borderColor=#c0c0c0>e</TD><TD borderColor=#c0c0c0>17</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>6</TD><TD borderColor=#c0c0c0>f</TD><TD borderColor=#c0c0c0>4</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>7</TD><TD borderColor=#c0c0c0>g</TD><TD borderColor=#c0c0c0>8</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Ex.1: When I would run a querry where I would select on name and would want to look up the value of "B", it would give me "581900a". Is there a way for me to get the value of the part in the record above "B", in this case 440158b? So that it looks up what is positioned a record above the current record in the same column.

ex. 2 If I'd look up "E", it will provide me as value 17, but I'd like to get the record on top which is "5" as well.

If more querry's are needed no problem, doesn't have to be in the same querry.

Hope it is more clear now.

Regards,
Frederik..

 

[xenou]

There is no above or below in a database table. Why do you need to look above or below - that's not how databases work.