Problem with Dlookup function

[Ken Snell [MVP]]

It has arrived. I'll look at it later today.

--

Ken Snell
<MS ACCESS MVP>

Got it. I just sent the db. More explanation in the email.

Thank you.

Luke

No, the database has not arrived yet, nor did the original one arrive.

I apologize that I cannot post the full email address here. I'll break it up
for you differently here. The email address can be obtained by putting the
following things together:

k
s n

e


l l
(the number nine)

the normal separator for email (that symbol!)

comcast

(the period)

the word short for network



--

Ken Snell
<MS ACCESS MVP>

I have sent the db again this morning (both .mdb and .zip files). Let me
know if you have it (the subject is: Problem with Dlookup function from
Luke).

If not I must have not decipher the email correctly and will try something
else.

Thank you.

Luke

:

No, it has not arrived here yet.

--

Ken Snell
<MS ACCESS MVP>

Already sent it (an hour ago). Hopefully I got the email right

(so
far

nothing came back). Let me know if you have it.

Thank you again for your help.

:

As Bas notes, remove the words this is not real by removing those
letters,
in order, from the email address -- they are not embedded as the exact
words
in side-by-side letters.

If db is only 240K, then you don't need to zip the file.

--

Ken Snell
<MS ACCESS MVP>

Thank you for your offer.

However, how do I extract your email address? When I click on your
name I
can see your address, but do not know if it is .net or .com

(the
cable

company; right now is shows .renaetl) and your name or

something
else.

The
db itself is only 240K. Should I still zip it?

Can you advise what to do next?

:

Yes, you may send me the database (zipped). My email address

can
be

"extracted" from my post's reply email address by removing

the
words

this is
not real from the address.

Be sure to include specific information regarding how to

find
and

test
the
function, and what it's supposed to do vs. what it's doing.

I will take a look as time permits and then post a reply

back
here

in
the
newsgroup.
--

Ken Snell
<MS ACCESS MVP>


Hello Ken,

Good suggestion, but it still does not work. Technically, it
should,
but
somehow it does not. I get 222 and 444 to capture the

errors
(13

and
94),
but no values.
If you would be interested I can send you example of the

db I
use

to
solve
this. Let me know if you can do that and if yes, how to

get
in

touch
with
you and how to send it (would zip be OK).
Thank you for your help. Greatly, greatly appreciate it.


:

I see a typo in the DLookup function's arguments. Here

is
what

you h
ave
posted:

dblFcstPct = DLookup("[FORECAST_GROWTH]",

"tblForecast",
_

"[PRODUCT_DESC]='" & strProduct & "'" & " And
[PRODUCT_ID]="
&
strProductID & _
" And '[PRODUCT_CODE]= & strProductCode '" & " And
[PLANT_LOCATION]=" & intLocation & _
" And [FISC_YEAR]=" & intYear & " And

[FISC_MONTH]<=
" &

intMonth)

Note the line with the [PRODUCT_CODE] part of the

argument.
It

has
extraneous ' characters and is missing " characters. Try this
(watch
word
wrap by newsreader):

dblFcstPct = DLookup("[FORECAST_GROWTH]",

"tblForecast",
_

"[PRODUCT_DESC]='" & strProduct & "'" & " And
[PRODUCT_ID]="
&
strProductID & _
" And [PRODUCT_CODE]='" & strProductCode & "'" &

"
And

[PLANT_LOCATION]=" & intLocation & _
" And [FISC_YEAR]=" & intYear & " And

[FISC_MONTH]<=
" &

intMonth)


Here is the above code step reformatted to fit in newsreader
window:

dblFcstPct = DLookup("[FORECAST_GROWTH]",

"tblForecast",
_

"[PRODUCT_DESC]='" & strProduct & "'" & _
" And [PRODUCT_ID]=" & strProductID & _
" And [PRODUCT_CODE]='" & strProductCode & _
"'" & " And [PLANT_LOCATION]=" & intLocation & _
" And [FISC_YEAR]=" & intYear & _
" And [FISC_MONTH]<= " & intMonth)

--

Ken Snell
<MS ACCESS MVP>

Function GetGrowthForecast(strProduct As String, strProductID
As
Integer,
_
strProductCode As String, intLocation As Integer, intYear
As
Integer,
_
intMonth As Integer) As Double
On Error GoTo ErrorHandler

'Find corresponding quota % based on all parameters entered
Dim dblFcstPct As Double

dblFcstPct = DLookup("[FORECAST_GROWTH]", "tblForecast", _
"[PRODUCT_DESC]='" & strProduct & "'" & " And
[PRODUCT_ID]=" &
strProductID & _
" And '[PRODUCT_CODE]= & strProductCode '" & " And
[PLANT_LOCATION]=" & intLocation & _
" And [FISC_YEAR]=" & intYear & " And [FISC_MONTH]<= "
&
intMonth)

If IsNull(dblFcstPct) = True Then
GetGrowthForecast = 0
Exit Function
ElseIf dblFcstPct = "" Then
GetGrowthForecast = 0
Exit Function
Else
GetGrowthForecast = dblFcstPct
End If

Exit_ErrorHandler:
On Error GoTo 0
Exit Function

