#error results in unmatched records of outer join - Error handler code

#error results in unmatched records of outer join - Error handler code

Post by strive4pea » Mon, 12 Jun 2006 00:24:02


Hi Steve,

Don't give up on UDFs -- you really need more checking than
you can do in an equation. Before you send the value to the
UDF, make sure it is not null. Then, in the UDF, put error
checking in before your calculation so you do not use string
manipulation on null values and use an error handler

ColumnName: IIF(
Isnull([expression]),
0,
ParseWI([expression])
)

I also noticed you used the first (optional) parameter of
Instr -- if it is 1, you do not need to specify that

'~~~~~~~~~~~~~~~~~~~~~~
dim mPos as integer
mPos = Instr(parametername," " )
if mPos > 0 then

'~~~~~~~~~~~~~~~~~~~~~~


ERROR HANDLER CODE:

put this at the top of your program
'~~~~~~~~~~~~~~~~~~~~~~

On Error GoTo Proc_Err

'initialize function
FunctionName = 0 'default value

'~~~~~~~~~~~~~~~~~~~~~~

put this at the end of the program

'~~~~~~~~~~~~~~~~~~~~~~

FunctionName = calculated value

Proc_Exit:
On Error Resume Next
'release object variables if any
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit

'~~~~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
 
 

1. Where to put error handler code for "No current record" error?

2. Left outer join returns results like inner join?

I'm really struggling with a query:

SELECT TOP (100) PERCENT dbo.tblBlogCategories.blogCategoryName,
COUNT(dbo.tblBlogCounter.blogCounterID) AS Count,
dbo.tblBlogCategories.blogCategoryID,
dbo.tblBlogCategories.blogViewType
FROM dbo.tblBlogCategories LEFT OUTER JOIN
dbo.tblBlogCounter ON
dbo.tblBlogCategories.blogCategoryID = dbo.tblBlogCounter.blogCategoryID
WHERE (dbo.tblBlogCounter.blogCounterDate > GETDATE() - .001) OR
(dbo.tblBlogCounter.blogCounterDate > GETDATE() - .001)
GROUP BY dbo.tblBlogCategories.blogCategoryName,
dbo.tblBlogCategories.blogCategoryID, dbo.tblBlogCategories.blogViewType
HAVING (dbo.tblBlogCategories.blogViewType = 1) OR
(dbo.tblBlogCategories.blogViewType = 4)
ORDER BY dbo.tblBlogCategories.blogViewType DESC,
dbo.tblBlogCategories.blogCategoryName

I would like this query to return all results from the left table and show
resutls from the right table that have dates old than a certain value.

The moment I add the getdate clause it will only return matched results like
an inner join, any ideas how I force it to return all the results from the
left table as well?

Many thanks for any help.

3. Distributed FULL OUTER JOIN results in INNER join

4. is there a ' combination join' of left outer and right outer join

5. MS SQL 2000 LEFT OUTER JOIN and RIGHT OUTER JOIN problem?

6. When is an outer join not an outer join?

7. More on Join Type Issues - How to find Orphan Records w/o Outer Joins ?

8. Code or Pseudo-code for implementing OUTER JOIN

9. Joining two sets of INNER JOINs with an OUTER JOIN

10. left join/inner join/outer join with repeat values

11. Hex Error Code In Error Handler

12. SP4 and error Query contains an outer-join request that is not per

13. SQL outer join syntax error

14. LEFT OUTER JOIN ERROR

15. Outer Join Error - Please help