Null value won't pass into function

Hi All,

I have started tearing my hair out over this problem!

I am pulling some data from a table and passing it as variables into a
function in Access. One of the variables I'm passing through can be
Null, so to avoid trying to pass a Null value to a function as a
string (which I gather Access wouldn't like) I created an IIF function
inside the parameters so if the value was null, it passed the string
"NULLDEPOT" into the function, and I dealt with it inside the
function.

No matter HOW I try (stacked queries, new variables, taking the IIF
out of the function parameters and creating it earlier)...I always get
an "#Error" value returned. Even when I set a breakpoint at the top of
the function, Access won't trip it if that value was ever Null,
however every other value works fine?!

I'll include my code below:

Example data being passed in is as follows:

Supplier("13","AG","S","JL884","NULLDEPOT")

Query code:
=========================
VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier
Number],IIf([DepotID] Is Null,"NULLDEPOT",[DepotID]))
=========================

VB Function Code:
=========================
Public Function Supplier(OrigDep As String, AgCE As String, PMC As
String, VendNum As String, MainDepot As String) As String
    Dim TempSupplier As String

....snip...

    ElseIf OrigDep = "13" Then
        If MainDepot = "NULLDEPOT" Then
            TempSupplier = "N"
        ElseIf MainDepot = "13" Then
            TempSupplier = VendNum
        Else
            TempSupplier = "1400000"
        End If

....snip...

    Else
        TempSupplier = "N"
    End If

    ' AgCE and PMC Logic Final Override
    If AgCE = "CE" Or PMC = "V" Then
        TempSupplier = "ZZZ"
    End If

    ' Return string
    Supplier = TempSupplier

End Function
=========================

Please HELP!!!

0
m
5/18/2007 1:27:11 AM
access.queries 6343 articles. 1 followers. Follow

10 Replies
1137 Views

Similar Articles

[PageSpeed] 28

m.wanstall wrote:
> Hi All,
> 
> I have started tearing my hair out over this problem!
> 
> I am pulling some data from a table and passing it as variables into a
> function in Access. One of the variables I'm passing through can be
> Null, so to avoid trying to pass a Null value to a function as a
> string (which I gather Access wouldn't like) I created an IIF function
> inside the parameters so if the value was null, it passed the string
> "NULLDEPOT" into the function, and I dealt with it inside the
> function.
> 
> No matter HOW I try (stacked queries, new variables, taking the IIF
> out of the function parameters and creating it earlier)...I always get
> an "#Error" value returned. Even when I set a breakpoint at the top of
> the function, Access won't trip it if that value was ever Null,
> however every other value works fine?!

Do you know about Nz?

Try using Nz([possible null field], value if null)

Query code:
=========================
VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier
Number],Nz([DepotID],"NULLDEPOT"))

Hoping this helps,
-- 
Smartin
0
Smartin
5/18/2007 2:03:35 AM
On May 18, 12:03 pm, Smartin <smartin...@yahoo.com> wrote:
> m.wanstall wrote:
> > Hi All,
>
> > I have started tearing my hair out over this problem!
>
> > I am pulling some data from a table and passing it as variables into a
> > function in Access. One of the variables I'm passing through can be
> > Null, so to avoid trying to pass a Null value to a function as a
> > string (which I gather Access wouldn't like) I created an IIF function
> > inside the parameters so if the value was null, it passed the string
> > "NULLDEPOT" into the function, and I dealt with it inside the
> > function.
>
> > No matter HOW I try (stacked queries, new variables, taking the IIF
> > out of the function parameters and creating it earlier)...I always get
> > an "#Error" value returned. Even when I set a breakpoint at the top of
> > the function, Access won't trip it if that value was ever Null,
> > however every other value works fine?!
>
> Do you know about Nz?
>
> Try using Nz([possible null field], value if null)
>
> Query code:
> =========================
> VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier
> Number],Nz([DepotID],"NULLDEPOT"))
>
> Hoping this helps,
> --
> Smartin- Hide quoted text -
>
> - Show quoted text -

Thanks Smartin, I'd already tried using NZ(). It also returns
"#Error"?!

I am truly stumped. If it won't even worked with a stacked query (ie
using the IIF statement in one query, then using another query to
import the result of that IIF statement and passing it through the
function -- that STILL returns an Error for those records that were
originally Null) I just don't understand it?! How would Access have
ANY memory that those records were originally Null? My IIF statement
should have turned the Null option into a String (which it does when I
ran the query...but the error occurs on the stacked query when I try
to pass it into the function still).

