"Like" condition query not returning the correct values

[dta87]

Here you go

[EMAIL="xtest.xtest@gmail.com"]xtest.xtest@gmail.com com[/EMAIL]#

xtest2@yahoo.com com#



This is the result of the query with the Right(,4) function. Like I said the 28 records that return the correct ".com" do not have a mailto: association in the edit hyperlink field. the other 580 records do.[/FONT]

 

[vbaInet]

Can you write them on separate lines please.

 

[boblarson]

Can you write them on separate lines please.

That is ONE email address. The problem is that it is in there as URL and not as TEXT.

Oops, nevermind I didn't see the two distinct domains. But it does look like what would be in a URL field when copied as it uses the # to store the display value as well as the actual value.

 

[dta87]

I edited my previous post

 

[boblarson]

dta87 - you wanna know how to best deal with this? Change the HYPERLINK field datatype to TEXT and don't store as a hyperlink. Store it as TEXT and then you can simulate a hyperlink at any time you need it. But hyperlink data types are a pain in the xxx.

 

[vbaInet]

I would expect the Hyperlink field to look like this in text form:

#xtest2@yahoo.com#

instead of this

xtest2@yahoo.com#

 

[dta87]

I would expect the Hyperlink field to look like this in text form:

#xtest2@yahoo.com#

instead of this

xtest2@yahoo.com#

Yes that is what I get when I change the field to a text format.

Boblarson's post on the display value explained why I am getting the #'s with the Right(,4) function but I am not sure if I want to change the field from a hyperlink to a text field just yet. With the amount of data (almost 4 years) I do not want to cause any conflicts in the relationships/queries that he has built.[/FONT]

 

[boblarson]

Maybe this would work instead:

NewFieldName:Right(Replace([FieldNameHere],"#",""),4)

criteria

".com"

 

[vbaInet]

Yes that is what I get when I change the field to a text format.

Boblarson's post on the display value explained why I am getting the #'s with the Right(,4) function but I am not sure if I want to change the field from a hyperlink to a text field just yet. With the amount of data (almost 4 years) I do not want to cause any conflicts in the relationships/queries that he has built.[/FONT][/QUOTE]Alright! Bob's code will do it but with slight tweak:
[code]
NewFieldName:Right(Replace([FieldNameHere][COLOR=Red] & ""[/COLOR],"#",""),4)
[/code][COLOR=Red]That [/COLOR]will ensure that it's converted to string before the replacement is performed.