Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-09-2012, 05:52 AM   #1
KAsad
Newly Registered User
 
Join Date: Nov 2012
Posts: 16
Thanks: 4
Thanked 0 Times in 0 Posts
KAsad is on a distinguished road
Dlookup

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

KAsad is offline   Reply With Quote
Old 11-09-2012, 06:03 AM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,232
Thanks: 92
Thanked 2,025 Times in 1,972 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Dlookup

Please refer to the examples here
http://www.techonthenet.com/access/f...in/dlookup.php
jdraw is offline   Reply With Quote
Old 11-09-2012, 06:07 AM   #3
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Dlookup

You have to use "" to surround each entity... something like..
Code:
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...

__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 11-09-2012, 06:24 AM   #4
KAsad
Newly Registered User
 
Join Date: Nov 2012
Posts: 16
Thanks: 4
Thanked 0 Times in 0 Posts
KAsad is on a distinguished road
Re: Dlookup

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
WHEREDATEPART(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
KAsad is offline   Reply With Quote
Old 11-09-2012, 06:29 AM   #5
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Dlookup

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 11-09-2012, 06:38 AM   #6
RainLover
VIP From a land downunder
 
RainLover's Avatar
 
Join Date: Jan 2009
Location: Qld Australia
Posts: 5,044
Thanks: 28
Thanked 517 Times in 492 Posts
RainLover will become famous soon enough
Re: Dlookup

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.
__________________
Regards Rain.

Windows 7. Access 2003. East Coast Australia. GMT +10,

RainLover is offline   Reply With Quote
Old 11-09-2012, 06:49 AM   #7
KAsad
Newly Registered User
 
Join Date: Nov 2012
Posts: 16
Thanks: 4
Thanked 0 Times in 0 Posts
KAsad is on a distinguished road
Re: Dlookup

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

KAsad is offline   Reply With Quote
Old 11-09-2012, 06:54 AM   #8
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Dlookup

Is this a continuous Form view?
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 11-09-2012, 06:59 AM   #9
KAsad
Newly Registered User
 
Join Date: Nov 2012
Posts: 16
Thanks: 4
Thanked 0 Times in 0 Posts
KAsad is on a distinguished road
Re: Dlookup

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.

KAsad is offline   Reply With Quote
Reply

Tags
dlookup access forms

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Nest dlookup inside a dlookup Djblois Modules & VBA 2 06-29-2011 12:06 PM
DLookup within DLookup not working brunopg7 Queries 3 04-22-2011 07:08 AM
DLookup using another Dlookup result stu999 Queries 4 11-09-2010 07:02 AM
Need help with DLookup April15Hater Reports 7 04-15-2009 11:49 AM
Dlookup (then) Dlookup Next Record, How? musicmaker Forms 1 01-18-2001 09:16 AM




All times are GMT -8. The time now is 10:33 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 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World