Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to assign a null value to SQL paramter

Posted on 2008-10-10
5
Medium Priority
?
997 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

916 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