UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Replace Query Wildcard Question    
 
   
MFS3000
post Dec 2 2011, 08:14 AM
Post #1

UtterAccess Member
Posts: 35
Hi,
I'm sure this is easy and I'm just overlooking something but I'm trying the use an update query with a replace statement to remove part of the data in a field but am having no luck...

I have a column with this type of data:

Column Name = test
RGRR.N0000
EWES.W0014
YUY.P0022
UYUI.N0001

After running the query I'd like to data to just be:
RGRR
EWES
YUY
UYUI

In the update to field I have Replace([test],".*","") - thinking this would remove anything after the dot in the data but it doesn't seem to work...

Thanks for any pointers you can offer in where I'm gonig wrong....
Go to the top of the page
 
+
Doug Steele
post Dec 2 2011, 08:29 AM
Post #2

UtterAccess VIP
Posts: 17,620
From: Don Mills, ON (Canada)
Try
CODE
Left([test], InStr([test], ".") - 1)


If there's a chance that there might be entries without the period, try
CODE
IIf(InStr([test], ".") > 1, Left([test], InStr([test], ".") - 1), [test])
Go to the top of the page
 
+
MFS3000
post Dec 2 2011, 08:37 AM
Post #3

UtterAccess Member
Posts: 35
Works a dream! Thanks for your help!
Go to the top of the page
 
+
« Next Oldest · Access Queries · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 21st May 2013 - 12:12 AM