Solved

Boolean Test Failing RS Records SQL

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now