ErrorHandler:
If err = 13 Then
GetGrowthForecast = 222
ElseIf err = 94 Then
GetGrowthForecast = 444
Else
MsgBox err.Number & " " & err.Description & " has
occured." &
vbCrLf
& _
"Please check the error."
Resume Exit_ErrorHandler
End If

End Function

This is the function and how it stands now.
There are two tables with different number of fields.

The
6

parameters
fields and the forecast field are the same:
The table that receives the value (with update query) is
tblProduction
(table that provides the values is tblForecast).
Following are the field names in table tblForeast with
tblProduction
names
in (), followed by Data Type and Detail:

PRODUCT_DESC (Product) Text 6
PRODUCT_ID (ProductID) Number Integer
PRODUCT_CODE (ProductCode) Text 2
PLANT_LOCATION (Location) Number Integer
FISC_YEAR (Year) Number Integer
FISC_MONTH (Month) Number Integer
FORECAST_GROWTH (GrowthForecast) Number Double

Let me know if you also need values. However, I can

not
post

the
actual
values for confidentiality reasons. I may change the values
but
am
unsure
if
that would not change the overall scope and how the function
behaves,
but
I
will try (please specify format).

Thank you for your help.

:

Let's have you post the real code/table info/etc.

Too
many

errors
creep
in
when you try to genericize the information.


Ken Snell
<MS ACCESS MVP>

Thank you for your suggestions:

The TableB is just a typo. There is no space in actual
function
(I am
substituting actual names with fictitious one; I

can
post

the
function
with
actual names if preferable).

I have checked and I do not use any reserve words.

I have hardcoded the function (great idea!). I have
changed
the
first
parameter coding (I have repositioned ' in the function).
I have also found what possibly could be the cause

of
all

the
problems
and
it's the last field: Month. The TableA has production
values
for
every
month; the forecast table (TableB) may have only

one
value

for
the
whole
year
or as many as 12 for each month. Here is an

example
for

TableB:
2 - 16%
5 - 13%
9 - 2.5%
What this means is that for period 2-4 forecast is 16%,
5-8
13%,
9-present
2.5% and I need to update this value to TableA

(that
is

why
the
last
statement read<=). Since I do not have values for 3,4,6,
etc.
I
get
error
message in the function: Invalid use of Null

(error
94).

I have IsNull and "" If statement to catch unmatched
values
(they
should
be
zero). However when I use If statement for IsNull=True, I
still
get
this
error. I can capture the error in ErrorHandler,

but
this

will
not
give me
any value whatsoever.

What do I do?


:

You're using a name for the table of
TableB(space)

Try removing the space at the end of the table

name
in

the
second
argument
of the DLookup function.

If that doesn't fix the problem, then I would

guess
that

your
criterias
are
not matching correctly, even though you say the
variables
have
the
right
values.

Try "hardcoding" values into the DLookup function
(values
that
you
know
are
in the table) to see if the DLookup then works. Watch
out
for
differences in
data types for fields versus the data type of

the
values

that
you're
using.

If this doesn't work, you'll need to post info

for
us

regarding
how
the
table's fields are designed/formatted, some

sample
data

for
the
table's
fields and the variables.

Also, it appears that your table's fields are using
reserved
words,
which
can cause great confusion in ACCESS. See this Knowledge
Base
article
for
more information:
ACC2002: Reserved Words in Microsoft Access

http://support.microsoft.com/default.aspx?scid=kb;en-us;286335


--

Ken Snell
<MS ACCESS MVP>



Thanks for the suggestion, Nikos, but it did

not
work.

Originally
I
had
it
as you write, but I streamlined it. Still get 77.

:

Luke,

Syntax errors in the DLookup. Try:

dblQuota = DLookup("[GrowthQuotaFcst]",

"TableB
", _

"[Product]= '" & Filed1 & "' And [ProductID]= "
&
Filed2 &
_
" And [Location]= '" & Filed3 & "' And
[Division]=
" &
Filed4 &
_
" And [Year]= " & Filed5 & " And

[Month]<=
" &

Filed6 )

HTH,
Nikos

Luke wrote:
Function UpdateQuota(Filed1 As String,

Filed2
As

Integer,
Filed3
As
String, _
Filed4 As Integer, Filed5 As Integer, Filed6
As
Integer)
As
Double

Dim dblQuota As Double

dblQuota = DLookup("[GrowthQuotaFcst]",
"TableB ",
_
"'[Product]= Filed1 ' And

[ProductID]=
" &

Filed2
& _
" And '[Location]= Filed3 ' And
[Division]= "
&
Filed4
& _
" And [Year]= " & Filed5 & " And [Month]<=
" &
Filed6)

If IsNull(dblQuota) = True Then
UpdateQuota = 33
ElseIf dblQuota = "" Then
UpdateQuota = 77
Else
UpdateQuota = dblQuota
End If
End Function

I have created this function. It is

supposed
to

update
Production
table
(Table A) with Production Forecast values (Table
B).
I
can't
create a
query
linking these 2 tables (Table A & B) since some of
the
values
last
for
more
then a month and sometimes the value is

good
for a

year
and as
such, I
use an
Update query based on Table A and this function.

However, when I run this function I get 77, or
empty,
not
null
value
(I set
up the If statement intentionally to know what
result
I
get;
when
this
function works properly both 33 and 77 will be
replaced
with
zero).
When I
place a break in the function, all the variables
show
correct
value,
but the
result is still blank. I have verified

that
both

tables
have
the
6
values
needed to identify the GrowthQuotaFcst.

I am at the end of my wits. Any

suggestions
on

what
to do
next,
what
am I
doing wrong? Is there a limit on how many
conditions
(in
this
case 6)
DLookup function can have? Or maybe I should
change
all
this
and
use
Loop
statement? Or something else?

I greatly appreciate your help.

 

[Ken Snell [MVP]]

I've looked at your database, and I'm sorry, but I cannot reproduce your
error.

I did have to reset two references in the database when I first got
it...both were missing for my ACCESS 2002 setup (both were version 2.8 ADO
libraries, neither of which you need for the database as it's currently
being used). I removed those references, closed the db, reopened it, and
reset them to version 2.7 (which is what I have in ACCESS 2002). I don't
know which version of ACCESS you are using, but I'm guessing it's ACCESS
2003 (with the file in ACCESS 2000 format)?

The query so far as I can tell is running correctly, with no errors. Here is
what the query is giving as a result:

qryFunctionTest Product ProductID ProductCode Location Year Month
GrowthForecast GrowthFcstPct HardCodeFcstPct YearMonthFcst
CEA04F 29 P2 701 2003 11 6.75 6.75 3.875 6.75
CEA04F 29 P2 701 2003 12 6.75 6.75 3.875 6.75
CEA04F 29 P2 701 2004 1 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 2 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 3 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 4 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 5 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 6 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 7 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 8 1.1 3.875 3.875 3.875
CEA04F 29 P2 701 2004 9 4.225 3.875 3.875 3.875
CEA04F 29 P2 701 2004 10 4.225 3.875 3.875 3.875
CEA04F 29 P2 701 2004 11 4.225 3.875 3.875 3.875
CEA04F 29 P2 701 2004 12 4.225 3.875 3.875 3.875



Also note that you're using reserved words as the names of fields in your
tblProducts table. See my earlier post about why you should not use reserved
words, and the link I provided to the MS article about those words. It's
possible that this use has confused ACCESS, and/or it's possible that the
references error (if found in your database on your end) might be causing
your problem.

I've emailed back to you my modified version of your db (just changed the
references) for you to try -- does it now work for you?
--

