Avatar of Evert Jor
Evert Jor
Flag for Norway asked on

Query line counter

I need to use a value that I get from DLookUp("[ID_m]";"tbl_ID_MAKS_VARELNORDRE";"[ID_m]") (there is only ever one record in the table) and add +1 for each line in a query result. I have tried for a few hours now, so any help appreciated. Thanks in advance.

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
Microsoft AccessVBASQL

Avatar of undefined
Last Comment
Evert Jor

8/22/2022 - Mon
SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dale Fye

How are using this query?  Are you displaying this in a form, or a report?
Evert Jor

ASKER
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
SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Evert Jor

ASKER
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Jim Dettman (EE MVE)

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
Ryan Chong

any progress so far?
ASKER CERTIFIED SOLUTION
Evert Jor

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Evert Jor

ASKER
Thanks Ryan, your solution was the best suggestion - although I ended up with a workaround due to the missing ID-field in the original dataset.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.