How to assign a null value to SQL paramter

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

LVL 4
ThirtAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
if you want to assign a null value
declare
 Dim lngcmfPram

or

 Dim lngcmfPram As variant
0
 
Rey Obrero (Capricorn1)Commented:
lngcmfpram = 0  

you declare    Dim lngcmfPram As Long
0
 
ThirtAuthor Commented:
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
 
ThirtAuthor Commented:
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
 
ThirtAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.