Ken Snell
<MS ACCESS MVP>

It has arrived. I'll look at it later today.

--

Ken Snell
<MS ACCESS MVP>

Got it. I just sent the db. More explanation in the email.

Thank you.

Luke

Let

me

address

can

Technically,

it

should,
but
somehow it does not. I get 222 and 444 to capture the errors
(13
and
94),
but no values.
If you would be interested I can send you example of the db I
use
to
solve
this. Let me know if you can do that and if yes, how to get
in
touch
with
you and how to send it (would zip be OK).
Thank you for your help. Greatly, greatly appreciate it.


:

I see a typo in the DLookup function's arguments. Here is
what
you h
ave
posted:

dblFcstPct = DLookup("[FORECAST_GROWTH]", "tblForecast",
_
"[PRODUCT_DESC]='" & strProduct & "'" & " And
[PRODUCT_ID]="
&
strProductID & _
" And '[PRODUCT_CODE]= & strProductCode '" & " And
[PLANT_LOCATION]=" & intLocation & _
" And [FISC_YEAR]=" & intYear & " And [FISC_MONTH]<=
" &
intMonth)

Note the line with the [PRODUCT_CODE] part of the argument.
It
has
extraneous ' characters and is missing " characters. Try
this
(watch
word
wrap by newsreader):

dblFcstPct = DLookup("[FORECAST_GROWTH]", "tblForecast",
_
"[PRODUCT_DESC]='" & strProduct & "'" & " And
[PRODUCT_ID]="
&
strProductID & _
" And [PRODUCT_CODE]='" & strProductCode & "'"

&

"

And
[PLANT_LOCATION]=" & intLocation & _
" And [FISC_YEAR]=" & intYear & " And [FISC_MONTH]<=
" &
intMonth)


Here is the above code step reformatted to fit in newsreader
window:

dblFcstPct = DLookup("[FORECAST_GROWTH]", "tblForecast",
_
"[PRODUCT_DESC]='" & strProduct & "'" & _
" And [PRODUCT_ID]=" & strProductID & _
" And [PRODUCT_CODE]='" & strProductCode & _
"'" & " And [PLANT_LOCATION]=" & intLocation & _
" And [FISC_YEAR]=" & intYear & _
" And [FISC_MONTH]<= " & intMonth)

--

Ken Snell
<MS ACCESS MVP>

Function GetGrowthForecast(strProduct As String,
strProductID
As
Integer,
_
strProductCode As String, intLocation As Integer,
intYear
As
Integer,
_
intMonth As Integer) As Double
On Error GoTo ErrorHandler

'Find corresponding quota % based on all parameters
entered
Dim dblFcstPct As Double