0
m
5/18/2007 2:19:14 AM
m.wanstall wrote:
> On May 18, 12:03 pm, Smartin <smartin...@yahoo.com> wrote:
>> m.wanstall wrote:
>>> Hi All,
>>> I have started tearing my hair out over this problem!
>>> I am pulling some data from a table and passing it as variables into a
>>> function in Access. One of the variables I'm passing through can be
>>> Null, so to avoid trying to pass a Null value to a function as a
>>> string (which I gather Access wouldn't like) I created an IIF function
>>> inside the parameters so if the value was null, it passed the string
>>> "NULLDEPOT" into the function, and I dealt with it inside the
>>> function.
>>> No matter HOW I try (stacked queries, new variables, taking the IIF
>>> out of the function parameters and creating it earlier)...I always get
>>> an "#Error" value returned. Even when I set a breakpoint at the top of
>>> the function, Access won't trip it if that value was ever Null,
>>> however every other value works fine?!
>> Do you know about Nz?
>>
>> Try using Nz([possible null field], value if null)
>>
>> Query code:
>> =========================
>> VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier
>> Number],Nz([DepotID],"NULLDEPOT"))
>>
>> Hoping this helps,
>> --
>> Smartin- Hide quoted text -
>>
>> - Show quoted text -
> 
> Thanks Smartin, I'd already tried using NZ(). It also returns
> "#Error"?!
> 
> I am truly stumped. If it won't even worked with a stacked query (ie
> using the IIF statement in one query, then using another query to
> import the result of that IIF statement and passing it through the
> function -- that STILL returns an Error for those records that were
> originally Null) I just don't understand it?! How would Access have
> ANY memory that those records were originally Null? My IIF statement
> should have turned the Null option into a String (which it does when I
> ran the query...but the error occurs on the stacked query when I try
> to pass it into the function still).
> 

Hmm, well I'm not totally following this, but another option is to 
change your function so the parameters that could be null are declared 
as Variant. Maybe:

Public Function Supplier(OrigDep As String, AgCE As String, PMC As
String, VendNum As String, MainDepot As Variant) As String
     Dim TempSupplier As String

Then add code immediately after the declarations to handle the possible 
null:

MainDepot = Nz(MainDepot, "NULLDEPOT")

HTH,

-- 
Smartin
0
Smartin
5/18/2007 2:29:21 AM
On May 18, 12:29 pm, Smartin <smartin...@yahoo.com> wrote:
> m.wanstall wrote:
> > On May 18, 12:03 pm, Smartin <smartin...@yahoo.com> wrote:
> >> m.wanstall wrote:
> >>> Hi All,
> >>> I have started tearing my hair out over this problem!
> >>> I am pulling some data from a table and passing it as variables into a
> >>> function in Access. One of the variables I'm passing through can be
> >>> Null, so to avoid trying to pass a Null value to a function as a
> >>> string (which I gather Access wouldn't like) I created an IIF function
> >>> inside the parameters so if the value was null, it passed the string
> >>> "NULLDEPOT" into the function, and I dealt with it inside the
> >>> function.
> >>> No matter HOW I try (stacked queries, new variables, taking the IIF
> >>> out of the function parameters and creating it earlier)...I always get
> >>> an "#Error" value returned. Even when I set a breakpoint at the top of
> >>> the function, Access won't trip it if that value was ever Null,
> >>> however every other value works fine?!
> >> Do you know about Nz?
>
> >> Try using Nz([possible null field], value if null)
>
> >> Query code:
> >> =========================
> >> VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier
> >> Number],Nz([DepotID],"NULLDEPOT"))
>
> >> Hoping this helps,
> >> --
> >> Smartin- Hide quoted text -
>
> >> - Show quoted text -
>
> > Thanks Smartin, I'd already tried using NZ(). It also returns
> > "#Error"?!
>
> > I am truly stumped. If it won't even worked with a stacked query (ie
> > using the IIF statement in one query, then using another query to
> > import the result of that IIF statement and passing it through the
> > function -- that STILL returns an Error for those records that were
> > originally Null) I just don't understand it?! How would Access have
> > ANY memory that those records were originally Null? My IIF statement
> > should have turned the Null option into a String (which it does when I
> > ran the query...but the error occurs on the stacked query when I try
> > to pass it into the function still).
>
> Hmm, well I'm not totally following this, but another option is to
> change your function so the parameters that could be null are declared
> as Variant. Maybe:
>
> Public Function Supplier(OrigDep As String, AgCE As String, PMC As
> String, VendNum As String, MainDepot As Variant) As String
>      Dim TempSupplier As String
>
> Then add code immediately after the declarations to handle the possible
> null:
>
> MainDepot = Nz(MainDepot, "NULLDEPOT")
>
> HTH,
>
> --
> Smartin- Hide quoted text -
>
> - Show quoted text -

