Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

access query

Posted on 2014-01-28
6
Medium Priority
?
252 Views
Last Modified: 2014-01-28
I would like to find strings that do not start with any letter from a to z in the thrid position

JS13G111 would need the 3 removed
JS16G111 woul;d need the 6 removed and so on

not sure how to do that.

am working in query grid of access 365
0
Comment
Question by:PeterBaileyUk
  • 3
  • 2
6 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 39814403
You may try this:

Needed: Left([TestField],3) & Right([TestField],4)

Sincerely,

Ed
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 39814406
This may not be the solution you want but this is what I think the quickest approach. See the image attached.

Sincerely,

Ed
sample.jpg
0
 

Author Comment

by:PeterBaileyUk
ID: 39814420
flipping the test cases this:
this                        became this
JS1BG112        JS1G112

in this case it should have done nothing

JS16R7DB    

Instead of removing the character could it just flag the issue

JS16R7DB  so it would say here

numeric exists in 4th position
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39814423
Hi,

pls try

Expr: IIf(Not (IsNumeric(Mid([Field1],4;1))),[Field1],Left([Field1],3) & Right([Field1],Len([Field1])-4))

Open in new window

Regards
0
 

Author Comment

by:PeterBaileyUk
ID: 39814429
I was jsut experimenting with similar: Expr3: IsNumeric(Mid([expr1],4,1))
0
 

Author Comment

by:PeterBaileyUk
ID: 39814437
corrected the small typo ";" in mid but works a treat thankyou
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question