Queries :: Return Characters To The Right Of Space

Apr 19, 2015

I want to return the characters to the right of the space but I'm getting back rubbish.

This is the type of data in the field AccountName

Z-Yachting Barcos y Apartamentos S.L 031
Zusi Ltd ST5
Leaders BR3

And this is the result I want

031
ST5
BR3

What I'm getting is:

os S.L 031
ST5
ers BR3

This is my current formula.

Right([AccountName],InStr(1,[AccountName]," ")-1)

View Replies


ADVERTISEMENT

Reports :: Remove Characters Before 1st Space

Mar 11, 2013

What forumla could I use in a report field to remove all characters before the first space in a text field e.g.

32A Bank Street --> Bank Street
19 Alder Street --> Alder Street

I've tried =Right([JLADD1],Len([JLADD1])-2) but this obviously doesn't work for the first example above.

View 5 Replies View Related

General :: Not Enough Space For Comments (Limited To 255 Characters)

Sep 18, 2013

We have created a database where data entry happens on the first form. So far all is working well on this form except users complain there is not enough space for comments. I set up a column for comments, but it is limited to 255 characters. What/How else can I set this up to provide a lot more room?

View 1 Replies View Related

Query To Return Records Containing 2 Characters In A Field

Sep 5, 2013

I have a field in Access 2010 called Section Number which generally holds two characters. The characters can be numbers, letters, or a combination. Examples are:

01
X1
KA
40

Sometimes this field holds three characters, such as:

01a
02b

I want to write a query that returns only the Section Numbers that contain two characters. The instances where the field holds three characters are too numerous and changeable to use a parameter that says, for example: not "01a" and not "02b"

Is there a way to write a parameter that means "not Section Numbers of three characters" or "not Section Numbers of more than 2 characters"?

View 2 Replies View Related

General :: Return Based On Criteria - Records With Less Than 5 Characters Or Blank

Aug 28, 2013

I have a table with Zip CODE field. I uploaded my information to this table form an excel file. There were over 120K rows of data so no way i could do this in excel.

So I found that some of the ZIP codes are either blank or have less than 5 digits.

Can I build a a query with the ZIP code field that will return all zip code records with less than 5 characters or blank. I'd like to repair these zip codes and a update query will be my next step.

I repeat excel is not an option because when I try to sort by the Zip Code column my computer explodes (okay not literally) ....

View 3 Replies View Related

Queries :: Get All Text To Right Of First Left Space

Mar 21, 2014

how to find the first space on the left of a text string, for example:I have this string : FUJIFILM XM-1 16-50mm BLACK and I want to find the space between FJUJIFILE and XM and then get all the text from XM onwards.I have this expression that gets me so far but then I'm stuck:

Code:

Model: Left([strDescription],InStr(1,[strDescription]," ")-1)

This expresssion gets me FUJIFILM, which isn't want I want.

View 6 Replies View Related

Queries :: Postcode Extraction When There Are Double Space On Some And Not Others

Feb 4, 2015

Any way to extract a postcode when there are double space on some and not others.

SA1 1AB (i need SA1 1AB)
HU1 2AB (i need HU1 2AB)
HU17 0BG (ok)

For some reason the postcode field returns 2 spaces when the postcode length is only 6 digits and 3 spaces when the postcode length is only 5 digits. Im assuming that the postcode field needs to be 8 characters in length (including a space).

All I want to to remove the addition spaces and return a normal looking postcode!

View 11 Replies View Related

Queries :: Command To Select First 30 Characters

May 7, 2013

I have the command to select first 30 characters in a simple select query.

View 3 Replies View Related

Queries :: How To Remove First 7 Characters In A Field

Dec 24, 2013

explain me the Query Expression to remove the first 7 characters in a column, to rum a Query.Eg - I have entry like - 4/8/11-Pipe...I need to return the result like "Pipe".How to build the query.

View 5 Replies View Related

Queries :: Replace 2 Characters In String With 0s

Dec 8, 2014

