Finding Field With Biggest Number Of Characters

Feb 26, 2008

I have a table with about 20.000 records.
From this table I would like to "search" in a column the field that holds the text with the most characters.

Is there a way to find this with a formula or expression?

View Replies


ADVERTISEMENT

Query Field For Number Of Characters

Nov 1, 2007

I need to query a field for any records where this field has less that 10 characters. Any suggestions? The reason is depending on the type of customer the field can be 10 or 11 digits. IF there are less than 10 but not blank they need to be pulled for correction.

View 1 Replies View Related

Count Number Of Characters In Numeric Field

Aug 24, 2006

Does anyone know the syntax to count the number of characters in a numeric field?

If not, how about counting the number of characters in a string?

View 5 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

Finding Alphanumeric Characters In Column

Oct 24, 2005

Hi guys,

Im using Access

i got this problem:

I have a list of street numbers, eg 1, 4, 8, 33, 44, and also some that contain some alphanumeric characters: eg: 21A, 21B, 21C.

I need to find out how to determine if a string has an alphanumeric character or not. I have used the access help but it is very vague and also Ive googled. One answer I found was to write a module, but Im not sure if thats the best way.


Please help as Im really stuck.

thanks in advance.

View 1 Replies View Related

Need Help Finding Lower Case Characters

Dec 14, 2006

hello if any one can help me create a query that pick out lower case characters from the beggining of words, and the beggining of each word in a sentance , my christmas would be made!



Thanks in advance

View 2 Replies View Related

Need Help Finding Lower Case Characters

Dec 14, 2006

hello if any one can help me create a query that pick out lower case characters from the beggining of words, and the beggining of each word in a sentance , my christmas would be made!



Thanks in advance

View 13 Replies View Related

Picking Biggest Default

Mar 21, 2006

I have a table with ID1, ID2, and AMT.

IF ID2 is NULL, I want to update it with the non-null ID2 where the ID1 matches and has the largest sum of AMT.

For instance

ID1 ID2 SUM(AMT)
1 333 1000
1 444 3000
1 <null> 5000


In this case, the third ID2 would get the value of 444 since it had the highest amt.

I'm guessing this involves sub-queries, and I have a real blind spot where they are concerned.

Thanks,
David

View 3 Replies View Related

Extracting First 2 Characters From VAT Number

Dec 20, 2005

Hi,

I have a long list of customers / suppliers and their VAT numbers. I would like to create a query that shows only the customer/supplier number and the first 2 characters (countrycode) from their VAT number. for example

Supplier 3 VAT number NL80980808
endresult should show:
3 NL

Is there anyone that can help me out??

View 1 Replies View Related

Queries :: Max Not Showing Only Biggest Date

May 13, 2015

I have 3 Tables with relationships between Pupils to Read and Books to Read:

Pupils ID, Forename, Surname
Books - BookID, Book, Author, Level, Genre
Read - ID, BookID, DateOut

On another thread in the forum I got to the position of the code below. However this code does not select only the read book with the biggest(latest) date, the Max, it displays all Read books.

SELECT Pupils.ID, Max (Read.DateOUT) AS MaxOfDateOut Books.Book
FROM (Books) RIGHT JOIN (Read) ON Books.BookID = Read.BookID) RIGHT JOIN Pupils ON Read.ID = Pupils.Id
GROUP BY Pupils.Id, Books.Book
HAVING (((Books.Book)<>"Blank"));

View 14 Replies View Related

Limiting The Number Of Characters Displayed

Apr 12, 2006

Hello,

I'm trying to limit the number of text characters displayed in various fields on a form. I want it to appear so that after a certain number of characters it then displayed "..." indicating there is now information in a field.

Thanks for any help!

View 1 Replies View Related

Limiting The Number Of Characters In A Text Box

Jun 13, 2007

I Have Created A Client Reference Allocation Screen Which Makes The Clients Reference From Details In A Number Of Text Boxes.

For Example:

Client Number: 0010, Forename: Andrew, Surname: Bloggs, Year: 2007,
Occupation: Driver.

Client Reference = 0010ab-dr07

I Have Copied The Text Boxes So The Information Is Automatically Displayed To Create The Client Reference, But Rather Than Shortening The Copied Text Boxes To Show Only The First Few Characters Is There A Way Of Limiting The Number Of Characters Shown In The Copied Text Boxes??

Matt

View 4 Replies View Related

Queries :: Finding Latest Number Using A Query

Jun 19, 2013

I need to find the latest number using a query.

All of the numbers follow this format:

[Doc No]
ABCD123

but the tricky part is the number could be suffixed with a revision. It is the latest revision I would like to find. For example, If I have a number followed by 3 revisions I would have:

[Doc No]
ABCD123
ABCD123+200
ABCD123+300
ABCD123+400

I need a query to give me the latest revision, in this case

[Doc No]
ABCD123+400

There is a possibility there are no revisions in which case the latest will be

[Doc No]
ABCD123.

How is this possible in a query???

View 5 Replies View Related

Filter Out Rows Based On Number Of Characters

Nov 23, 2005