Thanks Smartin,

Tried that and it's still throwing an "#Error".

I should point out that the values in the DepotID are NULL because of
a join that I've done in the query where I only include values from
TableB when they;re in TableA. TableB holds the DepotID and not all
PartNumbers (in TableA) have a matching DepotID so that's how I get my
Null value (not sure if this makes any kind of difference).

In case it helps anyone I'll include the full functions code without
the SNIPs and the actual SQL code that invokes the function and the
data as it is passed through so hopefully someone can recreate my
error if need be:

[SQL CODE]
....
Supplier("13",[AGCECOM],[PMC],[Supplier Number],IIf([DepotID] Is
Null,"NULLDEPOT",[DepotID])) AS VendorCode
....
[/SQL CODE]


[DATA]
....
VendorCode: Supplier("13","AG","S","JL8898",Null)
--OR if the IIF works it should send--
VendorCode: Supplier("13","AG","S","JL8898","NULLDEPOT")
....
[/DATA]


[VB CODE]
Public Function Supplier(OrigDep As String, AgCE As String, PMC As
String, VendNum As String, MainDepot As Variant) As String
    Dim TempSupplier As String

    MainDepot = Nz(MainDepot, "NULLDEPOT")

    ' Depot 1
    If OrigDep = "10" Then
        TempSupplier = "1300000"

    ' Depot 2
    ElseIf OrigDep = "13" Then
        If MainDepot = "NULLDEPOT" Then
            TempSupplier = "N"
        ElseIf MainDepot = "13" Then
            TempSupplier = VendNum
        Else
            TempSupplier = "1400000"
        End If

    ' Depot 3
    ElseIf OrigDep = "14" Then
        If MainDepot = "NULLDEPOT" Then
            TempSupplier = ""
        ElseIf MainDepot = "14" Then
            TempSupplier = VendNum
        Else
            TempSupplier = "1300000"
        End If

    ' Depot not 13, 14 or 10...error
    Else
        TempSupplier = "N"
    End If

    ''''''''''''''''''''''''''''''''''''
    ' AgCE and PMC Logic Final Override
    If AgCE = "CE" Or PMC = "V" Then
        TempSupplier = "ZZZ"
    End If

    ' Return string
    Supplier = TempSupplier

End Function
[/VB CODE]

0
m
5/18/2007 3:49:38 AM
Hi,
you have to declare all arguments here as variant:
Public Function Supplier(OrigDep As String, AgCE As String, PMC As
String, VendNum As String, MainDepot As String) As String

and then test for null inside this function

-- 
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

"m.wanstall" <m.wanstall@gmail.com> wrote in message 
news:1179451631.114560.255080@k79g2000hse.googlegroups.com...
> Hi All,
>
> I have started tearing my hair out over this problem!
>
> I am pulling some data from a table and passing it as variables into a
> function in Access. One of the variables I'm passing through can be
> Null, so to avoid trying to pass a Null value to a function as a
> string (which I gather Access wouldn't like) I created an IIF function
> inside the parameters so if the value was null, it passed the string
> "NULLDEPOT" into the function, and I dealt with it inside the
> function.
>
> No matter HOW I try (stacked queries, new variables, taking the IIF
> out of the function parameters and creating it earlier)...I always get
> an "#Error" value returned. Even when I set a breakpoint at the top of
> the function, Access won't trip it if that value was ever Null,
> however every other value works fine?!
>
> I'll include my code below:
>
> Example data being passed in is as follows:
>
> Supplier("13","AG","S","JL884","NULLDEPOT")
>
> Query code:
> =========================
> VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier
> Number],IIf([DepotID] Is Null,"NULLDEPOT",[DepotID]))
> =========================
>
> VB Function Code:
> =========================
> Public Function Supplier(OrigDep As String, AgCE As String, PMC As
> String, VendNum As String, MainDepot As String) As String
>    Dim TempSupplier As String
>
> ...snip...
>
>    ElseIf OrigDep = "13" Then
>        If MainDepot = "NULLDEPOT" Then
>            TempSupplier = "N"
>        ElseIf MainDepot = "13" Then
>            TempSupplier = VendNum
>        Else
>            TempSupplier = "1400000"
>        End If
>
> ...snip...
>
>    Else
>        TempSupplier = "N"
>    End If
>
>    ' AgCE and PMC Logic Final Override
>    If AgCE = "CE" Or PMC = "V" Then
>        TempSupplier = "ZZZ"
>    End If
>
>    ' Return string
>    Supplier = TempSupplier
>
> End Function
> =========================
>
> Please HELP!!!
> 

