Create function

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

Lucky

I need to create a custom function that will convert any
date into pay period. I have the pay period information
stored in a separate table (contains PP number, PP start
and end date).

The dates I need to convert may fall on the start/end
date or in between. This function will be used in
queries with no relationship to PP table. I have tried
to use a Dlookup but can not may it work.

Here is the example that does not work for me:

Function GetPayPeriod(strDate As String) As Integer
On Error GoTo GetPayPeriod_Err

Dim varPP As Variant

varPP = DLookup("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= " & strDate & " And
[PAY_PERIOD_END_DTE] <= " & strDate )

GetPayPeriod = varPP

Exit Function

GetPayPeriod_Err:
Exit Function

End Function

Any idea how to do this or a resource where I can find
out how?

Thank you for help.

Lucky
 
The date you're carrying to the function is a string; you need to "convert"
it to a date value:

If the date string is in the format "mm/dd/yyyy", then try this:

varPP = DLookup("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= " & CDate(strDate) & " And
[PAY_PERIOD_END_DTE] <= " & CDate(strDate) )

or this:

varPP = DLookup("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= #" & strDate & "# And
[PAY_PERIOD_END_DTE] <= #" & strDate & "#")


If it's in a different format, you need to change it to the mm/dd/yyyy
format (or mm/dd/yy) in order to have the SQL statement use it correctly.
 
Thank you for your suggestion. Unfortunately, it did not
work. I think the problem is in the criteria set up
(DLookup), since when I use the CurrentDate in the query,
I get either 0 or 1 as Pay Period values (instead of
values between 1 and 52). Or maybe I am going all wrong
about this.

Any further help is greatly appreciated.

Lucky
-----Original Message-----
The date you're carrying to the function is a string; you need to "convert"
it to a date value:

If the date string is in the format "mm/dd/yyyy", then try this:

varPP = DLookup("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= " & CDate(strDate) & " And
[PAY_PERIOD_END_DTE] <= " & CDate(strDate) )

or this:

varPP = DLookup("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= #" & strDate & "# And
[PAY_PERIOD_END_DTE] <= #" & strDate & "#")


If it's in a different format, you need to change it to the mm/dd/yyyy
format (or mm/dd/yy) in order to have the SQL statement use it correctly.

--
Ken Snell
<MS ACCESS MVP>

I need to create a custom function that will convert any
date into pay period. I have the pay period information
stored in a separate table (contains PP number, PP start
and end date).

The dates I need to convert may fall on the start/end
date or in between. This function will be used in
queries with no relationship to PP table. I have tried
to use a Dlookup but can not may it work.

Here is the example that does not work for me:

Function GetPayPeriod(strDate As String) As Integer
On Error GoTo GetPayPeriod_Err

Dim varPP As Variant

varPP = DLookup ("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= " & strDate & " And
[PAY_PERIOD_END_DTE] <= " & strDate )

GetPayPeriod = varPP

Exit Function

GetPayPeriod_Err:
Exit Function

End Function

Any idea how to do this or a resource where I can find
out how?

Thank you for help.

Lucky
Click to expand...


.
Click to expand...
 
Lucky said:
Thank you for your suggestion. Unfortunately, it did not
work. I think the problem is in the criteria set up
(DLookup), since when I use the CurrentDate in the query,
I get either 0 or 1 as Pay Period values (instead of
values between 1 and 52). Or maybe I am going all wrong
about this.

Any further help is greatly appreciated.

Lucky
-----Original Message-----
The date you're carrying to the function is a string; you need to
"convert" it to a date value:

If the date string is in the format "mm/dd/yyyy", then try this:

varPP = DLookup("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= " & CDate(strDate) & " And
[PAY_PERIOD_END_DTE] <= " & CDate(strDate) )

or this:

varPP = DLookup("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= #" & strDate & "# And
[PAY_PERIOD_END_DTE] <= #" & strDate & "#")


If it's in a different format, you need to change it to the
mm/dd/yyyy format (or mm/dd/yy) in order to have the SQL statement
use it correctly.
Click to expand...
Click to expand...

Ken's suggestion of
varPP = DLookup("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= #" & strDate & "# And
[PAY_PERIOD_END_DTE] <= #" & strDate & "#")
Click to expand...
Click to expand...

should work in principle, provided strDate has a value in m/d/y format.
Set a breakpoint in the routine and check the value of strDate. Also,
please report the data type of the fields [PAY_PERIOD_SRT_DTE] and
[PAY_PERIOD_END_DTE] in tbl_Pay_Period. They should be of type
date/time.
 
All fields (Start Date, End Date, CurrentDate, etc.) are
set Date/Time ("Short Date").

I agree with you that it should work in principle, but
for whatever reason it does not. I must be doing
something wrong, but do not know what.

Thank you.

Lucky
-----Original Message-----
Lucky said:
Thank you for your suggestion. Unfortunately, it did not
work. I think the problem is in the criteria set up
(DLookup), since when I use the CurrentDate in the query,
I get either 0 or 1 as Pay Period values (instead of
values between 1 and 52). Or maybe I am going all wrong
about this.

Any further help is greatly appreciated.

Lucky

-----Original Message-----
The date you're carrying to the function is a string; you need to
"convert" it to a date value:

If the date string is in the format "mm/dd/yyyy", then try this:

varPP = DLookup ("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= " & CDate(strDate) & " And
[PAY_PERIOD_END_DTE] <= " & CDate(strDate) )

or this:

varPP = DLookup ("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= #" & strDate & "# And
[PAY_PERIOD_END_DTE] <= #" & strDate & "#")


If it's in a different format, you need to change it to the
mm/dd/yyyy format (or mm/dd/yy) in order to have the SQL statement
use it correctly.
Click to expand...

Ken's suggestion of
varPP = DLookup ("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= #" & strDate & "# And
[PAY_PERIOD_END_DTE] <= #" & strDate & "#")
Click to expand...

should work in principle, provided strDate has a value in m/d/y
format. Set a breakpoint in the routine and check the value of
strDate. Also, please report the data type of the fields
[PAY_PERIOD_SRT_DTE] and [PAY_PERIOD_END_DTE] in tbl_Pay_Period.
They should be of type date/time.
Click to expand...
Click to expand...

What is your regional date format? The only guess left to me is that,
since your function is receiving the date as a string, the date value
you're passing to the function is being formatted in such a way that it
is being misinterpreted in the execution of the query. Set a breakpoint
in the function and examine the value of the strDate argument. Please
post that, and also post a sample of the contents of tbl_PayPeriod.
 
Thank you for all your help and suggestion. I really
appreciated. Some of your ideas made it all work.

I have finally made it work and it works like a charm.
Here is the function:

Function GetPayPeriod(CurrentDate As Date) As Integer
On Error GoTo GetPayPeriod_Err
'Displays Pay Period based on selected date

Dim varPP As Variant

varPP = DLookup("[PPNumber]", "tblPPDates", _
"[PPEnd] >= #" & CurrentDate & "# Or [PPStart] >=
#" & CurrentDate & "#")

GetPayPeriod = varPP

Exit Function

GetPayPeriod_Err:
Exit Function

End Function

The breaker was And. Once I replaced it with Or and
changed <= to >= on both Start and End Date, everything
fell in place.

Lucky

-----Original Message-----
All fields (Start Date, End Date, CurrentDate, etc.) are
set Date/Time ("Short Date").

I agree with you that it should work in principle, but
for whatever reason it does not. I must be doing
something wrong, but do not know what.

Thank you.

Lucky
-----Original Message-----
Thank you for your suggestion. Unfortunately, it did not
work. I think the problem is in the criteria set up
(DLookup), since when I use the CurrentDate in the query,
I get either 0 or 1 as Pay Period values (instead of
values between 1 and 52). Or maybe I am going all wrong
about this.

Any further help is greatly appreciated.

Lucky

-----Original Message-----
The date you're carrying to the function is a string; you need to
"convert" it to a date value:

If the date string is in the format "mm/dd/yyyy", then try this:

varPP = DLookup
Click to expand...
("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= " & CDate(strDate) & " And
[PAY_PERIOD_END_DTE] <= " & CDate(strDate) )

or this:

varPP = DLookup
Click to expand...
("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= #" & strDate & "# And
[PAY_PERIOD_END_DTE] <= #" & strDate & "#")


If it's in a different format, you need to change it to the
mm/dd/yyyy format (or mm/dd/yy) in order to have the SQL statement
use it correctly.

Ken's suggestion of

varPP = DLookup
Click to expand...
("[PAY_PERIOD_NUM]", "tbl_PayPeriod", _
"[PAY_PERIOD_SRT_DTE] >= #" & strDate & "# And
[PAY_PERIOD_END_DTE] <= #" & strDate & "#")

should work in principle, provided strDate has a value in m/d/y
format. Set a breakpoint in the routine and check the value of
strDate. Also, please report the data type of the fields
[PAY_PERIOD_SRT_DTE] and [PAY_PERIOD_END_DTE] in tbl_Pay_Period.
They should be of type date/time.
Click to expand...
Click to expand...

What is your regional date format? The only guess left to me is that,
since your function is receiving the date as a string, the date value
you're passing to the function is being formatted in such a way that it
is being misinterpreted in the execution of the query. Set a breakpoint
in the function and examine the value of the strDate argument. Please
post that, and also post a sample of the contents of tbl_PayPeriod.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Lucky said:
Thank you for all your help and suggestion. I really
appreciated. Some of your ideas made it all work.

I have finally made it work and it works like a charm.
Here is the function:

Function GetPayPeriod(CurrentDate As Date) As Integer
On Error GoTo GetPayPeriod_Err
'Displays Pay Period based on selected date

Dim varPP As Variant

varPP = DLookup("[PPNumber]", "tblPPDates", _
"[PPEnd] >= #" & CurrentDate & "# Or [PPStart] >=
#" & CurrentDate & "#")

GetPayPeriod = varPP

Exit Function

GetPayPeriod_Err:
Exit Function

End Function

The breaker was And. Once I replaced it with Or and
changed <= to >= on both Start and End Date, everything
fell in place.
Click to expand...

Hmm. You renamed the table and fields? It looks like we overlooked the
logic error in your criteria, but I think -- and I may be wrong -- that
it works now only because of an Access behavior you shouldn't rely on.
You've given what evaluates to these criteria:

[PPEnd] >= #<CurrentDate>#
Or
[PPStart] >= #<CurrentDate>#

Suppose for the sake of argument you have these records in tblPPDates:

PPNumber PPStart PPEnd
1 12/30/02 1/12/03
2 1/13/03 1/26/03
3 1/27/03 2/9/03
4 2/10/03 2/23/03
5 2/24/03 3/9/03

And suppose the CurrentDate value given for lookup is #2/1/03#. The set
of records that satisfy your criteria are all those with PPEnd
=#2/1/03# *and* all those with PPStart >= #2/1/03#. That set is:
Click to expand...

PPNumber PPStart PPEnd
3 1/27/03 2/9/03
4 2/10/03 2/23/03
5 2/24/03 3/9/03

So as you see, you haven't actually narrowed down the possible records
that DLookup could return to a single record. Since the query that
DLookup builds internally returns multiple records, it will just return
data from the first one in the recordset. In *theory* you have no idea
which record this will be, because there's no ORDER BY clause in the
internal query. In practice, though, because you're using Access the
qualifying records will normally be returned in ascending order by the
table's primary key, so the function works. My guess is that it would
still work if you dropped the criterion for PPend altogether.

However, it's not a good idea to use a DLookup when the criteria may
return multiple records, if you care which of those records is the
source of the returned data. The order of the records returned by a
query without an ORDER BY clause is not defined, so it is subject to
change at the whim of the database engine.

My recommendation is that you use these criteria:

[PPStart] <= #<CurrentDate>#
And
[PPEnd] >= #<CurrentDate>#

Applied to the records postulated above for CurrentDate=#2/1/03#, this
will return the single record:

PPNumber PPStart PPEnd
3 1/27/03 2/9/03

You wouldn't have to worry about the order of the records, because there
would be only one.

You may say, "But I originally *had* an "And" conjunction in there, and
it didn't work!" Yes, you did -- but you had the >= and <= comparison
operators reversed. I should have seen that in the first place, but I
didn't, for which please accept my apologies.
 
No, I have not changed the table or field names. Just
renamed them for easier use and understanding should
anybody else need this function.

I agree with your analysis and solution. I do not know,
how certain "internal" calculations work, but after so
many tries and variations I was happy to make my original
set up work. I have now changed it to your suggestion.
Works marvelously.

Now if I can figure out how to stop Access from setting
Required field to Yes when I use ADO to create a table, I
would be happy. For a short while anyway before another
problem arises.

It was great working with you. I really enjoyed your
letters and exchange of ideas.

Sincerely,

Lucky
-----Original Message-----
Thank you for all your help and suggestion. I really
appreciated. Some of your ideas made it all work.

I have finally made it work and it works like a charm.
Here is the function:

Function GetPayPeriod(CurrentDate As Date) As Integer
On Error GoTo GetPayPeriod_Err
'Displays Pay Period based on selected date

Dim varPP As Variant

varPP = DLookup("[PPNumber]", "tblPPDates", _
"[PPEnd] >= #" & CurrentDate & "# Or [PPStart] =
#" & CurrentDate & "#")

GetPayPeriod = varPP

Exit Function

GetPayPeriod_Err:
Exit Function

End Function

The breaker was And. Once I replaced it with Or and
changed <= to >= on both Start and End Date, everything
fell in place.
Click to expand...

Hmm. You renamed the table and fields? It looks like we overlooked the
logic error in your criteria, but I think -- and I may be wrong -- that
it works now only because of an Access behavior you shouldn't rely on.
You've given what evaluates to these criteria:

[PPEnd] >= #<CurrentDate>#
Or
[PPStart] >= #<CurrentDate>#

Suppose for the sake of argument you have these records in tblPPDates:

PPNumber PPStart PPEnd
1 12/30/02 1/12/03
2 1/13/03 1/26/03
3 1/27/03 2/9/03
4 2/10/03 2/23/03
5 2/24/03 3/9/03

And suppose the CurrentDate value given for lookup is #2/1/03#. The set
of records that satisfy your criteria are all those with PPEnd
=#2/1/03# *and* all those with PPStart >= #2/1/03#.
Click to expand...
That set is:

PPNumber PPStart PPEnd
3 1/27/03 2/9/03
4 2/10/03 2/23/03
5 2/24/03 3/9/03

So as you see, you haven't actually narrowed down the possible records
that DLookup could return to a single record. Since the query that
DLookup builds internally returns multiple records, it will just return
data from the first one in the recordset. In *theory* you have no idea
which record this will be, because there's no ORDER BY clause in the
internal query. In practice, though, because you're using Access the
qualifying records will normally be returned in ascending order by the
table's primary key, so the function works. My guess is that it would
still work if you dropped the criterion for PPend altogether.

However, it's not a good idea to use a DLookup when the criteria may
return multiple records, if you care which of those records is the
source of the returned data. The order of the records returned by a
query without an ORDER BY clause is not defined, so it is subject to
change at the whim of the database engine.

My recommendation is that you use these criteria:

[PPStart] <= #<CurrentDate>#
And
[PPEnd] >= #<CurrentDate>#

Applied to the records postulated above for CurrentDate=#2/1/03#, this
will return the single record:

PPNumber PPStart PPEnd
3 1/27/03 2/9/03

You wouldn't have to worry about the order of the records, because there
would be only one.

You may say, "But I originally *had* an "And" conjunction in there, and
it didn't work!" Yes, you did -- but you had the >= and <= comparison
operators reversed. I should have seen that in the first place, but I
didn't, for which please accept my apologies.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Back
Top