dblFcstPct = DLookup("[FORECAST_GROWTH]",
"tblForecast", _
"[PRODUCT_DESC]='" & strProduct & "'" & " And
[PRODUCT_ID]=" &
strProductID & _
" And '[PRODUCT_CODE]= & strProductCode '" &

"

And

[PLANT_LOCATION]=" & intLocation & _
" And [FISC_YEAR]=" & intYear & " And
[FISC_MONTH]<= "
&
intMonth)

If IsNull(dblFcstPct) = True Then
GetGrowthForecast = 0
Exit Function
ElseIf dblFcstPct = "" Then
GetGrowthForecast = 0
Exit Function
Else
GetGrowthForecast = dblFcstPct
End If

Exit_ErrorHandler:
On Error GoTo 0
Exit Function

ErrorHandler:
If err = 13 Then
GetGrowthForecast = 222
ElseIf err = 94 Then
GetGrowthForecast = 444
Else
MsgBox err.Number & " " & err.Description &

"

has

fields.

The

query)

is

cause

of

get

77.

:

Luke,

Syntax errors in the DLookup. Try:

dblQuota = DLookup("[GrowthQuotaFcst]", "TableB
", _
"[Product]= '" & Filed1 & "' And
[ProductID]= "
&
Filed2 &
_
" And [Location]= '" & Filed3 & "' And
[Division]=
" &
Filed4 &
_
" And [Year]= " & Filed5 & " And [Month]<=
" &
Filed6 )

HTH,
Nikos

Luke wrote:
Function UpdateQuota(Filed1 As String, Filed2
As
Integer,
Filed3
As
String, _
Filed4 As Integer, Filed5 As Integer,
Filed6
As
Integer)
As
Double

Dim dblQuota As Double

dblQuota = DLookup("[GrowthQuotaFcst]",
"TableB ",
_
"'[Product]= Filed1 ' And [ProductID]=
" &
Filed2
& _
" And '[Location]= Filed3 ' And
[Division]= "
&
Filed4
& _
" And [Year]= " & Filed5 & " And
[Month]<=
" &
Filed6)

If IsNull(dblQuota) = True Then
UpdateQuota = 33
ElseIf dblQuota = "" Then
UpdateQuota = 77
Else
UpdateQuota = dblQuota
End If
End Function

I have created this function. It is supposed
to
update
Production
table
(Table A) with Production Forecast values
(Table
B).
I
can't
create a
query
linking these 2 tables (Table A & B) since
some of
the
values
last
for
more
then a month and sometimes the value is good
for a
year
and as
such, I
use an
Update query based on Table A and this
function.

However, when I run this function I get 77, or
empty,
not
null
value
(I set
up the If statement intentionally to

know

what

 

[Ken Snell [MVP]]

(Reply to a private email message):

The problem is not with DLookup. It is working exactly as designed. DLookup
finds the "first" record that matches your criteria. Because a table has no
inherent order to how the records are stored, the record that is "first" may
not be the one you'd expect.

For your specific data, you have just one record with a year value of 2003.
All others have 2004. Therefore, in your DLookup criteria, for all 2004 data
records, DLookup will always find the same "2004" record from your table,
because all months are greater than or equal to the table's record where the
year is 2004 and the month is 1.

What you'll need to use is a query, instead of the table, as the "data
source" in the DLookup function. A query allows you to sort the data so that
you can find where in a heirarchial order your data fall.

The query (save it under the name qryForecast) might be something like this:

SELECT * FROM tblForecast
ORDER BY PRODUCT_DESC, PRODUCT_ID, PRODUCT_CODE,
PLANT_LOCATION, FISC_YEAR DESC, FISC_MONTH DESC;

Then the DLookup function would look like this:

dblFcstPct = DLookup("[FORECAST_GROWTH]", "qryForecast", _
"[PRODUCT_DESC]='" & strProduct & "'" & _
" And [PRODUCT_ID]=" & strProductID & _
" And [PRODUCT_CODE]='" & strProductCode & "'" & _
" And [PLANT_LOCATION]=" & intLocation & _
" And [FISC_YEAR]=" & intYear & _
" And [FISC_MONTH]<= " & intMonth)


I believe that this will provide you with the result that you seek.

--

Ken Snell
<MS ACCESS MVP>


---------------private email
note--------------------------------------------
Thank you for your reply. I have rectified the reserved word (it is now
FiscYear), but it did not help as the function still does not work.



Perhaps I am not clear in my explanation. Look at the query results that
you have pasted in your email reply and especially GrowthForecast and
GrowthFcstPct (disregard the 2 last columns for now).



The GrowthForecast is a field that needs to be updated with values from
tblForecast using the GetGrowthForecast function (in module bas_Functions).
It now has values that I manually typed in them (the normal starting values
are 0).



The GrowthFcstPct field is based on the function GetGrowthForecast. As you
see values start with 6.75 for Month 11-12/2003 and change to 3.875 at
1/2004 and stay the same for the remained of the year, when in fact the
values should change again to 1.1 at 8/2004 and should be 4.225 for
9-10/2004. That's how I need the function to work. Whatever the reason the
function stops working at the 2nd value change.



The GrowthForecast values are there just for an example and if the function
would work properly the values in both fields (GrowthForecast and
GrowthFcstPct) must be the same. Unfortunately, they are not.



Here is the copy of the table tblForecast that this is all based on:



PRODUCT_DESC
PRODUCT_ID
PRODUCT_CODE
PLANT_LOCATION
FISC_YEAR
FISC_MONTH
FORECAST_GROWTH

CEA04F
29
P2
701
2003
11
6.75

CEA04F
29
P2
701
2004
1
3.875

CEA04F
29
P2
701
2004
8
1.1

CEA04F
29
P2
701
2004
9
4.225




I hope this helps. If not, please let me know.



Again, thank you for your help.

I've looked at your database, and I'm sorry, but I cannot reproduce your
error.

I did have to reset two references in the database when I first got
it...both were missing for my ACCESS 2002 setup (both were version 2.8 ADO
libraries, neither of which you need for the database as it's currently
being used). I removed those references, closed the db, reopened it, and
reset them to version 2.7 (which is what I have in ACCESS 2002). I don't
know which version of ACCESS you are using, but I'm guessing it's ACCESS
2003 (with the file in ACCESS 2000 format)?

The query so far as I can tell is running correctly, with no errors. Here is
what the query is giving as a result:

qryFunctionTest Product ProductID ProductCode Location Year Month
GrowthForecast GrowthFcstPct HardCodeFcstPct YearMonthFcst
CEA04F 29 P2 701 2003 11 6.75 6.75 3.875 6.75
CEA04F 29 P2 701 2003 12 6.75 6.75 3.875 6.75
CEA04F 29 P2 701 2004 1 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 2 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 3 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 4 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 5 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 6 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 7 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 8 1.1 3.875 3.875 3.875
CEA04F 29 P2 701 2004 9 4.225 3.875 3.875 3.875
CEA04F 29 P2 701 2004 10 4.225 3.875 3.875 3.875
CEA04F 29 P2 701 2004 11 4.225 3.875 3.875 3.875
CEA04F 29 P2 701 2004 12 4.225 3.875 3.875 3.875



Also note that you're using reserved words as the names of fields in your
tblProducts table. See my earlier post about why you should not use reserved
words, and the link I provided to the MS article about those words. It's
possible that this use has confused ACCESS, and/or it's possible that the
references error (if found in your database on your end) might be causing
your problem.

I've emailed back to you my modified version of your db (just changed the
references) for you to try -- does it now work for you?
--

