DLookup return date from database

[Beejer]

I'm running Access 2003 on WinXP

Here is my problem:

I'm trying to lookup a date in a table from a query data. The query
include the following: Query = Qry_Invoice and Table = Tbl_Friday

Qry_Invoice contains invoice data like: supplier name, invoice date,
payment date, purchase order.

Tbl_Friday contains all the fridays from 2003 to 2007.

Since friday is the chosen date for the weekly accounting check run, my
issue is I'm trying to lookup the nearest friday to the payment date.
Once I get my result, I will be able to sort and run the weekly print
check reports automatically.

For example: I have an invoice form a supplier XYZ Inc. which is dated
2005-9-15 (Invoice_Date), the supplier has 45 days terms so the
Payment_date is 2005-10-30. This date appear between two fridays
2005-10-28 & 2005-11-04. I either get the first date of the table or
the last. How can I run a function to get my result?

I've tried the expressions DMax, DMin and DLookup with no success by
grouping the database. This seemed to be the most logical way until
now...

Thanks

[pietlinden]

How about doing something like a summary query, and using MIN>SomeDate
or MAX<SomeDate. Then you can return it anywhere you want or use DMAX
or DMIN...

[Bob Quintal]

"Beejer" <bj********@yahoo.ca> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:

I'm running Access 2003 on WinXP

Here is my problem:

I'm trying to lookup a date in a table from a query data. The
query include the following: Query = Qry_Invoice and Table =
Tbl_Friday

Qry_Invoice contains invoice data like: supplier name, invoice
date, payment date, purchase order.

Tbl_Friday contains all the fridays from 2003 to 2007.

Since friday is the chosen date for the weekly accounting
check run, my issue is I'm trying to lookup the nearest friday
to the payment date. Once I get my result, I will be able to
sort and run the weekly print check reports automatically.

For example: I have an invoice form a supplier XYZ Inc. which
is dated 2005-9-15 (Invoice_Date), the supplier has 45 days
terms so the Payment_date is 2005-10-30. This date appear
between two fridays 2005-10-28 & 2005-11-04. I either get the
first date of the table or the last. How can I run a function
to get my result?

I've tried the expressions DMax, DMin and DLookup with no
success by grouping the database. This seemed to be the most
logical way until now...

Thanks

What you are wanting to do with the invoice_date is to add the
number of days to make it the following friday.

The weekday([invoice_date],vbSaturday) function returns the
number 1 for saturday, 2 for sunday, 3 for monday through 7 for
friday, so embedding 7- that value to
dateadd("d",7-weekday([]invoice_date],vbSaturday),invoice_date)
will give you just the fridays. use that calculated date to sort
and summarize, you won't even need your table of fridays..

--
Bob Quintal

PA is y I've altered my email address.

[Beejer]

Thanks Bob, I haven't thought of that function. I've tried to implement
your exact function but didn't quite work. Although, it inspired me to
work around it to figure out a solution. Here my result :

Duedate: DateAdd("d";(6-Weekday([Invoice_Date]));[Invoice_Date])

It seems to be working very nicely in the database.

Regards

Beejer

[Bob Quintal]

"Beejer" <bj********@yahoo.ca> wrote in
news:11*********************@g44g2000cwa.googlegro ups.com:

Thanks Bob, I haven't thought of that function. I've tried to
implement your exact function but didn't quite work. Although,
it inspired me to work around it to figure out a solution.
Here my result :

Duedate:
DateAdd("d";(6-Weekday([Invoice_Date]));[Invoice_Date])

It seems to be working very nicely in the database.

Regards

Beejer

Glad to have helped.
--
Bob Quintal

PA is y I've altered my email address.