UtterAccess.com
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
> Dlookup Help, Access 2010    
 
   
Brepea
post Aug 10 2018, 11:22 AM
Post#1



Posts: 592
Joined: 11-January 09
From: UK


Hi - i'm trying to get this to work - but just missing something i guess:

dlookup("CS","q_RiskScores","[RiskID]=" & [RiskID] & " And [ScoreDate]<=#30/04/2018 & " Or [ScoreDate] >= #01/04/2018#)

Any ideas?
Go to the top of the page
 
theDBguy
post Aug 10 2018, 11:28 AM
Post#2


Access Wiki and Forums Moderator
Posts: 73,500
Joined: 19-June 07
From: SunnySandyEggo


Hi,

It looks like you're missing a closing hash tag on the first date a misplaced double quote and ampersand symbols; and also, use either American or International format for your dates. Perhaps you meant something like this:

DLookup("CS","q_RiskScores","[RiskID]=" & [RiskID] & " And [ScoreDate]<=#04/30/2018# AND [ScoreDate] >= #04/01/2018#")


Hope it helps...

PS. I also changed your OR to an AND.
Go to the top of the page
 
Brepea
post Aug 10 2018, 01:30 PM
Post#3



Posts: 592
Joined: 11-January 09
From: UK


Thanks - perfect!

Though i thought #30/04/2018# would work...didn't realise I had to use american date standard (or int std) -- both the american and #30/04/2018# yield same output...?
This post has been edited by Brepea: Aug 10 2018, 01:37 PM
Go to the top of the page
 
theDBguy
post Aug 10 2018, 01:58 PM
Post#4


Access Wiki and Forums Moderator
Posts: 73,500
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Re: "...both the american and #30/04/2018# yield same output...?"

You got lucky because Access tries to be helpful. However, if you were using an ambiguous date such as #01/05/2018#, you would probably find the result may not be what you were expecting.

Cheers!
Go to the top of the page
 
Brepea
post Aug 10 2018, 02:31 PM
Post#5



Posts: 592
Joined: 11-January 09
From: UK


You what is strange is that when i try change it to american format - it auto changes back to dd/mm/yyyy format...? Should this be happening?
Go to the top of the page
 
theDBguy
post Aug 10 2018, 02:37 PM
Post#6


Access Wiki and Forums Moderator
Posts: 73,500
Joined: 19-June 07
From: SunnySandyEggo


It depends... If you're changing the format in the expression, then no, it shouldn't change. However, if you're changing it in the table or query, then yes, it could change because Access will display the date based on your Regional Settings. In other words, if I have a table with a date field and I enter "Jan 5, 2018" in it, then Access should convert it to 5/1/2018, if I had the Regional Settings set to Europe or Canada or Australia, etc. Otherwise, Access would change it to 1/5/2018. However, if I had a code with the following expression:

=DateDiff("d",#1/5/2018#,Date())

Then Access shouldn't change it to:

=DateDiff("d",#5/1/2018#,Date())
Go to the top of the page
 
Brepea
post Aug 10 2018, 02:56 PM
Post#7



Posts: 592
Joined: 11-January 09
From: UK


in same query - when i use an IIF statement - it does convert from american to dd/mm/yyyy, but when i do a dlookup() - it doesn't convert (IIF statement references table t_Risk which defines date as "dd/mm/yyyy" & the dlookup() is referencing a query.
Go to the top of the page
 
theDBguy
post Aug 10 2018, 03:06 PM
Post#8


Access Wiki and Forums Moderator
Posts: 73,500
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Can you post some screenshots to make sure we're on the same page? Access can change the display but it shouldn't change code.
Go to the top of the page
 
Brepea
post Aug 10 2018, 03:13 PM
Post#9



Posts: 592
Joined: 11-January 09
From: UK


Attached File  UA_Help_1.png ( 2.86K )Number of downloads: 3
Go to the top of the page
 
theDBguy
post Aug 10 2018, 03:17 PM
Post#10


Access Wiki and Forums Moderator
Posts: 73,500
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Thanks! Are you saying you previously entered #04/30/2018# into those expressions but Access changed them into #30/04/2018#? If so, does Access change it as soon as you move out of the column or after closing and reopening the query in design view? This is interesting...
Go to the top of the page
 
Brepea
post Aug 10 2018, 03:27 PM
Post#11



Posts: 592
Joined: 11-January 09
From: UK


As soon as i move off the field...
Go to the top of the page
 
theDBguy
post Aug 10 2018, 03:28 PM
Post#12


Access Wiki and Forums Moderator
Posts: 73,500
Joined: 19-June 07
From: SunnySandyEggo


Okay, the only reason I could think of is because the first expression uses a Date value, Access changes it to match your Regional Settings. Whereas, the other expression uses a String value, so Access leaves it alone.

Hope it makes sense...
Go to the top of the page
 
theDBguy
post Aug 10 2018, 03:29 PM
Post#13


Access Wiki and Forums Moderator
Posts: 73,500
Joined: 19-June 07
From: SunnySandyEggo


By the way, to prove my earlier post, try entering the date #01/05/2018# in the IIf() expression to see if Access changes it to #05/01/20108#.

My guess is it won't.
Go to the top of the page
 
Brepea
post Aug 10 2018, 03:39 PM
Post#14



Posts: 592
Joined: 11-January 09
From: UK


No it doesn't change. So do i use mm/dd/yyyy in dlookup still?
Go to the top of the page
 
theDBguy
post Aug 10 2018, 03:46 PM
Post#15


Access Wiki and Forums Moderator
Posts: 73,500
Joined: 19-June 07
From: SunnySandyEggo


Yes, anywhere you use dates in code, you'll have to use US or ISO format; otherwise, you can use the local format. Again, this is to avoid unexpected results when the date could have ambiguous meaning like 01/05/2018.

Cheers!
Go to the top of the page
 
Brepea
post Aug 11 2018, 03:43 PM
Post#16



Posts: 592
Joined: 11-January 09
From: UK


Hi - on a the same thread with same dlookup query...but actually an additional query...

RiskID | ScoreDate | ScoreA | ScoreB --this query (q_RiskProfScores) is sortedby Risk ID & in DESC order of [Score Date]

307 | 21/05/2018 | 1 | 1
307 | 09/10/2017 | 2 | 1
307 | 09/10/2017 | 4 | 1


To pickup May 2015 score A values I'm using this: May2018CS: Nz(DLookUp("ScoreA","q_RiskProfScores","[RiskID] = " & [RiskID] & " and [ScoreDate]<=#31/05/2018#"),0) in query.

So, since i changed the sort order on ScoreDate (Desc) - it shows me the right values for May'18, however -for Apr'18 i'd expect it to show scoreA of 2 - but query shows "4" instead....is there anything i'm missing here?
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th October 2018 - 05:34 AM