Check up query

[samotek]

I want to build a check up query that checks two fields, the so called Saldo ad Stock for each affiliate and after that to issue a message enumerating the affiliates where the figures in Saldo and Stock do not match.Can you help?
My idea is to check for each office, but how to do it in the code ?

Public Function BatchCheckUp() ' check the difference between Saldo and Stcok for each office and at
'the end make alist with the offices where there is a discrepancy.

Dim StrSQL As String
Dim StrOffice As Integer
Dim StrItems As Integer

' cary out the check up for each office

' First office
StrOffice = 1
StrItems = 0

' Second office
StrOffice = 2
StrItems = 1


StrSQL = " SELECT qryCrosstab.ProductID, Sum(qryCrosstab.[-1]) AS [SumOf-1], Sum(qryCrosstab.[0]) AS SumOf0, [SumOf-1]-[SumOf0] AS Saldo, products.items6 AS stock" & _
" FROM qryCrosstab INNER JOIN products ON qryCrosstab.ProductID = products.Productid " & _
" WHERE (((qryCrosstab.afid) = = " & InOffice & "))" & _
" GROUP BY qryCrosstab.ProductID, products.items= " & InItems & ";"
CurrentDb.Execute StrSQL

End Function
But how should the query check and send the message ?
There could be several products for an office (affiliate) for which Saldo doesn't equal Stock.But in my case i need to build a quick check up from bird's view and to establish in which offices something is not in order. Regarldess if one or more products are concerned.We need to eliminate the offices where everything is ok.And, we expect, in the future everything will be OK, so our employees will save time only by clicking on the button and geting the information that everything is Ok ( hopefully).

 

[samotek]

I would reformulate my question in another way : Can I create a message from sql ? I want to create a message saying Attention ! The sql was already mentioned:

StrSQL = " SELECT qryCrosstab.ProductID, Sum(qryCrosstab.[-1]) AS [SumOf-1], Sum(qryCrosstab.[0]) AS SumOf0, [SumOf-1]-[SumOf0] AS Saldo, products.items6 AS stock" & _
" FROM qryCrosstab INNER JOIN products ON qryCrosstab.ProductID = products.Productid " & _
" WHERE (((qryCrosstab.afid) = = " & InOffice & "))" & _
" GROUP BY qryCrosstab.ProductID, products.items= " & InItems & ";"
CurrentDb.Execute StrSQL

End Function

My question is, can I create a message with the message box, when the saldo is not equal to the stock ?

 

[MD5150]

The query results *IS* your notification message. And there are several ways that you can display this notification. Viewing the results itself is one way.

If you have a query containing two fields that you need to compare, you can evaluate those two fields by adding a third expression field that looks at the values in both fields and returns a result which flags each record with the appropriate flag. It can be an IF statement. Or if the comparison requires complex or lengthy logic, you can create a user-defined function that returns the result back to the query.

I've never heard of anyone running a msgbox from within a sql statement. Normally, you would use the sql for returning a recordset used for viewing. Or alternatively, using code, you can run your sql statement and cycle through its returned recordset displaying messages as your code compares the values in the two fields. Look up "Looping through recordsets".

If you're not experienced with this, your best bet is probably to use an expression field in a query using a simple 1=True/0=False flag.

For example, if you have two fields in a table named Cost1 and Cost2. You can add a third field containing an expression with the following:

MatchFlag: IIf([cost1]=[cost2],1,0)

Here, "1" is returned if there is a match. You can further filter the results using "1" as your criteria showing only the matches to your users.

Using this in code, you can show the results if the returned matched recordset count is greater than 0.

HTH,
Mike Dorthick