Ken Snell
<MS ACCESS MVP>

It has arrived. I'll look at it later today.

--

Ken Snell
<MS ACCESS MVP>

break
it up putting
the Let function
from right
(so

click

on

your
name I
can see your address, but do not know if it is .net or

..com
(the

cable
company; right now is shows .renaetl) and your name or something
else.
The
db itself is only 240K. Should I still zip it?

Can you advise what to do next?

:

Yes, you may send me the database (zipped). My email

address

can
be
"extracted" from my post's reply email address by

removing
the

words
this is
not real from the address.

Be sure to include specific information regarding how to find
and
test
the
function, and what it's supposed to do vs. what it's doing.

I will take a look as time permits and then post a reply back
here
in
the
newsgroup.
--

Ken Snell
<MS ACCESS MVP>


Hello Ken,

Good suggestion, but it still does not work.

Technically,

it
should,
but
somehow it does not. I get 222 and 444 to capture the errors
(13
and
94),
but no values.
If you would be interested I can send you example of

the
db I

use
to
solve
this. Let me know if you can do that and if yes, how

to
get

in
touch
with
you and how to send it (would zip be OK).
Thank you for your help. Greatly, greatly appreciate it.


:

I see a typo in the DLookup function's arguments.

Here
is

what
you h
ave
posted:

dblFcstPct = DLookup("[FORECAST_GROWTH]", "tblForecast",
_
"[PRODUCT_DESC]='" & strProduct & "'" & " And
[PRODUCT_ID]="
&
strProductID & _
" And '[PRODUCT_CODE]= & strProductCode '" &

"
And

[PLANT_LOCATION]=" & intLocation & _
" And [FISC_YEAR]=" & intYear & " And [FISC_MONTH]<=
" &
intMonth)

Note the line with the [PRODUCT_CODE] part of the argument.
It
has
extraneous ' characters and is missing " characters. Try
this
(watch
word
wrap by newsreader):

dblFcstPct = DLookup("[FORECAST_GROWTH]", "tblForecast",
_
"[PRODUCT_DESC]='" & strProduct & "'" & " And
[PRODUCT_ID]="
&
strProductID & _
" And [PRODUCT_CODE]='" & strProductCode &

"'"

&

"
And
[PLANT_LOCATION]=" & intLocation & _
" And [FISC_YEAR]=" & intYear & " And [FISC_MONTH]<=
" &
intMonth)


Here is the above code step reformatted to fit in newsreader
window:

dblFcstPct = DLookup("[FORECAST_GROWTH]", "tblForecast",
_
"[PRODUCT_DESC]='" & strProduct & "'" & _
" And [PRODUCT_ID]=" & strProductID & _
" And [PRODUCT_CODE]='" & strProductCode & _
"'" & " And [PLANT_LOCATION]=" & intLocation

&

_

" And [FISC_YEAR]=" & intYear & _
" And [FISC_MONTH]<= " & intMonth)

--

Ken Snell
<MS ACCESS MVP>

Function GetGrowthForecast(strProduct As String,
strProductID
As
Integer,
_
strProductCode As String, intLocation As Integer,
intYear
As
Integer,
_
intMonth As Integer) As Double
On Error GoTo ErrorHandler

'Find corresponding quota % based on all parameters
entered
Dim dblFcstPct As Double

dblFcstPct = DLookup("[FORECAST_GROWTH]",
"tblForecast", _
"[PRODUCT_DESC]='" & strProduct & "'" & " And
[PRODUCT_ID]=" &
strProductID & _
" And '[PRODUCT_CODE]= & strProductCode '"

&

"

And
[PLANT_LOCATION]=" & intLocation & _
" And [FISC_YEAR]=" & intYear & " And
[FISC_MONTH]<= "
&
intMonth)

If IsNull(dblFcstPct) = True Then
GetGrowthForecast = 0
Exit Function
ElseIf dblFcstPct = "" Then
GetGrowthForecast = 0
Exit Function
Else
GetGrowthForecast = dblFcstPct
End If

Exit_ErrorHandler:
On Error GoTo 0
Exit Function

ErrorHandler:
If err = 13 Then
GetGrowthForecast = 222
ElseIf err = 94 Then
GetGrowthForecast = 444
Else
MsgBox err.Number & " " & err.Description

&

" fields. query) can
not info/etc.
Too I
can I
have cause only
one

forecast

is ErrorHandler,
but table
name would
guess of
the info
for are
using

http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

--

Ken Snell
<MS ACCESS MVP>

in

message

Thanks for the suggestion, Nikos, but it

did
not

work.
Originally
I
had
it
as you write, but I streamlined it. Still

get

77.

:

Luke,

Syntax errors in the DLookup. Try:

dblQuota = DLookup("[GrowthQuotaFcst]",

"TableB

", _
"[Product]= '" & Filed1 & "' And
[ProductID]= "
&
Filed2 &
_
" And [Location]= '" & Filed3 & "' And
[Division]=
" &
Filed4 &
_
" And [Year]= " & Filed5 & " And [Month]<=
" &
Filed6 )

HTH,
Nikos

Luke wrote:
Function UpdateQuota(Filed1 As String, Filed2
As
Integer,
Filed3
As
String, _
Filed4 As Integer, Filed5 As Integer,
Filed6
As
Integer)
As
Double

Dim dblQuota As Double

dblQuota = DLookup("[GrowthQuotaFcst]",
"TableB ",
_
"'[Product]= Filed1 ' And [ProductID]=
" &
Filed2
& _
" And '[Location]= Filed3 ' And
[Division]= "
&
Filed4
& _
" And [Year]= " & Filed5 & " And
[Month]<=
" &
Filed6)

If IsNull(dblQuota) = True Then
UpdateQuota = 33
ElseIf dblQuota = "" Then
UpdateQuota = 77
Else
UpdateQuota = dblQuota
End If
End Function

I have created this function. It is supposed
to
update
Production
table
(Table A) with Production Forecast values
(Table
B).
I
can't
create a
query
linking these 2 tables (Table A & B) since
some of
the
values
last
for
more
then a month and sometimes the value

is
good

for a
year
and as
such, I
use an
Update query based on Table A and this
function.

However, when I run this function I

get
77, or

empty,
not
null
value
(I set
up the If statement intentionally to

know

what
result
I
get;
when
this
function works properly both 33 and 77 will be
replaced
with
zero).
When I
place a break in the function, all the
variables
show
correct
value,
but the
result is still blank. I have

verified
that

both
tables
have
the
6
values
needed to identify the GrowthQuotaFcst.

I am at the end of my wits. Any suggestions
on
what
to do
next,
what
am I
doing wrong? Is there a limit on how many
conditions
(in
this
case 6)
DLookup function can have? Or maybe I should
change
all
this
and
use
Loop
statement? Or something else?

I greatly appreciate your help.

 

[Guest]

YES, YES, YES. It seems to work. I have tested it in the db1 example and it
worked as I hoped it would.

The real challenge was how it would perform in the real world of over 500K
lines with no ideal set up like we had in the test db. But it looks like it
worked fine. Very fine, that is. I have randomly checked some values and
they are being posted correctly.

I hope this is the end of it. I can not handle much more of this.

I want to thank you for your help and patience. I think we broke the
postings records for the past few days.

Again, thank you for your help. I did not realize that changing the order
would have such an impact on how the function works (by the way I tested
query replacement for a table, but it did not work since I have not sorted
the values according to your suggestion.)

Have Very Merry Christmas and a Happy New Year.

Three cheers,

Luke

(Reply to a private email message):

The problem is not with DLookup. It is working exactly as designed. DLookup
finds the "first" record that matches your criteria. Because a table has no
inherent order to how the records are stored, the record that is "first" may
not be the one you'd expect.

For your specific data, you have just one record with a year value of 2003.
All others have 2004. Therefore, in your DLookup criteria, for all 2004 data
records, DLookup will always find the same "2004" record from your table,
because all months are greater than or equal to the table's record where the
year is 2004 and the month is 1.

What you'll need to use is a query, instead of the table, as the "data
source" in the DLookup function. A query allows you to sort the data so that
you can find where in a heirarchial order your data fall.

The query (save it under the name qryForecast) might be something like this:

SELECT * FROM tblForecast
ORDER BY PRODUCT_DESC, PRODUCT_ID, PRODUCT_CODE,
PLANT_LOCATION, FISC_YEAR DESC, FISC_MONTH DESC;

Then the DLookup function would look like this:

dblFcstPct = DLookup("[FORECAST_GROWTH]", "qryForecast", _
"[PRODUCT_DESC]='" & strProduct & "'" & _
" And [PRODUCT_ID]=" & strProductID & _
" And [PRODUCT_CODE]='" & strProductCode & "'" & _
" And [PLANT_LOCATION]=" & intLocation & _
" And [FISC_YEAR]=" & intYear & _
" And [FISC_MONTH]<= " & intMonth)


I believe that this will provide you with the result that you seek.

--

Ken Snell
<MS ACCESS MVP>


---------------private email
note--------------------------------------------
Thank you for your reply. I have rectified the reserved word (it is now
FiscYear), but it did not help as the function still does not work.



Perhaps I am not clear in my explanation. Look at the query results that
you have pasted in your email reply and especially GrowthForecast and
GrowthFcstPct (disregard the 2 last columns for now).



The GrowthForecast is a field that needs to be updated with values from
tblForecast using the GetGrowthForecast function (in module bas_Functions).
It now has values that I manually typed in them (the normal starting values
are 0).



The GrowthFcstPct field is based on the function GetGrowthForecast. As you
see values start with 6.75 for Month 11-12/2003 and change to 3.875 at
1/2004 and stay the same for the remained of the year, when in fact the
values should change again to 1.1 at 8/2004 and should be 4.225 for
9-10/2004. That's how I need the function to work. Whatever the reason the
function stops working at the 2nd value change.



The GrowthForecast values are there just for an example and if the function
would work properly the values in both fields (GrowthForecast and
GrowthFcstPct) must be the same. Unfortunately, they are not.



Here is the copy of the table tblForecast that this is all based on:



PRODUCT_DESC
PRODUCT_ID
PRODUCT_CODE
PLANT_LOCATION
FISC_YEAR
FISC_MONTH
FORECAST_GROWTH

CEA04F
29
P2
701
2003
11
6.75

CEA04F
29
P2
701
2004
1
3.875

CEA04F
29
P2
701
2004
8
1.1

CEA04F
29
P2
701
2004
9
4.225




I hope this helps. If not, please let me know.



Again, thank you for your help.

I've looked at your database, and I'm sorry, but I cannot reproduce your
error.

I did have to reset two references in the database when I first got
it...both were missing for my ACCESS 2002 setup (both were version 2.8 ADO
libraries, neither of which you need for the database as it's currently
being used). I removed those references, closed the db, reopened it, and
reset them to version 2.7 (which is what I have in ACCESS 2002). I don't
know which version of ACCESS you are using, but I'm guessing it's ACCESS
2003 (with the file in ACCESS 2000 format)?

The query so far as I can tell is running correctly, with no errors. Here is
what the query is giving as a result:

qryFunctionTest Product ProductID ProductCode Location Year Month
GrowthForecast GrowthFcstPct HardCodeFcstPct YearMonthFcst
CEA04F 29 P2 701 2003 11 6.75 6.75 3.875 6.75
CEA04F 29 P2 701 2003 12 6.75 6.75 3.875 6.75
CEA04F 29 P2 701 2004 1 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 2 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 3 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 4 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 5 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 6 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 7 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 8 1.1 3.875 3.875 3.875
CEA04F 29 P2 701 2004 9 4.225 3.875 3.875 3.875
CEA04F 29 P2 701 2004 10 4.225 3.875 3.875 3.875
CEA04F 29 P2 701 2004 11 4.225 3.875 3.875 3.875
CEA04F 29 P2 701 2004 12 4.225 3.875 3.875 3.875



Also note that you're using reserved words as the names of fields in your
tblProducts table. See my earlier post about why you should not use reserved
words, and the link I provided to the MS article about those words. It's
possible that this use has confused ACCESS, and/or it's possible that the
references error (if found in your database on your end) might be causing
your problem.

I've emailed back to you my modified version of your db (just changed the
references) for you to try -- does it now work for you?

 

