Thirt
asked on
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?
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
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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"
to read as the following if the user selected original (0)
" AND effort.change_index= null"
ASKER
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!
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!
you declare Dim lngcmfPram As Long