Create Function

  • Thread starter Thread starter Lucky
  • Start date Start date
L

Lucky

I need to create function that will get Pay Period (PP)
number based on selected date (e.g. if PP 38 falls
between 9.8.03 and 9.14.03, all dates, including start
and end that falls within this range need to show 38;
dates starting before 9.8.03 should show 37, after
9.14.03, 39, and so on).

I have the PP values (Year, Number, Start Date, End Date)
stored in a separate table. This function will be used
in queries with no relationship to PP table.

Any suggestions how to do this or point me to a resource
where I can find out how?

Thank you.

Lucky
 
Lucky said:
I need to create function that will get Pay Period (PP)
number based on selected date (e.g. if PP 38 falls
between 9.8.03 and 9.14.03, all dates, including start
and end that falls within this range need to show 38;
dates starting before 9.8.03 should show 37, after
9.14.03, 39, and so on).

I have the PP values (Year, Number, Start Date, End Date)
stored in a separate table. This function will be used
in queries with no relationship to PP table.
Click to expand...

If you have table1 with a date field and you want to
construct a query that includes the PP value from tablepp,
then the tables are related and your query can Join on the
date range. This can not be done using the QBE grid, you
have to use SQL view to enter the query. It will look some
like:

SELECT table1.*, tablepp.PP
FROM table1 LEFT JOIN tablepp
ON table1.adatefield Between tablepp.[Start Date] And
tablep.[End Date]

If you need the PP value for a bunch or records (in a query,
form or report, that will, by far, be the fastest way to do
it.

If you only want the PP value for an occasional date value
(during data entry or some such use), then you can use the
DLookup function to retrieve the related PP:

DLookup("Number", "tablepp", Format(adatefield,
"\#m\/g\/yyyy\#") & " Between [Start Date] And [End Date]")
 
Back
Top