[Ken Snell [MVP]]

You're welcome! Good luck and Merry Christmas!

--

Ken Snell
<MS ACCESS MVP>

YES, YES, YES. It seems to work. I have tested it in the db1 example and it
worked as I hoped it would.

The real challenge was how it would perform in the real world of over 500K
lines with no ideal set up like we had in the test db. But it looks like it
worked fine. Very fine, that is. I have randomly checked some values and
they are being posted correctly.

I hope this is the end of it. I can not handle much more of this.

I want to thank you for your help and patience. I think we broke the
postings records for the past few days.

Again, thank you for your help. I did not realize that changing the order
would have such an impact on how the function works (by the way I tested
query replacement for a table, but it did not work since I have not sorted
the values according to your suggestion.)

Have Very Merry Christmas and a Happy New Year.

Three cheers,

Luke

(Reply to a private email message):

The problem is not with DLookup. It is working exactly as designed. DLookup
finds the "first" record that matches your criteria. Because a table has no
inherent order to how the records are stored, the record that is "first" may
not be the one you'd expect.

For your specific data, you have just one record with a year value of 2003.
All others have 2004. Therefore, in your DLookup criteria, for all 2004 data
records, DLookup will always find the same "2004" record from your table,
because all months are greater than or equal to the table's record where the
year is 2004 and the month is 1.

