Solved

Using the Split function in a query keep getting error Invalid dot or ! operator but those operators arent there.

Posted on 2013-11-26
5
516 Views
Last Modified: 2013-11-26
I have data in a field that I need to append but I only want to append part of the field. When I try to append the field I get a error invalid dot or ! operator or invalid parentheses. The data in the field is 57K66740245.
Im trying to delete the rest of the string 0245 and just leave this "57K6674".Example
Split("57K6674", "0")(1)
Barcode.accdb
0
Comment
Question by:gigifarrow
  • 3
  • 2
5 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39678069
Split() function does not work in a query..


you need to create a function in a regular module to do the splitting


try this format

Field : Left([Data],Instr([Data],"0")-1)
table

Append to PartNumber
0
 

Author Comment

by:gigifarrow
ID: 39678101
Thank you for your help. Are you saying to try this for my query or to put this in a module.

When I try it in a query I get datatype Mismatch. Left([57K6674],InStr([57K6674],"0")-1)


How would I make a module to append the query with this criteria?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39678161
check this, see query1
Barcode.accdb
0
 

Author Comment

by:gigifarrow
ID: 39678472
Excellent! Thankyou

How would I do it to separate in three places?


57K66740245999

Part          Serial   Cage
57K6674  0245     999
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39678558
If the information in the [data] field is of the same number of characters

Field: right([Data],3)


Append To Cage
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

696 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