0
Alex
5/18/2007 4:36:26 AM
> VendorCode: Supplier("13","AG","S","JL8898",Null)
> --OR if the IIF works it should send--
> VendorCode: Supplier("13","AG","S","JL8898","NULLDEPOT")

If you're getting this from a table, don't forget to use 
MyTable!MyField.Value (with the .Value being the important part here) if 
you're passing this to a Variant.  If you just use MyTable!MyField, you end 
up passing the whole field, which may or may not end up working, but is 
probably not what you intended to pass.

> [VB CODE]
> Public Function Supplier(OrigDep As String, AgCE As String, PMC As
> String, VendNum As String, MainDepot As Variant) As String

Try chaging all of these to, for example, "ByVal OrigDep As String, ByVal 
AgCE As String", etc...unless any of them actually need to be ByRef, but 
that wasn't my understanding of your code.

>    Dim TempSupplier As String
>
>    MainDepot = Nz(MainDepot, "NULLDEPOT")

Try putting a Debug.Print MainDepot here, to be absolutely certain that it's 
getting the correct value.  If you *are* passing the entire field by 
accident, then this is actually trying to change the field value, which is 
probably being ignored because the joined field is not updatable in this 
instance.

Other than that, your code looks good to me, but definitely check on how 
you're passing the value, because I suspect that might be the source of the 
problem here.



Rob 


0
Robert
5/18/2007 5:48:02 AM
On May 18, 2:36 pm, "Alex Dybenko"
<alex...@PLEASE.cemi.NO.rssi.SPAM.ru> wrote:
> Hi,
> you have to declare all arguments here as variant:
> Public Function Supplier(OrigDep As String, AgCE As String, PMC As
> String, VendNum As String, MainDepot As String) As String
>
> and then test for null inside this function
>
> --
> Best regards,
> ___________
> Alex Dybenko (MVP)http://alexdyb.blogspot.comhttp://www.PointLtd.com
>
> "m.wanstall" <m.wanst...@gmail.com> wrote in message
>
> news:1179451631.114560.255080@k79g2000hse.googlegroups.com...
>
>
>
> > Hi All,
>
> > I have started tearing my hair out over this problem!
>
> > I am pulling some data from a table and passing it as variables into a
> > function in Access. One of the variables I'm passing through can be
> > Null, so to avoid trying to pass a Null value to a function as a
> > string (which I gather Access wouldn't like) I created an IIF function
> > inside the parameters so if the value was null, it passed the string
> > "NULLDEPOT" into the function, and I dealt with it inside the
> > function.
>
> > No matter HOW I try (stacked queries, new variables, taking the IIF
> > out of the function parameters and creating it earlier)...I always get
> > an "#Error" value returned. Even when I set a breakpoint at the top of
> > the function, Access won't trip it if that value was ever Null,
> > however every other value works fine?!
>
> > I'll include my code below:
>
> > Example data being passed in is as follows:
>
> > Supplier("13","AG","S","JL884","NULLDEPOT")
>
> > Query code:
> > =========================
> > VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier
> > Number],IIf([DepotID] Is Null,"NULLDEPOT",[DepotID]))
> > =========================
>
> > VB Function Code:
> > =========================
> > Public Function Supplier(OrigDep As String, AgCE As String, PMC As
> > String, VendNum As String, MainDepot As String) As String
> >    Dim TempSupplier As String
>
> > ...snip...
>
> >    ElseIf OrigDep = "13" Then
> >        If MainDepot = "NULLDEPOT" Then
> >            TempSupplier = "N"
> >        ElseIf MainDepot = "13" Then
> >            TempSupplier = VendNum
> >        Else
> >            TempSupplier = "1400000"
> >        End If
>
> > ...snip...
>
> >    Else
> >        TempSupplier = "N"
> >    End If
>
> >    ' AgCE and PMC Logic Final Override
> >    If AgCE = "CE" Or PMC = "V" Then
> >        TempSupplier = "ZZZ"
> >    End If
>
> >    ' Return string
> >    Supplier = TempSupplier
>
> > End Function
> > =========================
>
> > Please HELP!!!- Hide quoted text -
>
> - Show quoted text -