I'm familiar with writing a replace query to replace characters or strings but what I'm trying to do this time is a bit unique to me. I have a string of numbers that will either be 8 or 9 digits in length. The first 1 or 2 digits will be the State code (1-50 hence the discrepancy in number of total digits), then a 2 digit agency code, then the last 5 digits are a producer code. What I need to do is change the 2 digits for the agency part both to 0. So basically characters 6 and 7 if you're counting from the right. To me it feels like I'm trying to do it backwards so I'm having a hard time writing it.

View 12 Replies View Related

Queries :: Extract Text Between Certain Characters

May 14, 2014

I have a field that contains the following data:

FirstName:Pedro LastName:Campos Restaurant:BI Strand Location:North Month:March

I need to extract the data into different fields like this:

Field1: Pedro
Field2: Campos
Field3: BI Strand
Field4: North

How to extract the information I want into different fields

View 1 Replies View Related

Queries :: Transform Number Of Characters In A Field

Mar 28, 2014

I have a table with acct numbers of various lengths, ie:

246
18
36895
352119
472

I need to run a query so that all of the acct numbers are 8 characters long with zeros as placeholders in front of the actual acct numbers, ie:

00000246
00000018
00036895
00352119
00000472

I tried using the expression NewAcctNo: 00000000+[AcctNo], but that did not fix the problem.

View 2 Replies View Related

Queries :: Checking For Special Characters In Descriptions

Mar 12, 2015

I want my to detect special characters in my descriptions that are not found on keyboard and display as error.
Tried using the following

Description check: IIf([Common Description] Is Null Or [Common Description] Not Like "*[!a-z0-9@=.^_$%!#&'`(){|}*?~[]/-]*" And [Description Local] Is Null Or [Description Local] Not Like "*[!a-z0-9@=.^_$%!#&'`(){|}*?~[]/-]*","<<Error Desc>>","OK")

but when i tested it using some data, it shows all as <<Error Desc>>

some of the special characters i want to check for are βuΩ etc.

So if my description contains characters that are not: a-z OR 0-9 or any of the following ~!@#$%^&*()_+=-`][';/.,<>?:"{}|~

it will return <<Error Desc>>

View 4 Replies View Related

Queries :: Left Function With 2 Static Characters

Jul 23, 2015

I have to query a record with 2 of the same static characters. "-".I can get the left function with the first " but I can't get the rest up to the second "-"

Example: B-4352B-PXP02W01-10

TagNo: Left([EventInstanceID],InStr([EventInstanceID],"-")) Brings me the first char plus "-" The next section is needed also but the remainder is not.

Needed: B-4352B

Is there a Mid Function that I could add to go to the second chaacter instead of stopping at the first one?

View 6 Replies View Related

Queries :: Search Field For Special Characters

Oct 22, 2014

I am trying to query on a field for any that contain special characters. How can I accomplish this? I do not know what special characters could be in this field, so I would want to query for ANY special character.

View 2 Replies View Related

Queries :: Alpha Characters In Numeric Field

Feb 6, 2014

I have been downloading .csv files from a construction website that we use to following projects as they develop. I import the file into Excel and then want to bring it into my database. The issue is with two fields I have that are numeric, however, when downloaded into Excel, the information in the two fields now have an (')added to the beginning and end of the string, i.e. and the second field has an (') and (-) to the data.

'201400409710'
'201300697683'

The second field is a zipcode field that comes in like this:

'14063-1127'
'14222-1004'

I would like to design a query to strip the ' from each field as well as the (-) in the zipcode field.If I have to write 2 separate queries that is fine.

View 4 Replies View Related

Queries :: Replacing Wildcard Characters In A Query

Jun 5, 2013

I am trying to replace a # character and all characters after in a text field.

U set up a Select query and used the following expression:
Expr1: Replace([Part Number],"[#]*","")

It doesn't change any of the records. Is this possible to do in Access 2007?

View 11 Replies View Related

Queries :: Possible To Query A Field For Special Characters In A String?

Oct 11, 2013

Is it possible to query a field for special characters in a string? I want to find any records that have characters other than A-Z, 0-9

View 9 Replies View Related

Queries :: Strange Characters Displayed In Query Results

Sep 5, 2014

I have a query that is linked to a data table. The values entered in the data table are fine and display as expected. When I run the query, the results of one of the columns show Chinese like characters instead of the actual value. But, when I filter that column by choosing 'does not contain...' the actual cell contents display. I have 2 other identical queries reading from the same table and they do not display funky.

