New to Dlookup (1 Viewer)

[RyLane]

Hello,

I just started using Dlookup but seemed to have hit a snag and not sure how to fix it.

I have a form with a text box for the date and a subform datasheet. The subform shows data that corresponds to the date in the text box. I wanted the textbox to show the next date that was greater than today, so I set its default value as:

=DLookUp("Interview_Date","Interview_Schedule","Interview_Date > Date()")

Now it works fine until I add a new record to the table. It appears the Dlookup is also going by my primary key ID when it looks up the date field. So when I add a new date(record), it ends up with a larger primary ID and it returns an existing older date with lower primary ID.

Hope that makes sense,

Thanks in Advance,
Ryan

 

[CJ_London]

Hope that makes sense,

Sorry, no it doesn't

Suggest post sample sample data to illustrate your problem

 

[jdraw]

I agree with CJ ???? Can you tell us in plain English WHAT you are trying to accomplish? Forget any code or Access jargon?

 

[RyLane]

I was afraid of that, I'll try the example route

My table looks likes like this, where ID is my primary key and an auto-complete field

ID Interview Date Person
1 1/12/2015 Tom
2 1/12/2015 Carol
3 1/15/2015
4 1/15/2015 Bob
5 1/15/2015 Anne
6 1/18/2015
7 1/18/2015 Kelly

As it is, my Dlookup works correctly in that it will show me 01/18/2015 because it is the next date in my table that is greater than today.

The issue is when I add records that fall between today’s date and the next date in the table
ID Interview Date Person
1 1/12/2015 Tom
2 1/12/2015 Carol
3 1/15/2015
4 1/15/2015 Bob
5 1/15/2015 Anne
8 1/16/2015 Jo
9 1/16/2015 Mark
6 1/18/2015
7 1/18/2015 Kelly

Now the Dlookup will not catch the new dates. It seems to be taking into account the ID field, and because the auto-complete numbers are higher for 1/16/2015 than 1/18/2015, it doesn’t return the new date, it returns 01/18/2015

 

[jdraw]

You're using DLookup---no jargon or Access terms.
see Autonumbers for more info/uses

Now the Dlookup will not catch the new dates

So you've added records Ids 8 and 9. Which record do you want to find?
Plain English --no access, no jargon

If record 3
Record 3 (untested)


=DLookUp("Interview_Date","Interview_Schedule","Interview_Date >= #" & Date &"# and Person =''")

If record 6 (untested)
=DLookUp("Interview_Date","Interview_Schedule","Interview_Date > #" & Date &"# and Person =''")

Note: Hopefully InterviewDate is a Date/Time datatype.

 

[smig]

DLookUp() will show you the first record match your criteria, based on the original table sorting.
Try to use DMin()

 

[RyLane]

Thanks smig does what I'm looking for,