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    
post Oct 30 2019, 06:18 AM

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

Table 2
Name of Table
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:

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

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
post Oct 30 2019, 02:48 PM

Posts: 1,164
Joined: 25-January 16
From: The Great Land

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.
Go to the top of the page
post Oct 30 2019, 05:39 PM

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
if rs.fields(field)=something

...you get the idea. maybe?
Go to the top of the page
post Nov 4 2019, 07:28 AM

Posts: 2
Joined: 30-October 19

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

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

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    23rd January 2020 - 06:39 PM