[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

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

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
gigifarrow
Asked:
gigifarrow
  • 3
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
gigifarrowAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
check this, see query1
Barcode.accdb
0
 
gigifarrowAuthor Commented:
Excellent! Thankyou

How would I do it to separate in three places?


57K66740245999

Part          Serial   Cage
57K6674  0245     999
0
 
Rey Obrero (Capricorn1)Commented:
If the information in the [data] field is of the same number of characters

Field: right([Data],3)


Append To Cage
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!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now