> Are the following all number fields?
> Quantity,
> Products.ItemCode, and
> CurrentStock.ItemCode
>
>
> "Simon Glencross" <simon.glencross[ at ]sg.consultancy.co.uk> wrote in message
> news:OBrS%23iQ0GHA.4116[ at ]TK2MSFTNGP02.phx.gbl...
>> Have check that again and still getting data tpye missmatch in expression
>>
>> SELECT Sum(currentstock.Quantity) AS QuantityAcq FROM currentstock INNER
>> JOIN products ON products.itemcode = currentstock.itemcode WHERE
>> ((currentstock.itemcode = 3));
>>
>> Should I post further information on tables which are configured?
>>
>> I am at a loss!!
>>
>>
>>
>> "John Spencer" <spencer[ at ]chpdm.edu> wrote in message
>> news:%23XhK3QQ0GHA.4816[ at ]TK2MSFTNGP06.phx.gbl...
>>> Assuming those are TWO SEPARATE queries.
>>>
>>> This one looks good.
>>> SELECT TOP 1 StockTakeDate, Quantity
>>> FROM tblStockTake
>>> WHERE itemcode = 3
>>> ORDER BY StockTakeDate DESC;
>>>
>>> This one has a bad table name in the on clause (is it productS or
>>> product?) The on clause had Product with the Join clause had Products.
>>>
>>> SELECT Sum(currentstock.Quantity) AS QuantityAcq
>>> FROM currentstock INNER JOIN products
>>> ON products.itemcode = currentstock.itemcode
>>> WHERE currentstock.itemcode = 3;
>>>
>>>
>>> "Simon Glencross" <simon.glencross[ at ]sg.consultancy.co.uk> wrote in
>>> message news:OoJ3mxP0GHA.1040[ at ]TK2MSFTNGP06.phx.gbl...
>>>> Ok cleared that one, but I now have a syntex error in innerjoin, here
>>>> is the debug info, any ideas?
>>>>
>>>>
>>>> SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake WHERE ((itemcode
>>>> = 3)) ORDER BY StockTakeDate DESC;
>>>> SELECT Sum(currentstock.Quantity) AS QuantityAcq FROM currentstock
>>>> INNER JOIN products ON product.itemcode = currentstock.itemcode WHERE
>>>> ((currentstock.itemcode = 3));
>>>>
>>>>
>>>>
>>>>
>>>> "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_canada.com> wrote in
>>>> message news:esSJJWP0GHA.1256[ at ]TK2MSFTNGP02.phx.gbl...
>>>>> The implication is that you've either mistyped the name of a field in
>>>>> your SQL string, or else that ProductID is a text field, and Access is
>>>>> having problems with the number being passed to it without quotes
>>>>> around it.
>>>>>
>>>>> Immediately before the line of code
>>>>>
>>>>> Set rs = db.OpenRecordset(strSQL)
>>>>>
>>>>> put
>>>>>
>>>>> Debug.Print strSQL
>>>>>
>>>>> Look in the Immediate Window to make sure that the SQL looks valid.
>>>>>
>>>>>
>>>>> --
>>>>> Doug Steele, Microsoft Access MVP
>>>>>
http://I.Am/DougSteele>>>>> (no e-mails, please!)
>>>>>
>>>>>
>>>>> "Simon Glencross" <simon.glencross[ at ]sg.consultancy.co.uk> wrote in
>>>>> message news:O50hDMP0GHA.1288[ at ]TK2MSFTNGP03.phx.gbl...
>>>>>> Thanks Allen,
>>>>>>
>>>>>> I have just done that and I am setting a run-time error 3061
>>>>>>
>>>>>> Too few parameters.Epected1.
>>>>>>
>>>>>> Highlighting this part of the code....
>>>>>>
>>>>>> Set rs = db.OpenRecordset(strSQL)
>>>>>> With rs
>>>>>> If .RecordCount > 0 Then
>>>>>> strSTDateLast = "#" & Format$(!StockTakeDate,
>>>>>> "mm\/dd\/yyyy") & "#"
>>>>>> lngQtyLast = Nz(!Quantity, 0)
>>>>>> End If
>>>>>> End With
>>>>>>
>>>>>> Any ideas?
>>>>>> "Allen Browne" <AllenBrowne[ at ]SeeSig.Invalid> wrote in message
>>>>>> news:uG5UHCP0GHA.2196[ at ]TK2MSFTNGP03.phx.gbl...
>>>>>>> Presumably you have verified that Access understands the code (by
>>>>>>> choosing Compile from the Debug menu, in the code window), and
>>>>>>> tested it by opening the Immediate Window (Ctrl+G) and entering:
>>>>>>> ? OnHand(99)
>>>>>>> using some valid product number in place of 99.
>>>>>>>
>>>>>>> Once you have it working, if you have a form with a field named
>>>>>>> ProductID, you can add a text box to your from, and set its Control
>>>>>>> Source property to:
>>>>>>> =OnHand([ProductID])
>>>>>>>
>>>>>>> --
>>>>>>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>>>>>>> Tips for Access users -
http://allenbrowne.com/tips.html>>>>>>> Reply to group, rather than allenbrowne at mvps dot org.
>>>>>>>
>>>>>>> "Simon Glencross" <simon.glencross[ at ]sg.consultancy.co.uk> wrote in
>>>>>>> message news:uR$NPFN0GHA.1300[ at ]TK2MSFTNGP05.phx.gbl...
>>>>>>>> Ok I an new to this and need a little help....
>>>>>>>>
>>>>>>>> I have customised the following code but I dont know how to
>>>>>>>> implement in to the access form, do I put the code behind a command
>>>>>>>> button? Any help is much appreciated.
>>>>>>>>
>>>>>>>> Function OnHand(vProductID As Variant, Optional vAsOfDate As
>>>>>>>> Variant) As Long
>>>>>>>> 'Purpose: Return the quantity-on-hand for a product.
>>>>>>>> 'Arguments: vProductID = the product to report on.
>>>>>>>> ' vAsOfDate = the date at which quantity is to be
>>>>>>>> calculated.
>>>>>>>> ' If missing, all transactions are
>>>>>>>> included.
>>>>>>>> 'Return: Quantity on hand. Zero on error.
>>>>>>>> Dim db As DAO.Database 'CurrentDb()
>>>>>>>> Dim rs As DAO.Recordset 'Various recordsets.
>>>>>>>> Dim lngProduct As Long 'vProductID as a long.
>>>>>>>> Dim strAsOf As String 'vAsOfDate as a string.
>>>>>>>> Dim strSTDateLast As String 'Last Stock Take Date as a
>>>>>>>> string.
>>>>>>>> Dim strDateClause As String 'Date clause to use in SQL
>>>>>>>> statement.
>>>>>>>> Dim strSQL As String 'SQL statement.
>>>>>>>> Dim lngQtyLast As Long 'Quantity at last stocktake.
>>>>>>>> Dim lngQtyAcq As Long 'Quantity acquired since
>>>>>>>> stocktake.
>>>>>>>> Dim lngQtyUsed As Long 'Quantity used since stocktake.
>>>>>>>>
>>>>>>>> If Not IsNull(vProductID) Then
>>>>>>>> 'Initialize: Validate and convert parameters.
>>>>>>>> Set db = CurrentDb()
>>>>>>>> lngProduct = vProductID
>>>>>>>> If IsDate(vAsOfDate) Then
>>>>>>>> strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"
>>>>>>>> End If
>>>>>>>>
>>>>>>>> 'Get the last stocktake date and quantity for this product.
>>>>>>>> If Len(strAsOf) > 0 Then
>>>>>>>> strDateClause = " AND (StockTakeDate <= " & strAsOf &
>>>>>>>> ")"
>>>>>>>> End If
>>>>>>>> strSQL = "SELECT TOP 1 StockTakeDate, Quantity FROM
>>>>>>>> tblStockTake " & _
>>>>>>>> "WHERE ((ProductID = " & lngProduct & ")" &
>>>>>>>> strDateClause & _
>>>>>>>> ") ORDER BY StockTakeDate DESC;"
>>>>>>>>
>>>>>>>> Set rs = db.OpenRecordset(strSQL)
>>>>>>>> With rs
>>>>>>>> If .RecordCount > 0 Then
>>>>>>>> strSTDateLast = "#" & Format$(!StockTakeDate,
>>>>>>>> "mm\/dd\/yyyy") & "#"
>>>>>>>> lngQtyLast = Nz(!Quantity, 0)
>>>>>>>> End If
>>>>>>>> End With
>>>>>>>> rs.Close
>>>>>>>>
>>>>>>>> 'Build the Date clause
>>>>>>>> If Len(strSTDateLast) > 0 Then
>>>>>>>> If Len(strAsOf) > 0 Then
>>>>>>>> strDateClause = " Between " & strSTDateLast & " And
>>>>>>>> " & strAsOf
>>>>>>>> Else
>>>>>>>> strDateClause = " >= " & strSTDateLast
>>>>>>>> End If
>>>>>>>> Else
>>>>>>>> If Len(strAsOf) > 0 Then
>>>>>>>> strDateClause = " <= " & strAsOf
>>>>>>>> Else
>>>>>>>> strDateClause = vbNullString
>>>>>>>> End If
>>>>>>>> End If
>>>>>>>>
>>>>>>>> 'Get the quantity acquired since then.
>>>>>>>> strSQL = "SELECT Sum(currentstock.Quantity) AS QuantityAcq "
>>>>>>>> & _
>>>>>>>> "FROM currentstock INNER JOIN products ON
>>>>>>>> product.itemcode = currentstock.itemcode " & _
>>>>>>>> "WHERE ((currentstock.itemcode = " & lngProduct & ")"
>>>>>>>> If Len(strDateClause) = 0 Then
>>>>>>>> strSQL = strSQL & ");"
>>>>>>>> Else
>>>>>>>> strSQL = strSQL & " AND (tblAcq.AcqDate " &
>>>>>>>> strDateClause & "));"
>>>>>>>> End If
>>>>>>>>
>>>>>>>> Set rs = db.OpenRecordset(strSQL)
>>>>>>>> If rs.RecordCount > 0 Then
>>>>>>>> lngQtyAcq = Nz(rs!QuantityAcq, 0)
>>>>>>>> End If
>>>>>>>> rs.Close
>>>>>>>>
>>>>>>>> 'Get the quantity used since then.
>>>>>>>> strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS
>>>>>>>> QuantityUsed " & _
>>>>>>>> "FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
>>>>>>>> "tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
>>>>>>>> "WHERE ((tblInvoiceDetail.itemcode = " & lngProduct &
>>>>>>>> ")"
>>>>>>>> If Len(strDateClause) = 0 Then
>>>>>>>> strSQL = strSQL & ");"
>>>>>>>> Else
>>>>>>>> strSQL = strSQL & " AND (tblInvoice.InvoiceDate " &
>>>>>>>> strDateClause & "));"
>>>>>>>&g