Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Boolean Test Failing RS Records SQL

All,

I have a table that contains ITM_REF_NO, STATE_CD, PORT_OF_LOAD.

In some cases, a state (STATE_CD) may not have a PORT_OF_LOAD so the field in the table is blank, in other cases, there may be multiple PORTS_OF_LOAD.

I am trying to develop a Boolean test that checks the following:
A. Whether or not there are any PORT_OF_LOAD records at all;
B. Whether or not there is more than one UNIQUE PORT_OF_LOAD record;

At present, I am getting unstuck because the "Null" records are being counted in my recordset.

I have enclosed my code as of now.

Can anyone please help me with this?

Public Function OriginPortTest(uMAID As Long, uRefNo As Long) As Boolean
'function to test the number of port of origin records for an item
'if the test returns true, then the user will be allowed to nominate a origin port otherwise, the default port record will be used

    Dim rs As New ADODB.Recordset
    Dim conn As Connection
    Set conn = CurrentProject.Connection
    Dim strSQL As String
    Dim vCount As Integer
    Dim vPodRecordCount
        
    'Turn off warnings
    DoCmd.SetWarnings False

    strSQL = "SELECT DISTINCTROW MT_SKU_ANALYSIS.PORT_OF_LOAD " & _
    "FROM MT_SKU_ANALYSIS GROUP BY MT_SKU_ANALYSIS.PORT_OF_LOAD, MT_SKU_ANALYSIS.MAID, MT_SKU_ANALYSIS.ITM_REF_NO " & _
    "HAVING (((MT_SKU_ANALYSIS.PORT_OF_LOAD) Is Not Null) " & _
    "And ((MT_SKU_ANALYSIS.MAID) =" & uMAID & ") And ((MT_SKU_ANALYSIS.ITM_REF_NO) =" & uRefNo & ")) " & _
    "ORDER BY MT_SKU_ANALYSIS.PORT_OF_LOAD DESC"
    rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
    
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        If IsNull(rs.Fields("PORT_OF_LOAD")) = True Then
            OriginPortTest = False 'only 1 port of load record found
        Else
            OriginPortTest = True 'more than 1 port of laod record found
        End If
    Else
       OriginPortTest = False 'no port of load found at all
    End If
   
    'Debug.Print vPodRecordCount
    Set rs = Nothing
    DoCmd.SetWarnings True
    
End Function

Open in new window

0
creativefusion
Asked:
creativefusion
  • 2
  • 2
2 Solutions
 
BardobraveCommented:
Have you tried to add your "IS NOT NULL" clause to a WHERE before the GROUP BY instead of getting it into the HAVING clause?
0
 
peter57rCommented:
Seems extremely complicated sql for your stated requirements.

Although I'm not clear whether you want Null  PORT_OF_LOAD in your recordset or not.

If not then it seems to me all you need is:

strSQL = "SELECT DISTINCT MT_SKU_ANALYSIS.PORT_OF_LOAD FROM MT_SKU_ANALYSIS " & _
    "where MT_SKU_ANALYSIS.PORT_OF_LOAD Is Not Null " & _
    "And MT_SKU_ANALYSIS.MAID =" & uMAID & " And MT_SKU_ANALYSIS.ITM_REF_NO =" & uRefNo &  _
    "ORDER BY MT_SKU_ANALYSIS.PORT_OF_LOAD DESC"

If you want to include nulls then..

strSQL = "SELECT DISTINCT MT_SKU_ANALYSIS.PORT_OF_LOAD FROM MT_SKU_ANALYSIS " & _
      "where MT_SKU_ANALYSIS.MAID =" & uMAID & " And MT_SKU_ANALYSIS.ITM_REF_NO =" & uRefNo &  _
    " ORDER BY MT_SKU_ANALYSIS.PORT_OF_LOAD DESC"

I don't understand the logic being used in the subsequent vba code either, but that's a different issue.




0
 
creativefusionAuthor Commented:
@ BardoBrave

Not as yet. Are you suggesting doing a seperate query first.

@ peter57r

I used your 1st SQL and it still returns a record count of 2. One with a value, one with a null.

Here is the intended logic behind the function:

If the number of unique PORT_OF_LOAD records = 1 Then
result = false 'only 1 port record exists
Else
result = True 'more than 1 port exists
End If

In terms of your last comment, don't knock it, the code is in development and I am a newbie at VB so patience please...

CF



0
 
BardobraveCommented:
This is the query I'm suggesting to avoid null records.

SELECT DISTINCTROW MT_SKU_ANALYSIS.PORT_OF_LOAD
FROM MT_SKU_ANALYSIS
WHERE MT_SKU_ANALYSIS.PORT_OF_LOAD IS NOT NULL
GROUP BY MT_SKU_ANALYSIS.PORT_OF_LOAD, MT_SKU_ANALYSIS.MAID, MT_SKU_ANALYSIS.ITM_REF_NO
HAVING (((MT_SKU_ANALYSIS.MAID) =" & uMAID & ")
And ((MT_SKU_ANALYSIS.ITM_REF_NO) =" & uRefNo & "))
ORDER BY MT_SKU_ANALYSIS.PORT_OF_LOAD DESC
0
 
peter57rCommented:
"I used your 1st SQL and it still returns a record count of 2. One with a value, one with a null. "

Then the 'Null' is not actually null; it must be an empty string.

To exclude records which are null or empty then use..

strSQL = "SELECT DISTINCT MT_SKU_ANALYSIS.PORT_OF_LOAD FROM MT_SKU_ANALYSIS " & _
    "where nz(MT_SKU_ANALYSIS.PORT_OF_LOAD,'') <>''  " & _
    "And MT_SKU_ANALYSIS.MAID =" & uMAID & " And MT_SKU_ANALYSIS.ITM_REF_NO =" & uRefNo &  _
    "ORDER BY MT_SKU_ANALYSIS.PORT_OF_LOAD DESC"

In   LOAD,'') <>''       each " is two ' not a "

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now