Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Query line counter
I need to use a value that I get from DLookUp("[ID_m]";"tbl_ID_M
The original query result may look like this:
Sample1 Result 234,4
Sample 15 Result 114,3
Sample 27 Result 43,2
I need the query to return, assuming the DLookup value is 200:
201 Sample1 Result 234,4
202 Sample 15 Result 114,3
203 Sample27 Result 43,2
Best regards,
Evert
The original query result may look like this:
Sample1 Result 234,4
Sample 15 Result 114,3
Sample 27 Result 43,2
I need the query to return, assuming the DLookup value is 200:
201 Sample1 Result 234,4
202 Sample 15 Result 114,3
203 Sample27 Result 43,2
Best regards,
Evert
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try something like this:
SELECT (select count(*) as idx from yourTable as b where a.ID >= b.ID) + nz(DLookUp("[ID_m]","tbl_ID_MAKS_VARELNORDRE","[ID_m]") ,0) as idx,
a.Feidl1, a.Field2
FROM yourTable as a
Ryan: Looked at your example. The problem is that there is no unique ID in the query (yourTable). That is the main issue. I need to get the value 200 into the query and add +1 for each line in the query result.
Best regards,
Evert
Best regards,
Evert
The problem is that there is no unique ID in the query (yourTable). That is the main issueyes that's the main issue. we need an unique identifier to determine the sequence of records in Access. without that field, we are difficult to create a sequence number for it.
OK. I will try to use your suggestion and some other scripts to get this to work. I will probably need until next week (Easter Holidays).
I will post the solution once I have it.
I will post the solution once I have it.
If you're making a single pass through the recordset you can do a line counter easily.
But if you're going to use this in a form where you're going to move back-and-forth through the records that then you cannot use that technique. However depending on the situation at temp table often work.
Jim
But if you're going to use this in a form where you're going to move back-and-forth through the records that then you cannot use that technique. However depending on the situation at temp table often work.
Jim
I solved the problem by:
- First using a create table query to create a table on the fly without adding any records.
- Then modifying that table in VBA to add a ID autonumber field.
- Then copy all the records into it, so each record would get the ID 1, 2, 3, 4 etc.
- Finally using a new query to display the correct line number as ID field + add it to a Dlookup value (for example 45900), so the resulting line numbers would be 45901, 45902, 45903, 45904.
Maybe not the neatest solution, but it works.
- First using a create table query to create a table on the fly without adding any records.
- Then modifying that table in VBA to add a ID autonumber field.
- Then copy all the records into it, so each record would get the ID 1, 2, 3, 4 etc.
- Finally using a new query to display the correct line number as ID field + add it to a Dlookup value (for example 45900), so the resulting line numbers would be 45901, 45902, 45903, 45904.
Maybe not the neatest solution, but it works.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial