[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

access query

Posted on 2014-01-28
6
Medium Priority
?
248 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 53

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

656 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