A client's database has tables Or 1

[TrekBiker]

A client's database has tables Order Details and Products.

Part of the later code includes this

SELECT COUNT(OrderDetailID) as NotAllocated FROM [Order Details] od LEFT JOIN [Products] pd ON od.ProductID = pd.ProductID WHERE od.CasksAssigned < od.Quantity AND OrderID = OrderID AND pd.CaskType <> 'UU'

To try to see what's going on I copied this to the SQL window of a new query. Its Design View shows two new tables od and pd that are exactly like Order Details and Products.

What is happening?

 

[Andrzejek]

...FROM [Order Details] [highlight #FCE94F]od[/highlight] ...

[tt][highlight #FCE94F]od[/highlight][/tt] is an alias for [tt]Order Details[/tt] table.

I use aliases for my table names when the name is long.
And I hate long tables names.


---- Andy

There is a great need for a sarcasm font.

 

[TrekBiker]

Great, thanks, wasn't aware you could do this.

What I sent comes from this function, but I have little idea what it's doing, eg the parts including 'con'.
The objective is to be able to tell if a particular order has been dispatched.

Public Function isOrderDispatched(OrderID) As Boolean
    Dim con As Object
    Dim rs As Object
    Dim rs2 As Object
    Dim stSql As String

    Set con = Application.CurrentProject.Connection
    
    'first check if beer items exist on the order
    stSql = "SELECT COUNT(OrderDetailID) as BeerItems FROM [Order Details] od "
    stSql = stSql & " LEFT JOIN [Products] pd "
    stSql = stSql & " ON od.ProductID = pd.ProductID"
    stSql = stSql & " WHERE OrderID = " & OrderID
    stSql = stSql & " AND pd.CaskType <> 'UU'"
    
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open stSql, con
    
    If (rs.EOF) Then
        'no beer items on the order so not dispatched, user can edit the order.
        isOrderDispatched = False
    Else
        While (Not (rs.EOF))
            If rs![BeerItems] > 0 Then
            
                'if there are beer items on the order then  check if they are all dispatched.
                    stSql = "SELECT COUNT(OrderDetailID) as NotAllocated FROM [Order Details] od "
                    stSql = stSql & " LEFT JOIN [Products] pd "
                    stSql = stSql & " ON od.ProductID = pd.ProductID"
                    stSql = stSql & " WHERE od.CasksAssigned < od.Quantity "
                    stSql = stSql & " AND OrderID = " & OrderID
                    stSql = stSql & " AND pd.CaskType <> 'UU'"
                    'check order has beer items.
                    Set rs2 = CreateObject("ADODB.Recordset")
                    rs2.Open stSql, con
                    
                        While (Not (rs2.EOF))
                            If rs2![NotAllocated] > 0 Then
                                isOrderDispatched = False
                            Else
                                isOrderDispatched = True
                            End If
                            rs2.MoveNext
                        Wend
            Else
                isOrderDispatched = False
            End If
            rs.MoveNext
        Wend
    End If

    isOrderDispatched = False
 
End Function

 

[Andrzejek]

Well, from what I see, you have a Function named isOrderDispatched (good name) that accepts an argument OrderID declared as Variant (not such a good idea) and returns a Boolean value: either True or False.

Since OrderID Is a Number, I would declare your function like:[tt]
Public Function isOrderDispatched(ByRef OrderID As Long) As Boolean[/tt]

Another possible problem, in a few places you assign False or True to isOrderDispatched, but no matter what you do in this Function, you still do
[tt]isOrderDispatched = False[/tt]

At the end, over-writing any previous setting to False or True.



---- Andy

There is a great need for a sarcasm font.