"Like" condition query not returning the correct values

[dta87]

I am running a query that gives me a listing of various e-mail addresses tied to a specific project. I need to filter out all the ".com" addresses. If I run the original query and then do an "ends with" filter on the results it gives me the correct listing. If I put a "like" condition into the original query, it only returns a small subset of the listing (28 returns as compared to the 575 it should output). The field that stores the e-mail address is a hyperlink field if that makes a difference.

Is there another way to automate the "ends with" text filter into a query?

 

[boblarson]

Post the SQL of your query with the Like in there so we can see how you are trying to do it.

 

[BBQ Kittens]

Your SQL would help, but if you want to exclude anything ending in "com" then:
Place this in your criteria.....Not Like "*com"

 

[vbaInet]

Right(, 4) = "com"[/COLOR] [/B]would be what I would do.

 

[Brianwarnock]

Shouldn't that be
Right(,3)<>"com"
To select those not ending in com

Brian

 

[vbaInet]

Shouldn't that be
Right(,3)<>"com"
To select those not ending in com

Brian[/QUOTE]Cheers Brian, I missed out the dot, so it should be:

[B][COLOR=Blue]Right([Email], 4) = "[COLOR=Red].[/COLOR]com"[/COLOR][/B]

 

[Brianwarnock]

Yep you are correct to include the . But won't that find all those ending .com whereas he wants to filter them out which I take to mean not ending .com

A quick reread of the thread leaves me unsure now.

Brian

 

[raskew]

Hi -

You might try playing with this (from the debug window):

'*************************
x = "thisisatest.com"
y = "thisisatest.abc"
? left(x, instr(1, x, ".com")-1)
thisisatest
? left(y, instr(1, y, ".abc")-1)
thisisatest
'*************************

HTH - Bob

 

[vbaInet]

Yep you are correct to include the . But won't that find all those ending .com whereas he wants to filter them out which I take to mean not ending .com

A quick reread of the thread leaves me unsure now.

Brian

I think he's after viewing all those records ending with .com. His second sentence contradicts this but the rest of what he said indicates that he would like to view .com records.

 

[dta87]

Originally I was just trying to do a like "*.com" for the criteria but after some further investigation it was found there are some extra blank characters at the end for most of the e-mail addresses that is why it was returning a truncated listing. I ended up using this expression to automate my query " InStr(1,[fieldname],".com",2) " and it returns the correct listing. Thanks for all the help.

 

[vbaInet]

In that case you can use:

Right(RTrim([COLOR=Blue])[/COLOR], 4) = ".com"[/B]

 

[dta87]

In that case you can use:

Right(RTrim([E-mail]), 4) = ".com"

I tried this and because of the extra (hidden) characters this just returned the truncated list

 

[vbaInet]

What hidden characters? Why don't you identify them?

 

[dta87]

What hidden characters? Why don't you identify them?

I inherited the database so 99% of it has not been entered/developed by me. I tried the Right function with 4 as the string length and for 95% of the entries it returned "com#" or some variant of the last 4 characters. I am not sure where the # character is coming from since when I highlight the address I can only highlight through the .com. I am not sure why running the filter "ends with .com" after the query is complete returns the correct result but the "like" condition in the query does not. We went through multiple functions before we found the InStr function that ended up working.

Now that I think about it, I think when the person entered the address he copied it from an e-mail and pasted it into the database and somehow picked up the return character. Right now, I do not want to go through over a 1000 entries and repair this issue.

 

[vbaInet]

I'm just trying to get to the root of the problem rather than just concluding that a solution was found.

Perhaps the # is coming from a hyperlink field?

Give me an example of a raw e-mail address. I want to see what extra characters appear and where. A bogus e-mail address will do.

 

[dta87]

Yes it is a hyperlinked field. When I run the expression -- Right([e-mail],4)-- it returns "e-mail address" com# for all but 28 records. When I try to edit the hyperlink for the 28 records that do not show up with the "#" as the last character it does not have an mailto e-mail address linked to that cell. I cannot duplicate this (no assigned e-mail) in my test inputs. I am not sure if the originator changed the format of the field after he set up the database and didn't go through and update the previous entries or if it is something else.

FYI: I can't post e-mail addresses at this point

 

[vbaInet]

I mentioned in my last post that I don't want to see real e-mail addresses, I want to see a bogus e-mail address. I also mentioned the reason why.

 

[dta87]

I understand and I had bogus e-mails in my previous reply but this forum kicks back my post saying I am not allowed to post e-mails at this time (or e-mail without the hyphen)

 

[vbaInet]

Alright, I know why. You need at least 10 posts before you can post e-mail addresses or e-mails. You have 8 now so you know what to do

 

[boblarson]

Just zip a document and post. As long as it is zipped, it doesn't matter if you have 1 post or 10 posts.