Re: Problem with Dlookup function

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 12/15/04 

Date: Wed, 15 Dec 2004 12:15:50 -0500

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>
"Luke" <Luke@discussions.microsoft.com> wrote in message
news:4987EE77-F0E0-4566-8EB9-34BB2E3C2B86@microsoft.com...
> 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
>
> "Ken Snell [MVP]" wrote:
>
> > No, it has not arrived here yet.
> >
> > -- 
> >
> >         Ken Snell
> > <MS ACCESS MVP>
> >
> > "Luke" <Luke@discussions.microsoft.com> wrote in message
> > news:980B5E0E-15D2-4AC9-B61D-269EF2879FAE@microsoft.com...
> > > 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.
> > >
> > > "Ken Snell [MVP]" wrote:
> > >
> > > > 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>
> > > >
> > > > "Luke" <Luke@discussions.microsoft.com> wrote in message
> > > > news:2C7D8110-278C-41EF-B472-929960719157@microsoft.com...
> > > > > 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?
> > > > >
> > > > > "Ken Snell [MVP]" wrote:
> > > > >
> > > > > > 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>
> > > > > >
> > > > > >
> > > > > > "Luke" <Luke@discussions.microsoft.com> wrote in message
> > > > > > news:0079A3EA-9C72-467D-97CB-5D188B4323AE@microsoft.com...
> > > > > > > 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.
> > > > > > >
> > > > > > >
> > > > > > > "Ken Snell [MVP]" wrote:
> > > > > > >
> > > > > > > > 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>
> > > > > > > >
> > > > > > > > "Luke" <Luke@discussions.microsoft.com> wrote in message
> > > > > > > > news:5BF8FC8F-127A-4A31-848A-55657834B20B@microsoft.com...
> > > > > > > > > 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.
> > > > > > > > >
> > > > > > > > > "Ken Snell [MVP]" wrote:
> > > > > > > > >
> > > > > > > > > > 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>
> > > > > > > > > >
> > > > > > > > > > "Luke" <Luke@discussions.microsoft.com> wrote in message
> > > > > > > > > >
news:53B66CA4-5143-46BF-9B8A-86C2C97CE4FE@microsoft.com...
> > > > > > > > > > > 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?
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > "Ken Snell [MVP]" wrote:
> > > > > > > > > > >
> > > > > > > > > > > > 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>
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > "Luke" <Luke@discussions.microsoft.com> wrote in
message
> > > > > > > > > > > >
> > news:ACD1489C-7ABF-470B-B222-834176323BCE@microsoft.com...
> > > > > > > > > > > > > Thanks for the suggestion, Nikos, but it did not
work.
> > > > > > Originally
> > > > > > > > I
> > > > > > > > > > had
> > > > > > > > > > > > it
> > > > > > > > > > > > > as you write, but I streamlined it.  Still get 77.
> > > > > > > > > > > > >
> > > > > > > > > > > > > "Nikos Yannacopoulos" wrote:
> > > > > > > > > > > > >
> > > > > > > > > > > > > > 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.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

Relevant Pages

  • Re: Editable forms with 1000 elements
    ... not about the database that stores the answers/values. ... This will become increasingly difficult if the HTML is all screwed up ... Best of luck, Luke. ...
    (comp.lang.php)
  • Re: http://advogato.org/article/888.html
    ... Rather than leave the above stand, and not post to the topic again, it has been requested that I apologize, and I agree with the necessity. ... Additionally, they constituted an unnecessary response to the topic, because the people involved in the respective subject areas are more than capable of responding sensibly and effectively and positively. ... If, indeed, I believe someone to be trolling the list, I should ignore him and reduce the noise; I should not have sounded off at Luke in the manner in which I did. ...
    (Linux-Kernel)
  • Re: MSDE and VS.NET Quickstart Tutorials
    ... SQL Server does not exist or access denied. ... > hi Luke, ... >> I suspect that the database files may have been corrupted or tables ... > you then have to re-run the pubs install script.. ...
    (microsoft.public.sqlserver.msde)
  • Re: Problem with Dlookup function
    ... "Luke" wrote in message ... >> <MS ACCESS MVP> ...
    (microsoft.public.access.modulesdaovba)
  • Re: File reading vulnerable in PHP and MySQL (Local Exploit)
    ... > # Create a database (mySQL) and upload this file to your server ... > PHP Code: viewfile.php (programmed by Luke) ...
    (Bugtraq)