View 3 Replies View Related

Queries :: Way To Group Model Numbers Once 1st 6 Characters Are Identical

Jan 30, 2015

In my transaction table, I would like to find out the latest transaction dates of each family models. It sounds a simple Max function can get the desired result. However model number consists of the first 6 characters for the family model and the rest for the versions (variants). The Max function fails to work in this scenario. Is there any other way to group the model numbers once the 1st 6 characters are identical (the rest is ignored)?

View 3 Replies View Related

Queries :: Memo Append Query Cutting At 255 Characters

Apr 11, 2013

memo fields chopping the content when appending to another table memo field.I have a query that takes in a couple of tables and then appends the content to one larger table.In each of the smaller tables there is a memo filed that when I check it has the entire type content in above 255 without a problem.

When the query runs and appends it to the larger table the content in the memo filed is chopped down.Both tables fields are set to memo I have double checked this.I have seen some comments about unique values in the query properties but these are set to NO as advised but it still does this.

View 4 Replies View Related

Queries :: Make Table Query - Remove Characters

Jun 26, 2013

I have a current file with GBP 12.00. I wish to remove the GBP in a make table query when ran to print off.

View 2 Replies View Related

Queries :: Removing Special Characters Where Original Data Length Varies

Mar 1, 2014

I thought I had solved my initial problem of removing the apostrophe character for each zip code field. Most of the fields have data that reads '12345-1234'. I want to remove each (') character and the "-1234" so I end up with a zipcode of 12345.

I created 2 queries for this process. I first run a query with the following statement "Left([ZIP],Len([ZIP])-6)". That gets rid of the "-1234' ".

The I run the second query with this statement "Right([ZIP],Len([ZIP])-6)".

These 2 queries work perfectly if the original zipcode is " '12345-1234' ", but if it is " '12345' ", the entire zipcode is deleted.

I have attached 3 (.jpg)'s to show you what I am talking about.

View 7 Replies View Related

Queries :: Return One Value Or Another

Nov 20, 2013

I am looking up a product based on a unique code in tblProducts...I need to return one of two values

If the value of [Product type] = "multi" then return "multi"
else
If the value of [Product type] = "solo" then return the value of the non empty field in a range of fields

for example

tblProducts
PID (key) | PACKAGE TYPE | RED | YELLOW | BLUE
C13T0714010 | single | <empty>| yellow | <empty>
C13T0754010 | multi | red | yellow | blue

query
C13T0714010 returns "yellow'
C13T0754010 returns "multi'

View 13 Replies View Related

Queries That Return No Records

Jan 31, 2006

Hi All,

I am wandering if it possible to check programatically whether a query returns a NULL result?

I am writing a program(VB) that involves an IFF statement where if there are records in the query it does a specific calculation & if there are no records returned in the query it does something else. Is this possible to achieve?

Thanks in advance:D

Cheers,

Karv

View 2 Replies View Related

Queries :: MIN (00) Or MAX Value To Return In Same Column

Jun 10, 2015

I am having an issue with a select query as I want one column to show the MAX and "00" value of the same Column. How do I go about having this task completed.

For example, the column has 00,01,02,03,04,05,*1,*2,*3,*4,*5 so I want a query that will return values of "00" and "05" on this example
00
05

Further example, this is what I have now

SELECT qryPR34_UNION.PR34001, qryPR34_UNION.PR34002, qryPR34_UNION.PR34003, qryPR34_UNION.PR34005,
qryPR34_UNION.PR34006, qryPR34_UNION.PR34007, qryPR34_UNION.PR34013, qryPR34_UNION.PR34014,
qryPR34_UNION.PR34098, qryPR34_UNION.PR34099, qryPR34_UNION.PR34100, qryPR34_UNION.PR34101
FROM qryPR34_UNION
WHERE (((qryPR34_UNION.PR34002)>"0") AND ((qryPR34_UNION.PR34003)>"0")
AND ((qryPR34_UNION.PR34006)<"0") AND ((qryPR34_UNION.PR34099)>0));

On the WHERE command, I need field PR34005 to give me the "00" value or the MAX value (on my example the result will have to give me records that have "00" and "05" value assigned on PR34005

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved