Dlookup (1 Viewer)

K

KAsad

Registered User.

Local time

Today, 18:15

Joined

Nov 9, 2012

Messages

16

• Nov 9, 2012

• #1

Hi

I have this Dlookup in my form in the source control o f a field but it does not work
=DLookUp([CALENDAR.FIN_MONTH],[CALENDAR],[TIME_DATE]>[CALENDAR.FIN_START] And DatePart("yyyy",[TIME_DATE])=[CALENDAR.FIN_YEAR])

the form is based on Timesheet table and I am trying to find a month in the calendar table based on the timesheet timedate.

Please hel pme

Thank u

 

jdraw

Super Moderator

Staff member

Local time

Today, 13:15

Joined

Jan 23, 2006

Messages

15,379

• Nov 9, 2012

• #2

Please refer to the examples here
http://www.techonthenet.com/access/functions/domain/dlookup.php

 

pr2-eugin

Super Moderator

Local time

Today, 18:15

Joined

Nov 30, 2011

Messages

8,494

• Nov 9, 2012

• #3

You have to use "" to surround each entity... something like..

Code:Copy to clipboard

DLookUp("[CALENDAR.FIN_MONTH]","[CALENDAR]","[TIME_DATE]>[CALENDAR.FIN_START] And Year([TIME_DATE])=[CALENDAR.FIN_YEAR]")

your criteria might mess up.. Just double check...

 

K

KAsad

Registered User.

Local time

Today, 18:15

Joined

Nov 9, 2012

Messages

16

• Nov 9, 2012

• #4

Thank you , I have changed it to this
=DLookUp(" [CALENDAR]![FIN_MONTH] ","[CALENDAR]","[CALENDAR]![FIN_START] <= " & [Forms]![frmFinanceCheckTimesheet]![TIME_DATE] & " AND [CALENDAR]![FIN_YEAR] = " & DatePart("yyyy",[Forms]![frmFinanceCheckTimesheet]![TIME_DATE]))

but it does not return any data.
It actually is the same as this SQL query
SELECT C.FIN_MONTH
FROM CALENDAR C , TIMESHEET T
WHERE DATEPART(YEAR,T.TIME_DATE) = C.FIN_YEAR
AND T.TIME_DATE >= C.FIN_START

the sql returns correct month but the form does not show anything. Any idea?

Thank you

 

pr2-eugin

Super Moderator

Local time

Today, 18:15

Joined

Nov 30, 2011

Messages

8,494

• Nov 9, 2012

• #5

I hope you did go through the link jdraw provided.. if the criteria holds a date/time field then it should be placed between ##..

 

RainLover

VIP From a land downunder

Local time

Tomorrow, 03:15

Joined

Jan 5, 2009

Messages

5,041

• Nov 9, 2012

• #6

Yor DlookUp names do not match your SQL.

Try posting a Pic of your Table to clear up your Names.

Also copy paste your sql rather than retype it.

 

K

KAsad

Registered User.

Local time

Today, 18:15

Joined

Nov 9, 2012

Messages

16

• Nov 9, 2012

• #7

Yes, I have checked the link but I have missed the point that I have to put # for date field.

Now I have changed it to
=DLookUp(" [CALENDAR]![FIN_MONTH] ","[CALENDAR]","[CALENDAR]![FIN_YEAR] = " & DatePart("yyyy",[Forms]![frmFinanceCheckTimesheet]![TIME_DATE]) & " AND
[CALENDAR]![FIN_START] <= #" & [Forms]![frmFinanceCheckTimesheet]![TIME_DATE] & "#")

but for some reason it does not run the 'AND' part. It always returns the first month in my calendar. But I want it to check if the year are the same and the fin_start in calendar is less than the time_date in each row of the form. I hope it make sense

 

pr2-eugin

Super Moderator

Local time

Today, 18:15

Joined

Nov 30, 2011

Messages

8,494

• Nov 9, 2012

• #8

Is this a continuous Form view?

 

K

KAsad

Registered User.

Local time

Today, 18:15

Joined

Nov 9, 2012

Messages

16

• Nov 9, 2012

• #9

It is only one form which shows all timesheets. In each line, I have timesheet ID, date and I need to show a delivery month which is based on a calendar. If the timesheet date is 01/05/2012, then the delivery will be May if it is 29/05/12 then the delivery will be June.