Solved

How to assign a null value to SQL paramter

Posted on 2008-10-10
5
988 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 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

856 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