Thanks for that Alex, that did the trick!

Can I ask WHY that is the case if I'm only ever passing Strings into
the other variables?

0
m
5/18/2007 6:23:52 AM
Hi,

> Can I ask WHY that is the case if I'm only ever passing Strings into
> the other variables?

even all fields has value - null can be passed to function on new record, or 
while form is loaded, and once you get an error in such function - if 
returns for all records. This is what I guess, don't know for sure.

-- 
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

0
Alex
5/18/2007 11:01:11 AM
Are you sure that all the fields in all the rows have a value?
IF [AGCECOM], [PMC], or [Supplier Number} is null in any record then you 
would get an error when you have declared the arguments as string in the 
function.

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Alex Dybenko" <alexdyb@PLEASE.cemi.NO.rssi.SPAM.ru> wrote in message 
news:OaVrYvTmHHA.4840@TK2MSFTNGP04.phx.gbl...
> Hi,
>
>> Can I ask WHY that is the case if I'm only ever passing Strings into
>> the other variables?
>
> even all fields has value - null can be passed to function on new record, 
> or while form is loaded, and once you get an error in such function - if 
> returns for all records. This is what I guess, don't know for sure.
>
> -- 
> Best regards,
> ___________
> Alex Dybenko (MVP)
> http://alexdyb.blogspot.com
> http://www.PointLtd.com
> 


0
John
5/18/2007 11:24:23 AM
There is nothing preventing you from defining variables as Variant in a 
declaration statement.  Why not just declare that parameter (MainDepot) as a 
variant.  Then, in you function, test to see if it is null 

IF ISNULL(MainDepot) Then
    TempSupplier = "N"
ELSEIF MainDepot = "13" then
   ...

I frequently define my parameters as variants and test for NULL values in my 
functions, especially those that involve passing the values from fields that 
have some possibility of being NULL.

HTH
Dale

  -- 
Email address is not valid.
Please reply to newsgroup only.


"m.wanstall" wrote:

