Solved

Boolean Test Failing RS Records SQL

Posted on 2011-03-16
5
217 Views
Last Modified: 2012-05-11
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
Comment
Question by:creativefusion
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 19

Expert Comment

by:Bardobrave
ID: 35146046
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
 
LVL 77

Expert Comment

by:peter57r
ID: 35146111
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
 

Author Comment

by:creativefusion
ID: 35146388
@ 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
 
LVL 19

Assisted Solution

by:Bardobrave
Bardobrave earned 50 total points
ID: 35146599
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
 
LVL 77

Accepted Solution

by:
peter57r earned 450 total points
ID: 35146726
"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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

630 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