Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Built-in Functions _ Using Dlookup

Posted by: Questvba Oct 30 2019, 06:18 AM

Hello UtterAccess,
This is my very first topic and I hope to respect all the rules. Excuse me for my English which is not very good.
Here is certainly the stupid question of the day for you....

I have two Tables: PUTIN and DATA.

Table 1
Name of table
= PUTIN
Field 1 = PERSONS
Field 2 = DATE


Table 2
Name of Table
= DATA
Field 1 = MOIS
Field 2 = AMOUNT


My problem: how, for each person (PERSONS) to recover the amount due (AMOUNT) according to the calculation date (DATE) in relation to the time of indexation (MOIS). I tried it with Dlookup but I can't get anywhere. Either
it's not the right solution or I use it wrong.

If a good soul could put me on the track....

In XL, I would have done:

CODE
=INDEX(DATA[AMOUNT];EQUIV([@DATE];DATA[MONTH];1))



Excel is fine, but now I want it in Access.

Posted by: June7 Oct 30 2019, 02:48 PM

You want Amount from record where MOIS is closest to Date but not later? DLookup alone will not do that.

DLookup("Amount","Data","MOIS=DMax('MOIS','Data','Mois<=#" & [Date] & "#)'")

Date is a reserved word and should not use reserved words as names for anything.

Posted by: ipisors12 Oct 30 2019, 05:39 PM

You can look into opening a recordset

dim rs as dao.recordset, db as dao.database
set db=currentdb
set rs = db.openrecordset("select something with ORDER BY")

do until rs.eof=true
rs.movenext
if rs.fields(field)=something
loop


...you get the idea. maybe?

Posted by: Questvba Nov 4 2019, 07:28 AM

Here is the solution I adapted to my case.
Thank you very much for putting in orbit.

CODE
SELECT [PUTIN]![PERSONS] AS Expr1, PUTIN![MONTH_P] AS Expr2, format(DLookUp("AMOUNT","DATA","[MONTH_D] <= #" & Format([MONTH_P],"mm/dd/yyyy") & "#"),"0.00") AS Expr3
FROM PUTIN;



How do I close the toptic?