What you'll need to use is a query, instead of the table, as the "data
source" in the DLookup function. A query allows you to sort the data so that
you can find where in a heirarchial order your data fall.

The query (save it under the name qryForecast) might be something like this:

SELECT * FROM tblForecast
ORDER BY PRODUCT_DESC, PRODUCT_ID, PRODUCT_CODE,
PLANT_LOCATION, FISC_YEAR DESC, FISC_MONTH DESC;

Then the DLookup function would look like this:

dblFcstPct = DLookup("[FORECAST_GROWTH]", "qryForecast", _
"[PRODUCT_DESC]='" & strProduct & "'" & _
" And [PRODUCT_ID]=" & strProductID & _
" And [PRODUCT_CODE]='" & strProductCode & "'" & _
" And [PLANT_LOCATION]=" & intLocation & _
" And [FISC_YEAR]=" & intYear & _
" And [FISC_MONTH]<= " & intMonth)


I believe that this will provide you with the result that you seek.

--

Ken Snell
<MS ACCESS MVP>


---------------private email
note--------------------------------------------
Thank you for your reply. I have rectified the reserved word (it is now
FiscYear), but it did not help as the function still does not work.



Perhaps I am not clear in my explanation. Look at the query results that
you have pasted in your email reply and especially GrowthForecast and
GrowthFcstPct (disregard the 2 last columns for now).



