By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

SUBSTRING equivalent in Access

code green
Expert 100+
P: 1,726
I want to compare two text fields and return records where they differ. But I need to ignore the last 15 characters of the first string. Is there an equivalent to this function in Access
Expand|Select|Wrap|Line Numbers
  1. SUBSTRING(field1,-1,15).
May 11 '07 #1
Share this Question
Share on Google+
9 Replies


Expert
P: 97
I don't know if there's an equivalent, but I usually use Left(), Right(), Mid(), Len() and InStr() to construct string searches
So for your example it would be something like:
Left(string1, len(string1)-15)
May 11 '07 #2

ADezii
Expert 5K+
P: 8,638
I want to compare two text fields and return records where they differ. But I need to ignore the last 15 characters of the first string. Is there an equivalent to this function in Access
Expand|Select|Wrap|Line Numbers
  1. SUBSTRING(field1,-1,15).
Expand|Select|Wrap|Line Numbers
  1. Dim strTest As String, strReturnString As String
  2. strTest = "Philadelphia is the city of brotherly love"
  3.  
  4. If Len(strTest) > 15 Then
  5.   strReturnString = Left$(strTest, Len(strTest) - 15)
  6. Else
  7.   strReturnString = vbNullString
  8. End If
  9.  
  10. Debug.Print strReturnString
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. Philadelphia is the city of
May 11 '07 #3

code green
Expert 100+
P: 1,726
Thanks for the help but unfortunately I've hit another problem. The string functions are returning a type mismatch error. This seems to be because the fields in question are memo fields that contain apostrophes. I can't change the text. Is there a way around this?
May 11 '07 #4

ADezii
Expert 5K+
P: 8,638
Thanks for the help but unfortunately I've hit another problem. The string functions are returning a type mismatch error. This seems to be because the fields in question are memo fields that contain apostrophes. I can't change the text. Is there a way around this?
Try uing the Variant Form of the Functions, namely Left, Right, Mid, etc.
May 11 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for the help but unfortunately I've hit another problem. The string functions are returning a type mismatch error. This seems to be because the fields in question are memo fields that contain apostrophes. I can't change the text. Is there a way around this?
Memo field are a bit of a nightmare. Are you sure the problem is being caused by apostrophes? Memo fields often get cluttered with paragraph and line down characters. These are more likely to be causing your problem. If it's just a case of apostrophes being in one string and not the other then you could run a replace function to get rid of them. There are a number of solutions but more information on the problem is required.

Mary
May 12 '07 #6

code green
Expert 100+
P: 1,726
I am developing the query as a SELECT statement to test the UPDATE. I have tried this
Expand|Select|Wrap|Line Numbers
  1. SELECT product.[Full description], product.sReportDescription
  2. FROM product
  3. WHERE LEFT(REPLACE(product.[Full description],"'",""),Instr(REPLACE(sReportDescription,"'",""),"<br><br>")-1)
  4. <> REPLACE( sReportDescription,"'","");
And getting the message
Expand|Select|Wrap|Line Numbers
  1. Data Type mismatch in Criteria Expression
. I was getting this error before I tried REPLACE on the apostrophe. I have no idea what is in the memo fields. Carriage returns, HTML, and other junk probably.
May 14 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
I am developing the query as a SELECT statement to test the UPDATE. I have tried this
Expand|Select|Wrap|Line Numbers
  1. SELECT product.[Full description], product.sReportDescription
  2. FROM product
  3. WHERE LEFT(REPLACE(product.[Full description],"'",""),Instr(REPLACE(sReportDescription,"'",""),"<br><br>")-1)
  4. <> REPLACE( sReportDescription,"'","");
And getting the message
Expand|Select|Wrap|Line Numbers
  1. Data Type mismatch in Criteria Expression
. I was getting this error before I tried REPLACE on the apostrophe. I have no idea what is in the memo fields. Carriage returns, HTML, and other junk probably.
Replace() is not available in the WHERE Statement.

Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT product.[Full description], product.sReportDescription,
  2. REPLACE(product.[Full description],"'","") As D1, 
  3. REPLACE(sReportDescription,"'","") As D2
  4. FROM product
  5. WHERE LEFT(D1,Instr(D2,"<br><br>")-1) Not Like D2;
  6.  
<> often won't work with strings you should use Not Like
May 14 '07 #8

code green
Expert 100+
P: 1,726
OK. Thanks mmccarthy. Using NOT LIKE now. REPLACE not available in WHERE? Thought I'd used that in MySql. Anyway I took the whole query apart and rebuilt it bit by bit. I tried casting the memo fields to Cstr which worked. But have managed to make it work without casting the memo fields.The following query compares the two memo fields ignoring the text from !!< <br><br> in product.[Full description] (about 15 characters). Thanks kepston using your string function suggestions. Considered ADezii VBA macro idea.

Expand|Select|Wrap|Line Numbers
  1. SELECT product.[Full description], product.sReportDescription
  2. FROM product
  3. WHERE TRIM(sReportDescription) NOT LIKE TRIM(LEFT(product.[Full description],
  4. (InStr(product.[Full description],"!!< <br><br>")-1)))
  5. AND INSTR(product.[Full description],"!!< <br><br>") <> 0
  6. AND INSTR(product.[Full description],"!!< <br><br>") IS NOT NULL
May 14 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
OK. Thanks mmccarthy. Using NOT LIKE now. REPLACE not available in WHERE? Thought I'd used that in MySql. Anyway I took the whole query apart and rebuilt it bit by bit. I tried casting the memo fields to Cstr which worked. But have managed to make it work without casting the memo fields.The following query compares the two memo fields ignoring the text from !!< <br><br> in product.[Full description] (about 15 characters). Thanks kepston using your string function suggestions. Considered ADezii VBA macro idea.

Expand|Select|Wrap|Line Numbers
  1. SELECT product.[Full description], product.sReportDescription
  2. FROM product
  3. WHERE TRIM(sReportDescription) NOT LIKE TRIM(LEFT(product.[Full description],
  4. (INSTR(product.[Full description],"!!< <br><br>")-1)))
  5. AND INSTR(product.[Full description],"!!< <br><br>") <> 0
  6. AND INSTR(product.[Full description],"!!< <br><br>") IS NOT NULL
Glad you got it working. Access is funny about which functions it will allow you to use in Criteria.

Mary
May 14 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.