UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Dlookup, Access 2016    
 
   
Questvba
post Oct 30 2019, 06:18 AM
Post#1



Posts: 2
Joined: 30-October 19



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
Attached File  Image_2.png ( 7.11K )Number of downloads: 1


Table 2
Name of Table
= DATA
Field 1 = MOIS
Field 2 = AMOUNT
Attached File  Image_3.png ( 7.69K )Number of downloads: 1


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

Attached File  Image_5.png ( 15.8K )Number of downloads: 5


Excel is fine, but now I want it in Access.
This post has been edited by Questvba: Oct 30 2019, 06:53 AM
Go to the top of the page
 
June7
post Oct 30 2019, 02:48 PM
Post#2



Posts: 1,040
Joined: 25-January 16



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.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
ipisors12
post Oct 30 2019, 05:39 PM
Post#3



Posts: 10
Joined: 14-March 19



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?
Go to the top of the page
 
Questvba
post Nov 4 2019, 07:28 AM
Post#4



Posts: 2
Joined: 30-October 19



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?
This post has been edited by Questvba: Nov 4 2019, 07:31 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th December 2019 - 10:22 AM