The GrowthForecast is a field that needs to be updated with values from
tblForecast using the GetGrowthForecast function (in module bas_Functions).
It now has values that I manually typed in them (the normal starting values
are 0).



The GrowthFcstPct field is based on the function GetGrowthForecast. As you
see values start with 6.75 for Month 11-12/2003 and change to 3.875 at
1/2004 and stay the same for the remained of the year, when in fact the
values should change again to 1.1 at 8/2004 and should be 4.225 for
9-10/2004. That's how I need the function to work. Whatever the reason the
function stops working at the 2nd value change.



The GrowthForecast values are there just for an example and if the function
would work properly the values in both fields (GrowthForecast and
GrowthFcstPct) must be the same. Unfortunately, they are not.



Here is the copy of the table tblForecast that this is all based on:



PRODUCT_DESC
PRODUCT_ID
PRODUCT_CODE
PLANT_LOCATION
FISC_YEAR
FISC_MONTH
FORECAST_GROWTH

CEA04F
29
P2
701
2003
11
6.75

CEA04F
29
P2
701
2004
1
3.875

CEA04F
29
P2
701
2004
8
1.1

CEA04F
29
P2
701
2004
9
4.225




I hope this helps. If not, please let me know.



Again, thank you for your help.

I've looked at your database, and I'm sorry, but I cannot reproduce your
error.

I did have to reset two references in the database when I first got
it...both were missing for my ACCESS 2002 setup (both were version 2.8 ADO
libraries, neither of which you need for the database as it's currently
being used). I removed those references, closed the db, reopened it, and
reset them to version 2.7 (which is what I have in ACCESS 2002). I don't
know which version of ACCESS you are using, but I'm guessing it's ACCESS
2003 (with the file in ACCESS 2000 format)?

The query so far as I can tell is running correctly, with no errors.

Here
is

what the query is giving as a result:

qryFunctionTest Product ProductID ProductCode Location Year Month
GrowthForecast GrowthFcstPct HardCodeFcstPct YearMonthFcst
CEA04F 29 P2 701 2003 11 6.75 6.75 3.875 6.75
CEA04F 29 P2 701 2003 12 6.75 6.75 3.875 6.75
CEA04F 29 P2 701 2004 1 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 2 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 3 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 4 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 5 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 6 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 7 3.875 3.875 3.875 3.875
CEA04F 29 P2 701 2004 8 1.1 3.875 3.875 3.875
CEA04F 29 P2 701 2004 9 4.225 3.875 3.875 3.875
CEA04F 29 P2 701 2004 10 4.225 3.875 3.875 3.875
CEA04F 29 P2 701 2004 11 4.225 3.875 3.875 3.875
CEA04F 29 P2 701 2004 12 4.225 3.875 3.875 3.875



Also note that you're using reserved words as the names of fields in your
tblProducts table. See my earlier post about why you should not use reserved
words, and the link I provided to the MS article about those words. It's
possible that this use has confused ACCESS, and/or it's possible that the
references error (if found in your database on your end) might be causing
your problem.

I've emailed back to you my modified version of your db (just changed the
references) for you to try -- does it now work for you?