Web Development

Ask something
Discussion Locked

Access difficulty

0 helpful
4 replies

by ozi eagle

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,
I have written a small lookup program in Access to extract data from a commercial program also in access.
The lookup is to determine the consignee (customer)of second hand school uniforms.
The way it works is that S/H items are tracked by using serial numbers, which are the customer Id number plus a - and a number, to allow for several items of the same type, as consignee. The program works well, but due to human cussedness the consignee's name is often entered or the number is wrong format.
I'm trying to create an exception report to give a listing of all serial numbers that don't correspond to ANY customer Id.
Have tried criteria of "<>Customer ID", however, that gives me 100s of thousands of results, because every customer Id is not the same as all the other customer Ids.
Unfortunarely the customer Id is of varying length 3 to 5 characters and also of the format 12/9876. Valid serial Nos should look like this:-

123-7
54321-5
12/9876-2

Wrong entries look like this:-
123 - 9
54321 -3 (which actually works because the space
left when the last 2 digits are
stripped is ignored in the lookup)
Joe Bloggs -1

As I'm not a programming guru a fairly straight forward idea would be appreciated.
TIA.

4 replies

by timfox

Part 1 of 2

Hi
(first - a bit of FYI)
Exception Reporting is not as straight forward as you might have hoped - because it requires you to determine specific goals of what exceptions you want to report on.
Once you have defined your criteria - create seperate queries for each, you can join them later (in a union query) - if required.

Basically - the more exception criterias you can define - the better the result.

You need to build a list of customer id values that appear in the Sold SH item values and compare the list with a list of known customer id codes.
I can explain how to get the list of customer id values from the sold SH item values - but that's where I'll stop. You can use the Unmatched Query Wizard to then get what you actually need.

The customer id value (of the sold SH item value) needs to be specicifally determined. This determination comes from looking at the way your data is structured (and entered).
It appears as though the best method is:
Start of CustID is start of string, where the first character is >=0 and <=9
End of CustId is (by stepping through each character and stopping at the first character that is not >=0 and <=9.

These statements (the determination) is required to help build the list of customer id values in the sold SH item values.
This is best done by writing a public function (copy the function code below to a module in your Access application) that you can call from a standard Access query.

The function is in the next post...

by ozi eagle

Thanks,
Though I didn't use all of it, the suggestion about Unmatched Queries did the trick

by timfox

Part 2 of 2

The function is as follows:

Public Function strGetCustID (strSoldSHValue as String) as String
On Error GoTo GetCustID_Err

Dim strOneChar as String
Dim intStringPos as Integer
Dim strCustID as String

intStringPos = 1
strCustID = ""

stronechar = Left(strSoldSHValue,intStringPos)
While strOneChar >=0 and strOneChar <=9
strCustID = strCustID & strOneChar
intStringPos = intStringPos + 1
stronechar = Left(strSoldSHValue,intStringPos)
Wend

strGetCustID = strCustID
Exit Function

GetCustID_Err
GetCustID = -1
End Function

In your query screen, you'll have a query that (in SQL) looks like this:
SELECT strGetCustID([tblSoldItems].[soldItem]) AS CustIDfromSoldSHitems
FROM [tblSoldItems]
Save this query (eg. qryCustIDsFromSoldSHitems)

Then use Access's "Fund Unmatched Query Wizard to generate your list of values that are in qryCustIDsFromSoldSHitems, but not in the known customer id table.

FYI - I haven't tested any of this - so it might need small adjustments here and there etc, but I think it looks ok...Good Luck.

This discussion is closed to new replies.