DLookUp with a date range

J

joshandsony

Registered User.
Local time
Today, 02:30
Joined
Feb 19, 2009
Messages
59
I am trying to build a column within a query with a dlookup and can't seem to get it right. Here is how it is set up:

Expedite Report Raw table has field PO Date - I need to format the date within this field on another field in query Expedite Report Raw. So what I did is create another table called Decoder. Here is an example of my decoder:

1/1/2007 needs to return a result of 2007
12/31/2007 needs to return a result of 2007
1/1/2008 needs to return a result of 2008
12/31/2008 needs to return a result of 2008
1/1/2009 needs to return a result of Jan-09
and so on.....

I am trying to create the dlookup by pointing to my decode field in the decoder table, then anything in PO date that has a date range of my decoder table needs to return the result of the decode field.

I can't seem to get this to work and I need it to create my pivot table. Can someone help me please!
 
There is a Built In Access Function called Year(AnyDateVar) that might get you close to what you are looking for.
 
I have tried that. The only problem is for the current year, I have to have the month as well. That is why I was trying to do the dlookup function.
 
joshandsony said:
I have tried that. The only problem is for the current year, I have to have the month as well. That is why I was trying to do the dlookup function.
Click to expand...

There is also two Built In Access Functions called MonthName((AnyMonthID), and Month(AnyDate), that can be combined to return the name of the Month ( MonthName(Month(AnyDate) ). Combing these with Year() might be more what you are looking for.

CORRECTION:

It would appear that your Field must be Text instead of Date/Time (otherwise you could not have Month without Year). IN that case, MonthName(Month(AnyDate) will not work, because "February" is not a Date, it is a string. You will need to find a way to convert the String "February" to its MonthID before it can be used.
 
Last edited:
So can I break them out that way. For anything in 2007 use the year only, same with 2008, and in 2009 use date and year?

How would that look in a query?

Thank you for your help. I am a semi-new user to Access and have not gotten into sql or VB yet.
 
Monthname(cstr(month(datevar))) will give you the full month name from a date so
IIf(Year([datetime])=2009,MonthName(CStr(Month([datetime]))) & "-" & Year([datetime]),year(datetime))

might give you what you want, replace datetime by your fieldname.

Brian
 
Hi -

Here's how you can format the year/month according to your criteria:

Code: 
SELECT
    PaymentID
  , AccountID
  , dteLastPaid
 [[B][COLOR="Red"] , IIf(Year([dteLastPaid])=Year(Date()),Format([dteLastPaid],"mmm-yy"),Year([dteLastPaid])) AS Expr1[/COLOR][/B]
FROM
   tblPayments;

Bob

Oops! Brian beat me to it. Hate when that happens!
 
OK so here is how it shows:
IIf(Year([EXPEDITE RAW]![PO Date])=2009,MonthName(CStr(Month([EXPEDITE RAW]![PO Date]))) & "-" & Year([EXPEDITE RAW]![PO Date]),Year("PO Date"))

It is giving me a message that says: Undefined function 'year' in expression.
 
Hi -

Try replacing Year("PO Date") with Year([PO Date]).

You do realize that be specifying the current year as 2009 (as opposed to year(date()), you'll have to adjust the query once it turns 2010?

Bob
 
raskew said:
Hi -

Bob

Oops! Brian beat me to it. Hate when that happens!
Click to expand...

Actually i prefer your answer, I kinda got into a mindset after reading Accessrookie's posts, grrr.
Is Year(datevar) better than Format(datevar,"yyyy")

Brian
 
I changed it to Bob's format and I still get the same response.

IIf(Year([EXPEDITE RAW]![PO Date])=Year(Date()),Format([EXPEDITE RAW]![PO Date],"mmm-yy"),Year([EXPEDITE RAW]![PO Date]))
 
I don't know what to say, it looks ok and as Rookie said Year is a built in Access function. Have you used it before?

If not create a new simple query with say Year(date()) as an expression, or maybe you datefield, just to rule in or out your system.

Brian
 
Brian, can I send you what I have so you can take a look at it? I am very perplexed on this one and as I said I am a fairly new user to access other than basic functions. My AOL screenname is Lilsiskin if you have IM.
 
Try one last thing. From the debug (immediate) window (Ctrl-G] type

? year(date()) <enter>

If that produces the error, rather than 2009, it's undoubtedly a reference problem. Check your references, looking for any marked as Missing.

Bob
 
It says File Not Found.

I am also on Access 2007. Would that make a difference?
 
I don't have 2007, sorry. But I would have thought that Year would still be a built in function. Like Bob I assumed a missing reference but I feel pretty sure that the references to this function cannot be missing in 2002, my release.

Brian
 
I too don't have A2007, thankfully. I'm with Brian here, but just as a test check your help file for the Year() function.

Bob
 
Do you happen to know what reference library it is in? There are only 3 checked in references.
 
All I have checked is Visual Basic for applications and the Access objrct Library and ole automation and it works, I think they are defaults.

Brian
Sorry dinner calls, or rather the boss does :D
 
You must log in or register to reply here.

Similar threads

A
Search button for more than one field search
Replies
3
Views
226
Ken Sheridan
Ken Sheridan
mloucel
Solved Interesting QUERY problem
Replies
11
Views
786
mloucel
mloucel
D
Solved How To Handle Calculated Fields Dependant On Parent Calculated Fields
Replies
4
Views
621
The_Doc_Man
The_Doc_Man
P
Extract Year From Control On Form
Replies
7
Views
452
arnelgp
arnelgp
Gasman
Solved Query advice please
Replies
7
Views
538
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom