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 Replies


ADVERTISEMENT

General :: Extracting Data From Access Without Knowing Number Of Results

Jun 25, 2014

I wish to extract data from my access database...namely all packages we are taking to the post office for dispatch that day. The post office form looks like this...

I need to extract the name the address & postal service used (this data is all contained in our access database. I envisage using a 'flag' to trap those orders that haven't been posted yet (in other words export only those database entries that have not been flagged as posted, then afterwards update the database to change the flag to 'posted')

My dilemma is that I've not really used reports much (I'm assuming that reports is the correct way to go here)...how do you format the output of a report, when you don't know how many lines are going result (some days might see 10 order, other days 20 ...and so on)...

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

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

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

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

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 3 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

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

Extracting Times

May 17, 2005

I've got a field in a table called GenTime. It is in a Date/Time format, like 5/16/2005 7:00:00 PM.

What I need to do is extract from this field the time, like 7:01:00 PM to 7:59:00 PM. I just can't seem to make it work?? :(

Help!

View 3 Replies View Related

Extracting First Name And Surname

Jan 12, 2006

Hi,

I have been sent a file of customer contacts. They are held in a single field and have the format firstname + surname. So that I can load this data into a enterprise database system, I need to be able to extract surname and first name into to separate fields.

Any ideas on how I achieve this through an Access query?

Many thanks

Clay

View 2 Replies View Related

Extracting A Substring

Mar 1, 2007

Hi Everybody,
I have an access database and in a certain table I have a string field which has two values. I want to make a query that will show all the fields which contain a substring.

I'd better give an example:

Table: Kid

Fields:
Name: Ben
Activities: Soccer, Tennis

Name: Dan
Activities: Soccer

Name: John
Activities: Football, Origami


.
.
.

I want to show in a query all the Names which have a "Soccer" substring in their Activities string field

Need your help ASAP !!!

View 3 Replies View Related

Extracting Odd/Even Or Both Numbers

Sep 19, 2007

Hi all,

In my query I can query for ODD or EVEN Street Numbers using the syntax below:

OE: [PROPHOUSENUMERIC] Mod 2

This works fine returning just odd or even numbers.

Now the problem I am facing now is that I wan the same query to return both Odd and Even Street numbers if I did not ask for either Odd or Even Street Numbers.

Is this possible. I have search hi and low in the forum but no answer. I have attached the file I am working with this post.

Thanks in advance.

matomo :confused:

View 2 Replies View Related

Extracting Duplicates

Aug 19, 2004

Can someone help me out with writing the expression to pull out the IDs that are duplicates?
I know it is something like count >1. thanks!

View 1 Replies View Related

Seeking Help On Extracting Letters From Name

May 5, 2005

Hi all: MS Access 2003. I have two text fields family_name and given_name I need to extract 1st, 2nd & 5th letter of family_name along with 2nd & 3rd letter of given_name to form a link_key field. If either name has insufficient letters the missing letter is replaced by the numeral 2. Does anybody know how to help me acheive this please!

View 2 Replies View Related

Extracting Month From A Date

Jun 15, 2005

Hi,

I have a date field in one of my queries (01DEC2004). I have a pivot table linked to the query. I would like the users of the pivot table to be able to group the data by month. Currently the group feature will not work. Does anyone have a suggestion on the best way to extract the month from the date field?

Could I create a new column in the query called month and use an expression to extract the month?
If so can someone tell me how to key the expression to do this?

Thanks for any help.

View 1 Replies View Related

Extracting Range Of Records

Jul 4, 2005

Was just wondering how i go about pulling out a range of records. I do not want to use my Primary Key autonumber value as some records have been deleted and selecting a range of 10 to 50 for example will not guarentee 40 records. Is there some way i can use an SQL command in the SQL View window to achieve this?


Cheers

View 3 Replies View Related

Extracting Part Of A Field

Jul 27, 2005

I found a thread from last year that is close to solving this question but not 100% so I'll post this new thread.

I have a field called ITEM_NUM in a database with the following structure:

XX-YYYYYYYYY
XX-YYYYY
XX-YYYYYYYYYYYY

I need to extract the Y portion of the data and a Query format would be the best.

FYI, the X portion consists of 2 Letters/numbers then the Hyphen and the Y portion is variable in length.

thanks for your help!

View 1 Replies View Related

Extracting Data Into A New Field

Dec 13, 2005

You guys have been so helpful in the past. Perhaps you can help me with this as well. I believe it’s a bit out of my scope. I have a list that reads similar to this:

