MS Access Giving me trouble 1

[brainmetz]

Hello,

Here is what I am trying to do…Not sure if it is possible:



SELECT [Activity File].[Computer Name], Sum([Activity File].[Total Time]) AS [SumOfTotal Time] INTO [ATT/IBM URL's By Computer]

FROM [Activity File]

WHERE ((([Activity File].[URL Address]) Like "*att*" Or ([Activity File].[URL Address]) Like "*ibm*&quot)

GROUP BY [Activity File].[Computer Name];



Those lines will find att and ibm as a url address….That works fine, but what I also need is (logic code

If URL Address is not like att or ibm

Then URL Address is NULL




I was thinking the code should be something like this:



(if ((([Activity File].[URL Address]) Is Not = "*att*" Or ([Activity File].[URL Address])Is Not Like "*ibm*&quot) Then ([Activity File].[URL Address] = NULL))



but that doesn’t work.



I need that so each computer name will have something associated with it…What is being displayed now is only those computer names which att/ibm shows up for and the total time spent in those URL’s. I need it to say something like:



Computer1 10

Computer2 0

Computer3 11



It currently only displays:

Computer1 10

Computer3 11



And it leaves out computer2…is there anyway to make it display computer2 with a value of 0?



That query saves the output to a table called ATT/IBM URL’s by Computer.



Thanks for your help.

 

[dodge20]

I think that the problem here is with the = null part of your statement. If you are going to use = then use '0' if you want to use null, I think you have to use is instead of =

Dodge20

 

[Lightning]

Extract from the Access XP VBA Help file (Note: this function also exists in A2K and A97):

Nz Function

You can use the Nz function to return zero, a zero-length string (" &quot, or another specified value when a Variant is Null. For example, you can use this function to convert a Null value to another value and prevent it from propagating through an expression.

Nz(variant, [valueifnull])

If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string. If the optional valueifnull argument is included, then the Nz function will return the value specified by that argument if the variant argument is Null. When used in a query expression, the NZ function should always include the valueifnull argument,

If the value of variant isn't Null, then the Nz function returns the value of variant.

Remarks
The Nz function is useful for expressions that may include Null values. To force an expression to evaluate to a non-Null value even when it contains a Null value, use the Nz function to return zero, a zero-length string, or a custom return value.

For example, the expression 2 + varX will always return a Null value when the Variant varX is Null. However, 2 + Nz(varX) returns 2.

You can often use the Nz function as an alternative to the IIf function. For example, in the following code, two expressions including the IIf function are necessary to return the desired result. The first expression including the IIf function is used to check the value of a variable and convert it to zero if it is Null.

HTH
Lightning