My Assistant
![]()
Custom Search
|
![]() ![]() |
![]() |
![]() 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 ![]() 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. This post has been edited by Questvba: Oct 30 2019, 06:53 AM |
![]() Post#2 | |
Posts: 1,008 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 |
![]() 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? |
![]() 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 |
![]()
Custom Search
|
![]() | Search Top Lo-Fi | 8th December 2019 - 01:18 PM |