I have an address database where people have been allowed to type in any characters they want in place of just leaving a missing field blank. For instance the Zip code was not a required field (don't as me why because I don't know. It just wasn't) so over the years when the person entering the data didn't know what the zip code was they would put a ?, a 0, a 000 or a period or what ever else they could think of at the time.

I would like to add a filter in my query where I could eliminate any thing with less then a certain amount of characters.

I thought something like this would do the trick

Zip: IIf(Len([V_ZIP]<5),[V_ZIP],"0")

But I am getting a data type mismatch in criteria expression. Any ideas of what I am doing wrong?

View 4 Replies View Related

Modules & VBA :: Finding Number In String - If Found Function Is True

Apr 17, 2015

Have a strings like this

Code:
dsa;hwq;67;dk;71c

Code:
uqiea;762c;iyh

Is there any possibilites to write a function which find number in string, even if some part (between ";" and ";") has number and text (like 762c)?

if there is number in string then function is true

View 5 Replies View Related

Queries :: Extract Number Out Of Field And Update Extracted Number To Another Field

Feb 6, 2015

791335.12pack. This is the object that is in one of my access fields. I need to extract the 12 and place that in another column called qty.

View 1 Replies View Related

How To Match 4 Character Word Or Number Or Combination By Identical 4 Characters Word

Apr 11, 2013

How to match 4 character word or number or combination by identical 4 characters word or number or combination in one word have 10 or 15 characters.

I have to two separate tables (Table A and Table B). Table A has one column (Tag No) and Table B has about 15 columns with one column name Tag No as below

Table A

Table B

Tag No

Tag No

2009

ZZZ-2030-DC

2010

ZZZ-2010-M9P

[code]....

They're both in MS Access.I am trying to match 2 tables - columns (Tag No) with join query, but not success. I want to match 4 characters in Table-A with 4 similar characters in Table-B (Tag No) cell.What query is suitable to compare two tables.

View 8 Replies View Related

Most/Least Characters Used In A Field?

Dec 18, 2007

I am sure there is an easier way fo doing this, but I am looking one at a time!!!

I want to know what is the Max and Min number of characters used in each field of my table.

I am using this code below, the max will be at the top and then scroll to the bottom to find the min, but as can be seen this is doing one field at a time and is very time consumining!


SELECT tbl05to06.Account, Len([Account]) AS MyAccount
FROM tbl05to06
ORDER BY Len([Index Key]) DESC , Len([Centre]) DESC , Len([Account]) DESC;

View 3 Replies View Related

Finding The Sum Of The Values In A Field.

Aug 22, 2006

I am trying to create a DB that would calculate the totals of different projects and also calculate the global total (i.e. the total of all the projects combined together)

So far I have created a query that can calculate the total of the projects in a field which I have named Total1. But now I want to calculate the sum of the field Total1, i.e. add all the totals of the projects up.

Sorry my explanation does seem quite lengthy, any help would be much appreciated.

ia.:)

View 3 Replies View Related

Finding Field When Entering ID

Oct 30, 2006

I have a form where I want to enter an ID and then see the Name in the next field.
How do I do that? Everytime I enter an ID I get "?Name" in the next field.

The Table is "BudgetLine" and I enter an ID from a table "Item"

Item
ID
Name

View 5 Replies View Related

Separating Characters Within A Field

Jan 16, 2006

Hi,

Simple question really (if you know your VBA)...

Have a field which contains records of strings of alphabetic characters i.e.

ADGHJLP
BCEFHIJKLMOPST
etc

Need to be able to separate these characters with a '/' so that the field reads

A/D/G/H/J/L/P
B/C/E/F/H/I/J/K/L/M/O/P/S/T

etc

I know it'll be a For..Next loop of some kind but can't seem to get the syntax correct.

Any help appreciated.

cheers,

Alex

View 4 Replies View Related

Limit Characters Per Field

May 9, 2006

Hi:

I have a table with one field for comments. The problem is the limit of the capacity of words, that you can type.
Any sugestions ????

Thanks for your time

View 1 Replies View Related

Make Field Exactly 8 Characters?

Jan 10, 2007

Hello All,

How would you make a field exactly 8 characters. I know how to only allow a field to be up to 8 characters, but not exactly 8...please help.

Thanks
Josh

View 3 Replies View Related

Removing The First Four Characters From A Field.

Nov 29, 2005

Hello,

I want to remove the first four characters from a field in a query.

The data are not always the same length, but it will always be the first four.

Example.

vmc_ml120e21/p -> ml120e21/p

Is that possible and how??

Thanks in advance.

View 6 Replies View Related

Trim Field To 35 Characters

Jan 26, 2008

I have a field "DisplayURL" that contains values of various lengths. Some are only 12 or 13 characters long, but others are 50 characters long. I need to run an update query to "cut-off" all the characters past the 35 limit - so that all records in the field will be 35 characters or less.

I've searched this forum and online and there are similar posts but nothing quite like this, I thought it would be a trim function but I keep reading that it only removes spaces. Any ideas?

View 2 Replies View Related

Grab First Characters From Field

Oct 4, 2011

Is there documentation/reading out there on how to query fields and take only a certain number of characters from said field?

We are setting up a user database on a 3rd party web application and they have a 15 character username limit so we want to pull the first 3 letters of a persons first name, middle initial, and first 4 letters of a persons last name to use as the username. We have an Access database will all of this information but I am not sure what the correct query is to complete this task.

John P Roberts = JohPRobe

View 1 Replies View Related







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