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 RepliesDoes 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?
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?
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.
Maybe it's the day's 'brain drain', but I need to set a criteria in a query whereby it will select answers in a field that are a specific number of characters in length.
i.e., answers that are 5 digits long (without knowing any of the digits)
Russ
Hello,
I have created a query using the query builder by concatenationg several fields using the expression builder. Once concatenated, the total number of characters is greater than 255, and is therefore truncated. Since this is a query field and not a table field, there must be a way around this limitation. I can't even create the query and dump the results into a memo field because the dump will still only contain 255 char.
I could probably create the query using VBA, which creates a table containg a memo field, which is then populated by a variable containing the concatenated fields, but I would like a simpler solution.
Any ideas on how I can generate a query field that contains more than 255 char? The query is used to populate a report.
Thanks in advance.
Ken
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"?
I have over 270,000 records in my database with 4 fields. And for each field I need to make sure they dont go over a certain number of characters for example field1 can't go over 20. Is there a way I can make sure that they don't go over that? When I go to design view and go to the Data Type of each field I change the filed size to 20, but if something is imported into that field that is over 20 characters long will I get a message saying it won't import? Or can I write a query to check the whole field to make sure that nothing is over 20 characters?
View 3 Replies View RelatedIs 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 RelatedHi,
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??
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!
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
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?
I currently import data into a table and it it has a text field which looks like this: 12,345.67 GBP...I need to use a query to make this a number field so that I can sum it's contents. I've managed to remove the 'GBP' part but can't seem to get rid of the comma?
View 4 Replies View RelatedAs the title says, i have an identifier field which combines two codes eg. AAAAAA/1234.
I am using a query to extract the number part, however when i try to link to another query it says 'type mismatch'.
I assume that this is because the main query is based on a table where [ShipID] is numeric and the extracted data is based upon a underlying table where [PackageID] is a text field.
Is there any way to get round this?
Many thanks
Hi,
I have a text field in a table and need to convert it in a query to a number field for sorting purposes, but not have it change in the table.
Thanks,
Jeff
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 RelatedI have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies View RelatedHow 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.
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;
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
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
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
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.
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?
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