Field 1

DMA: ALBANY
CAR 1
CAR 2
CAR 3
DMA: BOSTON
CAR 1
CAR 3
CAR 4
CAR 5
CAR 6
DMA: HOUSTON
CAR 1
CAR 3
CAR 4
CAR 5

I need it to read like this:

Field 1 Field 2

DMA: ALBANY CAR 1
DMA: ALBANY CAR 2
DMA: ALBANY CAR 3
DMA: BOSTON CAR 1
DMA: BOSTON CAR 3
DMA: BOSTON CAR 4
DMA: BOSTON CAR 5
DMA: BOSTON CAR 6
DMA: HOUSTON CAR 1
DMA: HOUSTON CAR 3
DMA: HOUSTON CAR 4
DMA: HOUSTON CAR 5

If my list was this short, I would do it all manually, but my record list is in the 10,000s. Basically I want field 1 to read: DMA:ALBANY and Field 2 to read:CAR 1, and so on. I’ve tried linking the table to itself in a query, however, that hasn’t proven to be helpful. I've also searched the FAQ's and newsgroups. Any insight would be appreciated.

Thanks,
Amanda

View 3 Replies View Related

Extracting A Portion Of Data

Oct 3, 2006

I probably have an easy question for one of you out there, but I just cannot get it myself.

I need to extract the data between two characters in a string.

The data will be similar to this:

T4454: Text Text-Text: $296.07: Text Text

I need to get the dollar amount between the dollar sign and the colon on the right of it.

So far, I have this for my query:

Amount1: Mid$([subject],InStr([subject],"$"))

Which gets the amount to the left side, but also has the $ in it.

In the example above, I would like to just end up with 296.07 as the result from the query. I should also add that the dollar amount may vary from 1.00 to #,###.00

Thanks in advance for any help you can provide

View 5 Replies View Related

Extracting Random Records

Nov 3, 2006

If i have a table with several records let's say 1000 records, and each record has a field called cboCategory. Now from these 1000 records, lets say i have 200 records which has a value of CategoryA in the cboCategory field. Now is it possible that i extract "randomly" a number of records from that particular category chosen.

e.g.

Table -> 1000 records

records which belong to categoryA -> 200

extract 20 random records from the table which have a value of categoryA inthe cboCategory field.

Thanks for any suggestion.

View 5 Replies View Related

Extracting Data From Combo Box

Aug 1, 2006

Hi,
I have a combo box, which the first and last name of an employee is combined using trim. Does anybody know of a way to seperate both fields as these would be used an a query so I would need them seen as 2 fields otherwise there won't be any matches.

Cheers,
Ben

View 1 Replies View Related

Extracting Data From A Table As String

Nov 15, 2005

I need to be able to extract individual fields from a table as strings in order to then send this data to my EPOS printer (which will accept only strings to print) I have created the table with a query so the question could equally be how can i output strings as query results. Im using Access97 HELP!!
I have tried to set up a ADODB.Connection but either Access 97 wont accept it or i've got the coding wrong

View 3 Replies View Related

Extracting Data From One Field In A Table To Another--Help!!!

Jun 27, 2006

Hi Guys,

I have a table called "tblEmployees" with the following fields:
EmployeeNo (Primary key)
Name
Address
DOB etc
Letter1Dated
Letter2Dated

I have another couple of tables:

"tblLetter1" with the following fields:
EmployeeNo (Primary key)
Name
Letter1Dated
Letter1RemainingFields...

"tblLetter2" with the following fields:
EmployeeNo (Primary key)
Name
Letter2Dated
Letter2RemainingFields...

I want to be able to put the data from 'Letter1Dated' field (from the "tblLetter1" table) into the 'Letter1Dated' Field in the "tblEmployees" table.
Likewise, I want to be able to put the data from 'Letter2Dated' field (from the "tblLetter2" table) into the 'Letter2Dated' Field in the "tblEmployees" table.

All three tables have the same primary key (EmployeeNo), which makes it even more difficult to do this!
I understand this may sound abit confusing so i apologise for this.

I would really appreciate it if anyone could point me in the right direction please.

Thanks alot.

View 4 Replies View Related

Extracting Data From A Field In A Table.

Jan 29, 2007

when I have created my table I have created a field called name that stored both last and first name. Right now I need to extract the the last name from the the field name and store it in another field "lastName". Any ways to do that?

View 7 Replies View Related







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