Use DLookup to identify a certain Table/Records Field value?

English
#1

My system info is at the bottom.

I have a form which shows records ‘to date’ and I want to add that date value to a field, so that I can have a line like “Total Spend, to DD/MM/YYYY for the Year” at the top of the form. The actual date value (DD/MM/YYYY) will always be in the Date field in the last record of table tblSpend and that would be shown in a discrete field on the form (the text on either side being label names). I think a Dlookup would work. The Base guide doesn’t give much help with Dlookup, so is this possible and where could I put the DLookup function? BTW I can’t have actual dates showing in the form (or in the query supplying it) because it shows totals for the year. Thanks.

Version: 7.5.5.2 / LibreOffice Community
Build: caf8fe7424262805f223b9a233
Environment: CPU Threads: 4;OS: Windows 10.0 Build 19405
User Interface: UI render: Skia/Raster; VCL:win
Locale: en.GB (en_GB); UI: en-GB
Misc Calc: threaded
Database HSQLDB Embedded

#2

If I’m right DLOOKUP() is included in the extension Access2Base.

#3

Thanks. I’ve had a look at Extensions online and my Base version should have it pre installed. I don’t have any knowledge of extensions, so I can’t see if it’s there and how to use it anyway! If I look at Tools/Extension manager, it’s not listed. I also can’t find any help with using extensions, never mind the syntax for the operation I need carried out. Actually, never mind. I created a simple query: SELECT MAX( “Date” ) FROM “tblSpend” and this gives the value I need and I can call this up in my forms field. Seems a bit clunky though.

#4

That’s the way I use, too. And the advantage is: Sub-SELECTS are standard SQL and therefore work nearly everywhere.

#5

Hmmm. I spoke too soon. I can’t seem to find a way to call this sql up in a text field (or label field). Any new text field I introduce on my form sees only those fields supplied by the forms query. Maybe it’s too late for me. I’ll try again tomorrow - I’ll look out for any further help then. Perhaps the phrase Sub-SELECT is the key here - I have no knowledge of that. Thanks.

#6

A query will only show it’s calculated content when the data of the row have been saved to the database. If I understood right: You will see the result of the saved content while you want to input content to a new row.
You could do this with a tablecontrol in the form. You will see more than one row there.
You could also do this with separate forms - one form for input new data, one form for the query, which shows the content of the saved data.

#7

As far as I remember, DLookup does something analog to spreadsheet function LOOKUP
Date | Value
2023-10-01 1
2023-10-08 2
2023-10-15 3
2023-10-22 4

DLOOKUP(‘2023-10-13’, “Date”, “Value”) returns 2 because the Oct 13th falls into the category of Oct 8th. This is analog to
=LOOKUP(DATE(2023;10;13);$A$2:$A$5;$B$2:$B5).
The database engine looks up in the sorted date vector. On the sheet you have to make sure that the dates are sorted.

If this lookup on a sorted vector is really what you need, a query can generate a sorted row set which then can be used in a spreadsheet. Spreadsheet cells can be linked to Base queries.
Some tricky SQL code might be possible to select the smallest value from the date vector which is greater than or equal to the search value plus the related column values, thus substituting the missing DLookup function.

#8

Thanks for all the suggestions here. I sorted my ‘problem’ using good old queries.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.