Complex DLookup (well it is for me!)

[Malcy]

Hi
I am trying to identify the financial year within which a transaction occurs. I have tried to use a format to circumvent confusion between US and UK dates but I have clearly got something wrong in the SQL.
I wonder if any of you wizards can spot my mistake?

 intYear = DLookup("lngCurYear", "tblCurrentYear", "Format(Me.txtRecD, 'yyyymmdd') > " & Format(CDate([dtmCurYearStart]), "yyyymmdd") & " And Format(Me.txtRecD, 'yyyymmdd') < " & Format(CDate([dtmCurYearEnd]), "yyyymmdd") & "")

I have cobbled it together from various other things I have used and I am pretty sure I must have some error in my inverted commas but cannot see where.
Any help very much appreciated
Thanks and best wishes

 

[modest]

What's the problem you are having?

Usually when you're using DLookup() you need to have the field being returned in a bracket.
DLookup("[lngCurYear]", ...

 

[pbaldy]

I suspect that using the Format function is making your values text, which needs to be handled differently:

http://www.mvps.org/access/general/gen0018.htm

 

[The_Doc_Man]

intYear = DLookup("lngCurYear", "tblCurrentYear", "Format(Me.txtRecD, 'yyyymmdd') > " & Format(CDate([dtmCurYearStart]), "yyyymmdd") & " And Format(Me.txtRecD, 'yyyymmdd') < " & Format(CDate([dtmCurYearEnd]), "yyyymmdd") & "")

Maybe DLookup( "[lngCurYear]", "tblCurrentYear",
"""" & Format(Me.txtRecD...) & ">" & Format(Cdate([dtmCurYearStart], ...) &
" and " & Format( me.txt....) & """" )

Also, is this a case where BETWEEN...AND... might help? Or do you care?

 

[Malcy]

Thanks guys
Well I had thought of using "between x and y" but was not sure how to SQL it
What I now have is

intYear = DLookup("[lngCurYear]", "tblCurrentYear", """" & Format(Me.txtRecD, 'yyyymmdd') & " Between " & Format(CDate([dtmCurYearStart]), "yyyymmdd") & " And " & Format(CDate([dtmCurYearEnd]), "yyyymmdd") & """")

As soon as I move off the line I get the usual peep, it all goes red and the first ' just after Me.txtRecD, is highlighted.
I tried rather simplistically converting the ' to " but that didn't work either.
I may well have changed the syntax through trying the between and approach but I confess to finding SQL syntax very impenetrable.
Any more thoughts?
Best wishes

 

[The_Doc_Man]

Your problem is basically unbalanced quoting.

 

[Malcy]

Well I thought I had got it with this

intYear = DLookup("[lngCurYear]", "tblCurrentYear", """ & Format(Me.txtRecD, 'yyyymmdd') &  Between  & Format(CDate([dtmCurYearStart]), 'yyyymmdd') & And & Format(CDate([dtmCurYearEnd]), 'yyyymmdd') & """)

since it compiled and ran OK with the first test I ran - I had the value 20/03/2007 as txtRecD and right enough it gave me 2006 as the year I wanted (runs from 1/4/06 to 31/3/07). Bingo I thought.
Then I tried it with 20/04/2007 which should have given me 2007 but it still gives me 2006.
As a result of further testing it seems to always give me 2006 - which is suspiciously the first record in tblCurrentYear. The entries I have are

Year Start Ends Current
2006 01/04/2006 31/03/2007 No
2007 01/04/2007 31/03/2008 No
2008 01/04/2008 31/03/2009 No
2009 01/04/2009 31/03/2010 No
2010 01/04/2010 31/03/2011 No

So it would seem (I think) that the criteria part is not working even though it is an acceptable syntax.
I am currently exploring other ways of trying to get this, unless someone cleverer than I can spot what is going on.

 

[pbaldy]

Rather than a DLookup, why not join the tables together (non-equi join)?

 

[Malcy]

Hi Paul
I was just about to post a note to the effect that I had done exactly as you suggested and it has worked nicely.
Now trying to get some of my other DLookup functions to work. I think it is getting too late since I have fallen over with a joint text and date field criteria set about ten time now - and each time it looks right till it fails!!!
Thanks again
Best wishes