Solved

How to assign a null value to SQL paramter

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Filter cbo on datasheet - 11 32
VBA Access 2016 syntax 6 41
DSN-LESS connection to MS Access database 6 29
Need help constructing a conditional update query 16 43
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

776 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