?
Solved

How to assign a null value to SQL paramter

Posted on 2008-10-10
5
Medium Priority
?
1,000 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22687734
lngcmfpram = 0  

you declare    Dim lngcmfPram As Long
0
 
LVL 4

Author Comment

by:Thirt
ID: 22687757
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 22687761
if you want to assign a null value
declare
 Dim lngcmfPram

or

 Dim lngcmfPram As variant
0
 
LVL 4

Author Comment

by:Thirt
ID: 22687776
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
ID: 22687888
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

621 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