Dlookup to return previous day closing (1 Viewer)

raziel3

Registered User.
Local time
Today, 16:33
Joined
Oct 5, 2017
Messages
273
Hello,
I have query with the following fields from the Data Table
DaysWork: CloseDate
Lane: Source
ClosingCash: Cash
PrevDate: Format(DateAdd('d',-1,[CloseDate]),"dd/mm/yyyy")
Opening: Dlookup("ClosingCash","Data","[Source]= '" & [Lane] & "'" AND [CloseDate] =#" & [PrevDate] & "#")

Lane/Source is a text field. The problem I am having is the Dlookup returns the previous day's Cash but in some of the fields. The fields with text like CashDrawer'1 (I did not label this) returns #Error and other fields remains blank. What am I doing wrong?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:33
Joined
Aug 30, 2003
Messages
36,118
Try

Dlookup("ClosingCash","Data","[Source]= " & Chr(34) & [Lane] & Chr(34) & " AND [CloseDate] =#" & [PrevDate] & "#")

though a query join would typically be used. Tricky with the previous date though, and it's dinner time here so I've got to go.
 

raziel3

Registered User.
Local time
Today, 16:33
Joined
Oct 5, 2017
Messages
273
I'm still getting #Error with the fields that have the "CashDrawer'1" text and now it not returning any values. Can you help me with a join query?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:33
Joined
Aug 30, 2003
Messages
36,118
Really? Can you attach the db here?
 

raziel3

Registered User.
Local time
Today, 16:33
Joined
Oct 5, 2017
Messages
273
Ok, I got it to work. What was happening, well at least what I think was the problem, I was working on the db on 2 different computers. One computer had the date format mm/dd/yyyy and the other dd/mm/yyyy so I was forcing [PrevDate] to try to get the ClosingCash for 1/8/2018 when the ClosingCash was being seen as 8/1/2018 so no ClosingCash was returned.

Also, I cannot use [OpeningCash] as part of a formula. For example, I want to create another field in the query Net: [ClosingCash]-[Opening]. I've attached for you to take a look. View attachment MyDB.zip

I already have a whole set of records in the dd/mm/yyyy format how can I make the query 'universal' so it doesn't matter what the system date format is?

I really want to explore the query joins you mentioned. I've heard that Dlookups can cause performance issues when your database begins to grow and I expect that a lot of records will be added in the future.
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:33
Joined
Aug 30, 2003
Messages
36,118
I was thinking the DLookup() was looking at a different table. Try this:

SELECT Data.CloseDate, Data.Source, Data.ClosingCash, (SELECT TOP 1 Dupe.ClosingCash FROM Data As Dupe WHERE Dupe.Source = Data.Source AND Dupe.CloseDate < Data.CloseDate ORDER BY Dupe.CloseDate DESC, Dupe.ID) AS OpeningCash
FROM Data
ORDER BY Data.CloseDate, Data.Source;

which uses Allen's method:

http://allenbrowne.com/subquery-01.html#AnotherRecord
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:33
Joined
Aug 30, 2003
Messages
36,118
Happy to help!
 

raziel3

Registered User.
Local time
Today, 16:33
Joined
Oct 5, 2017
Messages
273
I was thinking the DLookup() was looking at a different table. Try this:

SELECT Data.CloseDate, Data.Source, Data.ClosingCash, (SELECT TOP 1 Dupe.ClosingCash FROM Data As Dupe WHERE Dupe.Source = Data.Source AND Dupe.CloseDate < Data.CloseDate ORDER BY Dupe.CloseDate DESC, Dupe.ID) AS OpeningCash
FROM Data
ORDER BY Data.CloseDate, Data.Source;

which uses Allen's method:

http://allenbrowne.com/subquery-01.html#AnotherRecord

Is there anyway to improve efficiency? My database is growing and this is taking a while (about 2-3 seconds) to run the query. Sometimes the screen goes blank.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:33
Joined
Aug 30, 2003
Messages
36,118
Normally I'd expect to run it for a range of dates, like the last month. That should speed it up. Are you always going to run it on the whole table?
 

raziel3

Registered User.
Local time
Today, 16:33
Joined
Oct 5, 2017
Messages
273
Yes. In addition to previous month comparisons I need to do previous year.
 

raziel3

Registered User.
Local time
Today, 16:33
Joined
Oct 5, 2017
Messages
273
Normally I'd expect to run it for a range of dates, like the last month. That should speed it up. Are you always going to run it on the whole table?

Hi, a while back you suggested this:

Code:
SELECT Data.CloseDate, Data.Source, Data.ClosingCash, (SELECT TOP 1 Dupe.ClosingCash FROM Data As Dupe WHERE Dupe.Source = Data.Source AND Dupe.CloseDate < Data.CloseDate ORDER BY Dupe.CloseDate DESC, Dupe.ID) AS OpeningCash
FROM Data
ORDER BY Data.CloseDate, Data.Source;

to help me get the previous day's closing balances. Now that more records are being added I'm getting this error now "At most one record can be returned by this sub-query". Is there anyway to get around this besides setting a date parameter?

I want to return all the records for a year but the query has 4 months of data and it started to giving me this error.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:33
Joined
Aug 30, 2003
Messages
36,118
My guess is that there are 2 records with the same CloseDate and ID. Is ID not unique?
 

Users who are viewing this thread

Top Bottom