Solved

Boolean Test Failing RS Records SQL

Posted on 2011-03-16
5
214 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
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

839 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