Forumla to get part of the string - Microsoft Access / VBA

Piotr

Hi,
Im trying to write a query which takes part of the string for example:

peter_Poland
michael_Germany

I wish to get output:
peter
michael

regards
Piotr

Nov 25 '05 #1

Subscribe Reply

4238

Allen Browne

Type somehing like this into the Field row in your query:
Left([Field1], Instr([Field1], "_") -1)
replacing Field1 with the name of your field.

To avoid the error for fields that have no underscore, enter this into the
Criteria row as well
Like "?*_*"

(That expression almost looks like a piece of ASCII-art.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Piotr" <ho***@wp.pl> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

Hi,
Im trying to write a query which takes part of the string for example:

peter_Poland
michael_Germany

I wish to get output:
peter
michael

regards
Piotr

Nov 25 '05 #2

Mike Preston

On 24 Nov 2005 23:55:50 -0800, "Piotr" <ho***@wp.pl> wrote:

Hi,
Im trying to write a query which takes part of the string for example:

peter_Poland
michael_German y

I wish to get output:
peter
michael

regards
Piotr

If strName is the holder for the above names, then using:

mid(strName,1,i nstr(1,strName, "_")-1)

will give you the first name.

Note that this will only work as long as the underscore character
actually exists in the strName holder. If it doesn't, the function
will give you an error. A way around it, if that is a possibility, is
to do:

Iif(instr(1,str Name,"_")=0,"", mid(strName,1,i nstr(1,strName, "_")-1))

but this isn't needed if your data always has an underscore in it.

mike

Nov 25 '05 #3

Trevor Best

Piotr wrote:

Hi,
Im trying to write a query which takes part of the string for example:

peter_Poland
michael_Germany

I wish to get output:
peter
michael

regards
Piotr

IOW you want the part of the column before the "_" ?

Left(fieldname, instr(1,fieldna me,"_")-1)

Nov 25 '05 #4

Piotr

Im keep on trying do this query and I still get statement error ?

Exam: Left([net_hal],instr(1,[net_hal],"_")-1)

What can be wrong ?

Im trying tp use InStr alone and doesnt work also ?
Could be no such function in my access ?

Nov 25 '05 #5

Piotr

wow thank its working I had to put ; instead of ,

Nov 25 '05 #6

Similar topics

53511

System.IO.DirectoryNotFoundException: Could not find a part of the path

by: Matthias Ludwig | last post by:

I'm trying to create a directory on the web server with a vb.net code: .... Dim dirName As String = "w:\filepath\images" If Not Directory.Exists(dirName) Then Directory.CreateDirectory(dirName) Label1.Text = "Directory created" Else

.NET Framework

4512

Could not find a part of the path Error (System.IO.__Error.WinIOEr

by: Alex | last post by:

Hello, in my company, we have a diagnostic tool for hardware. Depending on the hardware projcet, a different project configuration is needed. The configuration file is written in XML and contains information for the visualization and storage of the retrieved data. From mid july till now, i have made 4 different configuration sets and everything was fine. Today, i got the order to make a new configuration set. When the xml file was...

.NET Framework

1567

How to delete the duplication part in a string

by: ad | last post by:

I have a string like string myString="dog,cat,dog,tiger" I want to delete the duplication part, and make myString to "dog,cat,tiger" How can I do that?

C# / C Sharp

5316

sys.web.mail err: The requested body part was not found in this message

by: SMG | last post by:

Hi All, My Send mail system was working very fine, today suddenly it started giving me following error. Can any one suggest why this is happening so. I am using system.Web.Mail "The requested body part was not found in this message. " Note : here XXXX and YYYYYYY are company specific info and nothing related with code.

ASP.NET

2643

Function needed - String Part(Source,Section)

by: Ima Loozer | last post by:

OK folks here is what I need help with. Lets assume I have a text field that will contain AlphaNumeric data. There is no set pattern to the field such that any given character can be either alpha or numeric. I need a function that will return the requested 'part' of the contents of the field. A 'part' would be defined as consecutive Alpha or Numeric characters in the text field. Examples: Field Name = cFoo

Microsoft Access / VBA

7118

Need help on Java Inventory Program Part 5

by: south622 | last post by:

I'm taking a beginning Java course and I'm stuck in week eight of a nine week course. If anyone could help me I would greatly appreciate it. This assignment was due yesterday and each day I go past the due date 10% of the grade is taken off. I think I'm coming down with the flu and my brain is just not processing this assignment. Here is the assignment: Modify the Inventory program by adding a button to the GUI that allows the user to move...

Java

1170

Mime part numbering

by: bill | last post by:

IMAP_FETCHBODY requires the "part number" to fetch. Can anyone point me at how the part numbers are derrived. for example, here is the formatted parts array from a message: Parts: array(2) { =object(stdClass)(12) { =int(0) =int(0) =int(1)

PHP

1557

How Do I find a part of a record content ..???

by: wassimdaccache | last post by:

I'm using this code to search a value on a continuous form it is working properly Am I able to write for example part of the string Dim strCriteria As String Dim rst As DAO.Recordset Set rst = Me.RecordsetClone strCriteria = " = " & Me! rst.FindFirst strCriteria If rst.NoMatch Then

Microsoft Access / VBA

1350

regexp help on part match

by: Andrew Poulos | last post by:

I have a string that looks like this "cmi.interactions.fred.id" I need to test that: - the first part of the string is "cmi.interactions." (case sensitive). - the last part of the string is ".id" (case sensitive). - that there's 1 or more characters between the 2nd and third dots (in the above example its "fred") - that there's only 3 dots in the whole string

Javascript

10618

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10366

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

10371

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9187

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7649

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6877

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5678

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4329

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3008

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General