> Hi All,
> 
> I have started tearing my hair out over this problem!
> 
> I am pulling some data from a table and passing it as variables into a
> function in Access. One of the variables I'm passing through can be
> Null, so to avoid trying to pass a Null value to a function as a
> string (which I gather Access wouldn't like) I created an IIF function
> inside the parameters so if the value was null, it passed the string
> "NULLDEPOT" into the function, and I dealt with it inside the
> function.
> 
> No matter HOW I try (stacked queries, new variables, taking the IIF
> out of the function parameters and creating it earlier)...I always get
> an "#Error" value returned. Even when I set a breakpoint at the top of
> the function, Access won't trip it if that value was ever Null,
> however every other value works fine?!
> 
> I'll include my code below:
> 
> Example data being passed in is as follows:
> 
> Supplier("13","AG","S","JL884","NULLDEPOT")
> 
> Query code:
> =========================
> VendorCode: Supplier("13",[AGCECOM],[PMC],[Supplier
> Number],IIf([DepotID] Is Null,"NULLDEPOT",[DepotID]))
> =========================
> 
> VB Function Code:
> =========================
> Public Function Supplier(OrigDep As String, AgCE As String, PMC As
> String, VendNum As String, MainDepot As String) As String
>     Dim TempSupplier As String
> 
> ....snip...
> 
>     ElseIf OrigDep = "13" Then
>         If MainDepot = "NULLDEPOT" Then
>             TempSupplier = "N"
>         ElseIf MainDepot = "13" Then
>             TempSupplier = VendNum
>         Else
>             TempSupplier = "1400000"
>         End If
> 
> ....snip...
> 
>     Else
>         TempSupplier = "N"
>     End If
> 
>     ' AgCE and PMC Logic Final Override
>     If AgCE = "CE" Or PMC = "V" Then
>         TempSupplier = "ZZZ"
>     End If
> 
>     ' Return string
>     Supplier = TempSupplier
> 
> End Function
> =========================
> 
> Please HELP!!!
> 
> 
0
Utf
5/18/2007 12:51:02 PM
Reply:

Similar Artilces:

Function to find expired information
I am trying to create a function which will let me know when a particular date has passed or expired. For example, a person has insurance which expires on 4/30/06 - I want something that will let me know on 4/31/06 that that person's insurance is expired. Maybe also create a macro that will automatically filter all the expired's. Is there anyway to do this? I tried VLOOKUP, I tried an IF function, but I'm just not sure exactly how to write this. Thanks a million! take a look at conditional formatting: Select the column with the dates Format>Conditional Formatting ...

spin button value
Hi Does the value in spinbutton properties have to be numeric or can it be a day eg mon tues wed etc if so how do I dio this Thanks tina A Forms toolbar spinner control returns an integer. You can have another cell with a formula that references the cell linked to the spinner like =CHOOSE(A1,"Sun","Mon","Tues","Weds","Thurs","Fri","Sat") Here A1 is linked to the spinner. You'd want to limit the spinner's minimum value to 1 and maximum value to 7 in this case. -- Jim Rech Excel MVP "tina" <tina@...

if A1=Null and B1=A1 why is result in B1=0 ??
if A1=Null and B1=A1 why is result in B1=0 ?? because of this I get wrong result for average calculations: average for (6,8,0) <> average for (6,8,null) Use =IF(A1="","",A1) -- HTH RP (remove nothere from the email address if mailing direct) "WGeorg" <WGeorg@discussions.microsoft.com> wrote in message news:09FCCF25-D0DC-4B1E-BFA1-CEF9A9327C46@microsoft.com... > if A1=Null and B1=A1 why is result in B1=0 ?? > because of this I get wrong result for average calculations: average for > (6,8,0) <> average for (6,8,null) Use this ...

Null Is Null
I've got a text box in a report that's not returning properly. Here's its Control Source: =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]! [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]! [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"") This returns #Error. It's definitely my IIf statement as it returns properly when removed however there are times when [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this subreport will not appear if no records exist. I suspect that this is the problem. How can I write this Control Source t...

vlookup and if function
hiya all i'm trying to make 2 dropdown lists which when added with a figure in a 3rd cell copies the cell into another column. i.e a1=(dropdown"january") b1=(dropdown"paid") c1=("manual input"100) in g1 i want the total 100(c1) only if a1& b1 is set above. but what i also want is if a1=jan,b1=paid,c1=200 and g1 is full(>1) then enter 200(c1) into g2 please help. -- deejay On the face of it, this might suffice In G1: =IF(AND(A1="january",B1="Paid"),C1,"") In G2: =IF(G1=200,G1,"") -- Max Singapore http...

Syntax for Is Not Null ?
When one of our forms is opened (Current event) I want a message box fired by certain conditions (that the Ref control does have a value, but the Country control is empty). I have tried various bits of code along the lines shown below, but keep getting runtime errors. Wot's wrong with the following, please? If [Me.Ref] is not null and [Me.Country] = "" Then MsgBox "Please enter the country!" Many thanks CW CW - Try this: If (not isnull([Me.Ref])) AND IsNull([Me.Country]) Then MsgBox "Please enter the country!" -- Daryl S ...

When is a Null not a Null?
I have a form which is used for adding and entering data depending on where it is called from. When called in add mode (acFormAdd) a blank form is openned. When the cursor enters the text box [Name] on the form field I am trying test whether it is empty so that a search form can be openned if a new name is to be added. (I don't want the search form to open in cases when the form already has data in and I am editing rather than adding data). The form is based on a query and the data displayed in [Name] is the result of a calculated field in the query which concatenates firstname and surn...

IF Function Help, Please:)
I need the IF function to add up two cells and if the sum is greater than or equal to 80, then the cell needs to read "80," if the sum is less than 80 I just want it to show whatever number it calculates (79 and below). This is for a timesheet with overtime. Thank you for your help!! Use: =IF(Cell1+Cell2<=80,Cell1+Cell2,80) The syntax: =IF(some result equals or is less than 80, TRUE: the result, FALSE: show 80) "Pepper233" wrote: > > I need the IF function to add up two cells and if the sum is greater than or > equal to 80, then the cell needs to rea...

Passing Qry as part of Rpt DoCmd.OpenReport
If the selected manufacturer = "_All" first value in list, then my query sQry = "Year = " & ddlYear.Value else: sQry = "Year = & ddlYear.Value & " and manufacturer = " & ddlManu.Value .... also tried: "...=" & "'" & ddlManu.Value & "'" DoCmd.OpenReport sReportName, acViewPreview, , sQry Checking the value of sQry shows "Year = 2007 and manufactuer = 'TRMCO'" But I still get the same data set as "Year = 2007" When I query by pasting the same where clase into my dat...

What is the Null character?
What is the character sequence that Excel uses to represent an empty cell? I am using a formula and when it returns false, I would like for it to represent the cell as if it was empty. I have tried using "" as an empty cell, but excel recognizes that this is a value, not an empty cell. You can't do what you want to do. A formula always returns *something*. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Quinn Ramsey" <Quinn Ramsey@discussions.microsoft.com> wrote in message news:D67F6311-0CCA-48B5-A...

retrieving the XmlEnumAttribute and the enum-values (2)
This post is a followup to the thread "retrieving the XmlEnumAttribute values for an Enum" in this same forum earlier this month, since my last query in that thread went unanswered. I have an enum defined (DotNet v2) as public enum velocityUom { [System.Xml.Serialization.XmlEnumAttribute("m/s")] ms = 21, [System.Xml.Serialization.XmlEnumAttribute("cm/a")] cma, ... } My original post was to ask how I could retrieve the XmlEnumAttribute values for this Enum so that I could create a combo box with values like "m/s", &qu...

Passing Values between forms
I am trying to link to forms. I have a button on the main form (frmCompanies) that needs to open another form when a button is clicked. The new form that opens is frmScheduledActivities. When it open it needs to check to see if there is an existing record (scheduled activity) for the company and if there is no scheduled activity be set to add a new record. When the button is clicked on the main company form (on the on click event) this code runs: Private Sub cmdCallBack_Click() On Error GoTo Err_cmdCallBack_Click Dim stDocName As String Dim stLinkCriteria As String...

more on VBA function name change
I thought I'd start a new thread since I haven't received any replies to my first one... To recap: I've declared a function in a module using mixed case: Function TMDE_Category (FormName As Form) I noticed recently that it appeared in the module as Function tmde_category(FormName As Form) I changed it back to the mixed case declaration, saved the module, exited the app, reopened it and looked. The function had changed back to the all lowercase declaration. Things I've tried since the original post: Using the databse documenter, I selected all ob...

Simple EXCEL worksheet function
I am creating an EXCEL worksheet of people's names, company and location. I need to do a simple sort on name. When the list goes beyond 250, the sort function stops working. Is this a limitation of EXCEL? If so, can I continue the list on "Sheet 2", "Sheet 3" etc and still be able to sort through all the lists as if they are one? #1. Select the entire range you want to sort--don't let excel guess. I'm guessing that you have an empty row in the real range to sort and excel is guessing wrong. #2. Nope. Excel doesn't support this type of...

Charting null values
I have a chart that is plotting null values. I tried the formula =if(C25="",N/A(),Sum(C22:C25)). I've also deleted the formula to create a completely null cell. I've been to Tools, Options, Chart and selected Plot empty cells as Not plotted, zero and Interpollated As well as turning Plot Visible Cells only (turned it on and off) Nothing seems to make a difference. Any suggestions? Stephanie, what kind of chart are you using? Can you give an example of how your data table looks like? br, Henk "StephanieH" wrote: > I have a chart that is plotting ...

How to Link a cell in an Excel spread sheet to a text value in Visio 2007
I would like to have a text value in Visio take the value of a cell in an Excell spread sheet. Is there a way to do this, at least on opening the Visio Document? Thanks in advance, Marco UCO Lick Observatory Laboratory for Adaptive Optics visio 2007 professional http://office.microsoft.com/en-us/visio/HA100518191033.aspx al "Marco" <null@null.net> wrote in message news:uCcafQkuKHA.1796@TK2MSFTNGP02.phx.gbl... > I would like to have a text value in Visio take the value of a cell in an > Excell spread sheet. Is there a way to do this, at leas...

NULL values
Working with a downloaded table form a Lotus Notes contact manager. Now in access call tblclients. I query certain info. to update the records. BUT...'Is Null' does not work. It comes back empty and there are definitely empty contents in the fields I chose. I know how to do this as it works in my other tables on other databases. The only difference I can see in this table as opposed to tables created directly in Access is the Field Design property called Unicode Compression saying No instead of the default Yes. I changed the Unicode to Yes in case this had something to do with it and ...

How can I check a combo box content or value or something
Hi. I need to check if my combobox has something on it, what I mean is if my users choose any value. I need something looked like: if combo1 then msgbox "you must choose an option on Combo1" exit sub end if How can I do something looked like? Regards, Marco Marco, In the AfterUpdate event of the combo type, if IsNull(combo1.text) then msgbox "you must choose an option on Combo1" End If "Marco" <Marco@discussions.microsoft.com> wrote in message news:92B57DBB-74BC-4177-AB68-A87153654F34@microsoft.com... > Hi. I need to check if my combobox h...

How to MATCH value up a column
I need to match/find the first matching cell up a column. Ideas? TIA! Hi I don't know what you are looking for, but see: MATCH(A2,A1:F1,0) or VLOOKUP(A3,Sheet2!A:B,2,FALSE) -- Please click "yes" if this post helped you! Greatly appreciated Eva "DevourU" wrote: > I need to match/find the first matching cell up a column. Ideas? TIA! Thankx for the reply. Here is my formula:=IF(A11>A10,C10,(IF(A11=A10,A10,"?"))). I need to have IF(A11<A10, search up column A for 1st match, example A4, display next column B4. Clear as mu...

Taking the Mode based on cell value
Could you provide a few more details about what you want to do? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "sip8316" <sip8316@discussions.microsoft.com> wrote in message news:E3F4DE24-C5E8-4911-96E6-E61A1E56DEF0@microsoft.com... > ...

VLOOKUP: Retrieving Values
Vlookup: I am trying to lookup a value ( SCOTLAND ) on a spreadsheet called ( Data ) in order to populate ceratin cells on my other spreadsheet ( Spreadone ). If the value (SCOTLAND) is located in (Data) I want it to copy the values of specific cells (not all of them ) from the same row into ( Spreadone ). So : CHECK COLUMN N (CLASS) IN SPREADSHEET (DATA) FOR STRING (SCOTLAND) IF THE STRING = SCOTLAND THEN IN SPREADSHEET (Spreadone) POPULATE CELL A7:A25 = ACCOUNT NO (from Data) CELL B7:B25 = CLIENT (from Data) CELL C7:C25 = CUSIP (from Data) CELL D7:D25 = QTY (from Data) CELL E7:E25...

Using ISNA on more than one column within an If Function
Hi All, I'm trying to construct a formula that looks at the following section of data: A B C D #N/A less than 12mnths Not On List 12 to 18mnths #N/A 1 Essentially I want to show in Column D a value based on the following: Where the value in column C shows anything other than "not On List" is assigns "1" If however, the value shown is Not On List then it needs to lookup the value in Column A and return the corresponding value in the associa...

pass control on photo swap
Do you have to pass control on photo swap for the other person to download photos? If that's the case it would explain a lot. I would appreciate your help. Thanks. EggHeadCafe - Software Developer Portal of Choice Silverlight 3 RIA Services: DataForm, Validation http://www.eggheadcafe.com/tutorials/aspnet/0f7d1dda-3449-4f24-9fac-3d8dcaf5e8a3/silverlight-3-ria-service.aspx Greetings William, I assume you mean the MSN Photo Swap activity. In that activity, you do need to pass control for the other user to add pictures. There are also some instances where the 'Save' ...

Can I set an audio alert that triggers as Excel cell value chgs?
I would like to add an audio alert to a spreadsheet cell whenever it changes to a specific value? Can this be done? ....I'm not familiar with any audio alerts but, you can use conditional formatting to the cell's format change when it value changes. Go to Format...Conditional Formatting. You can then either set a value or use a formula and set the format (what it will look like when the cell is the value that you set). "SellUnHi" wrote: > I would like to add an audio alert to a spreadsheet cell whenever it changes > to a specific value? Can this be done? L...

PrimaryContactID NULL
Hello, i have discovered a strange behavior of CRM regarding the relationship between accounts and contacts: Some of my accounts show up a referenced contact but do not have a PrimaryContactID GUID in the databasefield. Both where converted from a lead which is refrenced with the correct GUID in the OriginatingLeadID Field in the database? Any idea how this can happen? Regards Sebastian When you convert a lead into an account/contact the contact references the account throught the parent customer field. The account does not reference the contact (this would be the primary contact fi...