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.
