Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-21-2018, 05:31 PM   #1
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 66
Thanks: 20
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Dlookup to return previous day closing

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?

raziel3 is offline   Reply With Quote
Old 11-21-2018, 05:56 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,479
Thanks: 15
Thanked 4,147 Times in 4,078 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Dlookup to return previous day closing

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-22-2018, 10:11 AM   #3
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 66
Thanks: 20
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Dlookup to return previous day closing

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?

raziel3 is offline   Reply With Quote
Old 11-22-2018, 11:12 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,479
Thanks: 15
Thanked 4,147 Times in 4,078 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Dlookup to return previous day closing

Really? Can you attach the db here?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-22-2018, 03:08 PM   #5
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 66
Thanks: 20
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Dlookup to return previous day closing

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. 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 by raziel3; 11-22-2018 at 03:18 PM.
raziel3 is offline   Reply With Quote
Old 11-22-2018, 03:24 PM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,479
Thanks: 15
Thanked 4,147 Times in 4,078 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Dlookup to return previous day closing

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
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
raziel3 (11-22-2018)
Old 11-22-2018, 03:47 PM   #7
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 66
Thanks: 20
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Dlookup to return previous day closing

Works great. Thank you.

raziel3 is offline   Reply With Quote
Old 11-22-2018, 04:51 PM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,479
Thanks: 15
Thanked 4,147 Times in 4,078 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Dlookup to return previous day closing

Happy to help!
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 01-17-2019, 08:22 AM   #9
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 66
Thanks: 20
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Dlookup to return previous day closing

Quote:
Originally Posted by pbaldy View Post
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.
raziel3 is offline   Reply With Quote
Old 01-17-2019, 08:32 AM   #10
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,479
Thanks: 15
Thanked 4,147 Times in 4,078 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Dlookup to return previous day closing

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?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 01-17-2019, 11:52 AM   #11
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 66
Thanks: 20
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Dlookup to return previous day closing

Yes. In addition to previous month comparisons I need to do previous year.
raziel3 is offline   Reply With Quote
Old 04-26-2019, 05:00 AM   #12
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 66
Thanks: 20
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Dlookup to return previous day closing

Quote:
Originally Posted by pbaldy View Post
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.
raziel3 is offline   Reply With Quote
Old 04-26-2019, 07:18 AM   #13
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,479
Thanks: 15
Thanked 4,147 Times in 4,078 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Dlookup to return previous day closing

My guess is that there are 2 records with the same CloseDate and ID. Is ID not unique?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
raziel3 (04-30-2019)
Old 04-30-2019, 09:13 AM   #14
raziel3
Newly Registered User
 
Join Date: Oct 2017
Posts: 66
Thanks: 20
Thanked 0 Times in 0 Posts
raziel3 is on a distinguished road
Re: Dlookup to return previous day closing

Quote:
Originally Posted by pbaldy View Post
My guess is that there are 2 records with the same CloseDate and ID. Is ID not unique?
Yep that was it, duplicate records. Thanks alot.
raziel3 is offline   Reply With Quote
Old 04-30-2019, 09:35 AM   #15
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,479
Thanks: 15
Thanked 4,147 Times in 4,078 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Dlookup to return previous day closing

No problem.

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Tags
access2016 , msaccess

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to get previous days closing stock saudkazia Queries 2 03-17-2017 01:44 AM
Return value from previous record jakratzer Queries 3 03-04-2011 06:26 AM
Return a value from a previous record GaryW Forms 1 02-11-2003 08:07 AM
closing previous form cvaccess Forms 6 08-06-2002 10:26 AM
return to previous form pbuethe Forms 6 07-31-2002 01:05 PM




All times are GMT -8. The time now is 01:23 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World