Solved

How to assign a null value to SQL paramter

Posted on 2008-10-10
5
984 Views
Last Modified: 2012-08-13
I have a legacy database app that I'm working on that handles estimated effort required for projects.  The original effort for the project is defined and approved. Then as the project progresses, Change of scope is tracked as a CMF#. So existing queries used by the application find original -vs- CMF effort is done using the "Change_Index" field.

effort.change_index=null (this represents original effort)

effort.change_index= 2102 (This represents a Change Number)

So now I'm working on a function to extract either original or CMF effort and running into a brick wall and need some help. If the users selects to see a CMF, the below code works fine. But I'm having trouble calling up the orginal effort. How can I make this flexible enough to pass a Long or null value in the SQL?
Private Function ProjectPositions(ByVal iEst As Long, iDiscID As Long, iProjectid As Long, lngCMFno As Long) As Variant

    Dim EstIDTemp As Long

    Dim varPositions As Variant

    Dim rs As DAO.Recordset

    Dim strSql As String

    Dim lngcmfPram As Long

    

    Const strcProcName As String = "ProjectPositions"

    on error GoTo ErrorHandler    

    ReDim varPositions(0)

   

    If iCMFno = 0 Then

        lngcmfpram = Null  <<<<<<<<<<< This fails as you cannot assign a null value to a long

    Else

        lngcmfPram = iCMFno

    End If

    

    strSql = "SELECT DISTINCT effort.position " & _

            "FROM (tasks INNER JOIN tblProjects ON tasks.project_id = tblProjects.fldprojectindexno) " & _

            "INNER JOIN effort ON tasks.task_id = effort.task_id " & _

            "WHERE tasks.estimate_id=" & iEst & _

            " AND tasks.discipline_code=" & iDiscID & _

            " AND tasks.project_id=" & iProjectid & _

            " AND effort.change_index=" & lngcmfPram & _    '<<<< need option to make lnfcmfPram = null

            " union select sposition as position " & _

            "FROM tbl_defaultpositions " & _

            "WHERE idiscipline = " & iDiscID

Open in new window

0
Comment
Question by:Thirt
  • 3
  • 2
5 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
lngcmfpram = 0  

you declare    Dim lngcmfPram As Long
0
 
LVL 4

Author Comment

by:Thirt
Comment Utility
Forgot to mention the ICMFno is set to 0 of the user selections the option to view Original Effort. Other wise they select the CMF number.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
if you want to assign a null value
declare
 Dim lngcmfPram

or

 Dim lngcmfPram As variant
0
 
LVL 4

Author Comment

by:Thirt
Comment Utility
but I need this line: " AND effort.change_index=" & lngcmfPram & _    '<<<< need option to make lnfcmfPram = null

to read as the following if the user selected original (0)
" AND effort.change_index= null"
0
 
LVL 4

Author Comment

by:Thirt
Comment Utility
ok, that allows me to assign null to lngcmfPram. Thanks.

But when the sql string gets built, this is the end result:
... AND tasks.project_id=2415 AND effort.change_index= union select...

So I guess I need to set it to:
lngcmfpram = "null"

That now builds the sql statement correctly. Now to make sure I didn't screw it up when a CMF numbers is passed.

Thanks again for helping me move forward!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now