Caclulated qry column cannot group

[joeKra]

Hi,
i am trying to group the following Query to find duplicates with no success any help will be appreciated


Base Query:

SELECT AccountActivity.AccountID, Mid([details],InStr(1,[details],"-")-1,4) AS FetchedCat, elookup("Neighborhood","Combo","Category='" & [FetchedCat] & "'") AS Neighborhood
FROM AccountActivity
WHERE (((Mid([details],InStr(1,[details],"-")-1,4)) Like "?-??"));

Find Duplicates Query:

SELECT AccountsWithNeighborHood.[AccountID], AccountsWithNeighborHood.[FetchedCat], AccountsWithNeighborHood.[Neighborhood]
FROM AccountsWithNeighborHood
WHERE (((AccountsWithNeighborHood.[AccountID]) In (SELECT [AccountID] FROM [AccountsWithNeighborHood] As Tmp GROUP BY [AccountID] HAVING Count(*)>1 )))
ORDER BY AccountsWithNeighborHood.[AccountID];

 

[plog]

...with no success...

That's a pretty ambiguous term. Don't tell us what you aren't getting, tell us what you are getting--error message, invalid data? If its invalid data, how do you know its invalid? What do you expect vs. what did you get?

The first thing I see is that you have a call to a function called 'elookup' in your base query? Is that a typo? Should that be DLookup?

The second thing I see is that inside that elookup you are using a field called 'FetchedCat' which you just calculated inside that same base query--sometimes that works, sometimes it doesn't. In either case its a bad practice to use a calculated field inside another calculated field in the same query statement.

Third, unrelated to your issue, but most likely an error in your table layout : Why are you calculating 'FetchedCat' in the first place? Discrete pieces of data should be in their own fields. Based on your calculation of 'FetchedCat', it is a distinct piece of data inside the 'details' field. That's a no no, you should store it in its own field in a table.

It may be best just to post sample data from AccountActivity and then what data you expect to ultimately be returned.

 

[joeKra]

1. Elookup is similier to Dlookup From allen browne http://allenbrowne.com/ser-42.htmlclaimed to be faster


2. i tried also with using the actual code of 'Fetchedcat' but the same error occurred

3.The reason why i use 1 field instead of separating all pieces because that's the way how i designed it when i was new to access and it was the easiest to implement....otherwise i would've create multiple fields and i didn't like having 30 fields in 1 table,

While preparing the DB i found that i have a category field in AccountActivity Table which i can use the get those info. with left function (includes 2 categories the old and the new one, separated with semi-colon) and it works, but if you still want to look into i want to understand why it didn't worked with mid function, the error was 'invalid procedure Call'

thanks.

 

[CJ_London]

why it didn't worked with mid function

This will be one reason (referring to your criteria)

Mid([details],InStr(1,[details],"-")-1,4)

If [details] does not contain a "-" or it is the first character then the instr will return 0 or 1 respectively, from which you then subtract 1 which will give you -1 or 0 respectively. Both of which will give the Mid function palpitations.

Another possibility is Details contains less than 4 characters or is null so the Mid function will fail again

A possible fix is

WHERE iif(InStr(1,[details],"-")>1 and Len([details]>3,Mid([details],InStr(1,[details],"-")-1,4),"") Like "?-??"