Queries :: Postcodes - Return Only TEXT Before Numbers
Jun 17, 2015I have a number of short postcodes ie
S43
GU10
ME8
etc etc
I want a run query to return only first characters before the numbers ie..
S
GU
ME
ADVERTISEMENT
Queries :: Match Postcodes Ad Return Part That Matches?
Sep 7, 2014I have been given a list of UK postcodes, with the following format L15TG or TS14TGU.
I need to be able to match these postcodes to a list of postcodes I have stored in the database, however, my list are only UK outcodes, so L15TG is just L1 and TS14TGU is just TS14.
So I need to match the records and return the part of the string that matches i.e take L1 from L15TG.
Queries :: Return Yes Or No If Other Text Field Contains Particular Text
Feb 18, 2015Produce a query that returns a yes or no (Y or N) field if other text field contains particular text (with wildcard on either side).
Idea is to have a column for each preset word.
Queries :: Return / Extract Just Text From Field
Aug 5, 2013I have a field which contains "01501 PRIKLJ. VRHNIKA - LJ / Stevno mesto" .
1.) I would like to extract just text part from that field, how? *number is always on beginning but different 1-10000
2.) Optional, how could I delete in this new only text field part of text after / ...*some rows have some dont /
Queries :: Return Value In A Text Box On A Report If String Contains?
Mar 24, 2014In my query, I have the week number and year arranged like this - "Y14-W11"
I want to return a value in a text box on a report if the string contains, for example, W11. In this textbox I've put the expression
Code:
=IIf([Y##-W##]="*" & "W11" & "*","2100000","BLAH")
But this just returns the falsepart no matter if the string contains W11 or not.
Queries :: Search For Certain Text In A Field And Return Value
Jan 1, 2015I am trying to search for specific text in a field and returns its value. For instance some of string includes Sub, L2L, Temp, Model, or MTM and I would like a query to return these values if found.
View 2 Replies View RelatedQueries :: Select Query Return (text) Instead Of (Boolean Value)
Mar 26, 2015how to do to return a text for each row (as field value) when a table field contains "1" as value ?
for example i have a table named "products" with a field/column called "promotion". Sometime a product is promotional, so in this case, the "promo" column holds "1" as value.
during a select on products table, how can i do to return "in promotion" (e.g.) if the column "promo" holds "1" for a product ?
Union Queries - Results Showing As Text & Not Numbers
Dec 13, 2007To cut a long story short, i've got a union query that is a full outer join of 2 tables.
Anyway, the results i have got back needed formatting to no decimal places.
How do i do that?
I've tried leaving it as it is and formatting it in the report, but it still doesn't format and i can't total it up.
It's like it is showing as a text and not number.
Is there a way of converting the result to a number, particularly in a sql query.
Queries :: Delete All Records That Are Numbers In Text Field
Oct 24, 2013I have a field that is text format. However many of the records in that field contain numbers.I want to delete all the records that are numbers in this text field, leaving only records with textual data.
View 4 Replies View RelatedQueries :: Unable To Find Missing Sequential Numbers In A Text Field
May 5, 2013I am trying to create a query to find missing sequential numbers in a text field. I am using this specific field as a case number which is designated as two letters, the # sign, two digits indicating the year, a dash, and then a four digit number; For Example: AB#13-1234.
The reason for this query is to tell the user of this database that a specific case number has yet to be entered and needs to be. The case numbers are unique and will never be referenced more than once.
My table name is "MainDataTbl" and the field i'm trying to find the missing case numbers is titled "CaseNumber".
Numbers Stored As Text Convert To Numbers?
Jan 10, 2007Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?
View 1 Replies View RelatedQueries :: Search For Multiple Plot Numbers Preferably In One Parameter Prompt With Comma To Separate Numbers
Aug 12, 2014I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
Sorting Text Field Containing Both Text And Numbers Sequentially?
Mar 26, 2014We have a database that we use to track the tablets in our organization, a combination of Apple, Android, and Windows.
We label them with a hostname depending on their branding. Example below.:
APPLE01
APPLE02
APPLE03
ANDROID01
ANDROID02
ANDROID03
WINDOWS01
WINDOWS02
In the database we use the field name HOSTNAME for these entries. When sorting the field, due to it being a text field, we get the traditional sort of
APPLE01
APPLE02
APPLE10
APPLE100
APPLE101
APPLE11
APPLE110
ANDROID01
ANDROID02
ANDROID10
ANDROID100
ANDROID101
ANDROID11
ANDROID110
We would like to have them show up as
APPLE01
APPLE02
APPLE10
APPLE11
APPLE100
APPLE101
APPLE110
ANDROID01
ANDROID02
ANDROID10
ANDROID11
ANDROID100
ANDROID101
ANDROID110
What is the best way to tackle this?
Help With Categorising Postcodes! :(
Mar 7, 2006I have a database with 3000 + records all with customers from all over the UK.
The CUSTOMER table contains
>customer ID
>name
>address
>postcode
I have been asked to produce reports from the database.
These reports should group all customers into postcodes regions and then display information accordingly. E.g how many customers have ordered information in region 1.
For each region I have been give a list of postcodes.
Region 1, e.g LS17, LS18
Region 2 e.g BD4 0,
Region 3 ETC
Region 4 ETC
Each region list is extremely long to type in but I have a copy on paper.
Could someone help and guide me into a quick way of grouping the required postcodes into regions 2 enable the reports to be produced.
One way I have done this is by doing a select query and typing in the criteria “like “LS1*” or like LS18*” …and by typing this on and on to the list this is quite long.
Plus I need to be able to use the * function so I select the correct postcodes.
As this is so time consuming ..Bearing this in mind I have been told a far better approach is to put the postcodes in a table and then perform a lookup on that table when trying to determine the region.
I don’t understand what to do…would this be a better approach if so how would I do this as I don’t know how to use lookup? Could someone help me? Perhaps with a small example so I could follow it? :(
Appreciate any help
Many thanks
Categorising Postcodes? Can Someone Help?
Mar 13, 2006I have a database with 3000 + records all with customers from all over the UK.
The CUSTOMER table contains
>customer ID
>name
>address
>postcode
I have been asked to produce reports from the database.
These reports should group all customers into postcodes regions and then display information accordingly. E.g how many customers have ordered information in region 1.
For each region I have been give a list of postcodes.
Region 1, e.g LS17, LS18
Region 2 e.g BD4 0,
Region 3 ETC
Region 4 ETC
Each region list is extremely long to type in but I have a copy on paper.
Could someone help and guide me into a quick way of grouping the required postcodes into regions 2 enable the reports to be produced.
One way I have done this is by doing a select query and typing in the criteria “like “LS1*” or like LS18*” …and by typing this on and on to the list this is quite long.
Plus I need to be able to use the * function so I select the correct postcodes.
As this is so time consuming ..Bearing this in mind I have been told a far better approach is to put the postcodes in a table and then perform a lookup on that table when trying to determine the region.
I don’t understand what to do…would this be a better approach if so how would I do this as I don’t know how to use lookup? Could someone help me? Perhaps with a small example so I could follow it?
Appreciate any help
Many thanks
How To Have Text And Not Numbers In Text Data Type
Jan 13, 2006I was wondering if there is a way of only allowing text in a text field - we need to do this for validation purposes when inporting a text file into a database. I have tried input mask but this only checks when entering new data - I have tried to use validation rule like"???????" however this means that there has to be 7 letters in each field and this is not the case. - the field is for town and a couple of the records in this field have numbers at the end which are incorrect that we want the database to reject :eek:
View 1 Replies View RelatedSelecting Multiple Postcodes From Table
Aug 15, 2006This must be the easiest of things, but I haven't used access in a while in now in my new job I need to run the following query.
I have to select a number of postcodes from a single table to see how many are in there. For example select postcodes beginning with B, CV, DY, HR, ST, TF, WR, WS, WW. Then I need to write down how many records found with the above postcodes.
Also I may have to omit some too like omit CV12
Thank you in advance
UK Postcodes In The Query Design Grid.
Oct 13, 2006Hi everyone;
I am using Access 2003 with Windows XP Pro. I have a problem with using a query and the dreaded UK Postcodes!
I have a Client table that contains a field “Client Postcode 1”, in which is entered the first part of the UK postcode i.e. AB11, WC1A, E1, EC2V, etc.
In a Candidate table here are many text fields where we enter which postcode a Candidate wants to work in, i.e. TN, CV4, EC, W, etc.
We have a separate Candidate Search form where we select a particular client (Combo box from the Client table) which then displays the Client’s Postcode 1 data i.e. the first part of the UK postcode before the space.
We have a select query that then tries to match the Client Postcode 1 with the postcode that the Candidate wants to work in from the Candidate’s table.
In this query design grid I have successfully created criteria that will match the Client Postcode 1 field to the Candidate’s postcode field – very straightforward. This, for example, will match a Client with a TN39 postcode with a Candidate who wants to work in the TN39 postcode.
In the same query design grid I have also been able to successfully match the Client Postcode 1 field to a Candidate’s Postcode field using just the first 2 letters (using Left). This matches a Candidate who wants to work anywhere in the TN postcode area (TN1, TN2 etc.) with a Client whose postcode starts with TN
However, what is stumping me is where a Client’s Post code has only 1 letter followed by numbers (E1, B2 etc) in the first part of their postcode without returning EX, BL etc. I would like the accumulated wisdom on this forum to point me in the right direction to design criteria to input into the query design grid that will match ONLY the first letter of the postcode IF the second character is a number.
Regards to all - and what an excellent site!
Return Last Month Value In A Text Box
Aug 26, 2006I have used the following function to return the date of the previous Sunday in a text box when I open a form..
=Date()-Weekday(Date()-1)
In a different text box I would like return the pevious month based on the date in the text box.
For example the above function would return date 20/08/06. I would like to display JUL-06 in the second text box. Can anybody help?
Text Return Based On GPA
Nov 10, 2004Dear All:
Thank you for all you help with the above title. Your suggestions were all implemented.
Code:
Private Sub GPA_AfterUpdate()
If DIVISION = GRADUATE Then 'Honors field is empty
HONORS = ""
End If
If UNDERGRADUATE = True Then 'Apply appropriate honors
End If
If GPA < 3.2 Then
HONORS = ""
ElseIf GPA >= 3.2 And GPA < 3.5 Then
HONORS = "CUM LAUDE"
ElseIf GPA >= 3.5 And GPA < 3.8 Then
HONORS = "MAGNA CUM LAUDE"
ElseIf GPA >= 3.8 Then
HONORS = "SUMMA CUM LAUDE"
End If
End Sub
Here is the issue: Form created with with a combo box called "DIVISION."The choices of the combo box are "GRADUATE" AND "UNDERGRADUATE." There is a text box called "GPA", where a gpa is entered. then, there is an "Honors" field. When a gpa is entered, the honors box returns the appropriate message.
When graduate is selected from the combobox, the honors box should be empty. (That part does not work.) When undergraduate is selected from the combo box, it works.
Please help,
Dion
Asking A Text Box To Return A Number
Jan 28, 2005Hi guys and gals,
I have compiled a very simple database to track staff and training audit requirements for my company.
I am 57 thick as 2 short planks and need some help please.
I wish to ask a text box called "Score" to return a number 2 if a yes/No box is "NO" and a number "1" if the Yes/No box is "YES".
I have looked at the help sections of Access and thought maybe an "IIf" statment in the Validation Rule section of the "Score" box might do the trick.
However, i haven't got a clue as to what the coding should be written like.
Can anybody help me please.
Basically i want to say
"If YES/NO = NO then SCORE = 2 but If YES/NO = YES then SCORE = 1"
Told you i was thick
Honors Text Return Based On GPA
Nov 9, 2004Dear Access Wizards:
I have created a form based on a query with students' gpa in the form. The gpa is in a specific field.
There is another field for honors with is for summa cum laude, magna cum laude, cum laude.
Here is the issue: If the students' gpa is 3.8, return "summa cum laude"
If the students' gpa is 3.5, return "magna cum laude"
if the students' gpa is 3.2, return "cum laude"
This applies to undergraduate students only, which there is a field for.
Any ideas on how to resolve this issue?
Many thanks,
Dion
Converting Text To Numbers
Jun 14, 2006I wondered if someone could help.
I am using the Val () Function to convert a text field to number within a query which still gives me a text output.
P.S I do not have permissions to change within table as using Access as a front end to SQL via link Tables.
How To Convert Text To Numbers?
Mar 24, 2007Hello, I have a Field in a Table called Fund Codes and they are entered as text: 1, 2, 3, 4 all the way to 1000. Now i need to find away to change 1 to 001, 2 to 002, 3 to 003, 10 to 010, but leave 100 as 100 and 222 as 222, so only add 0s to numbers from 1 to 99. Is there a way to do it? I know i can change the text to numbers and use format to do that, but i need to leave it as text. Please help. Many thanks!
View 11 Replies View RelatedTreating Text Like Numbers
Oct 12, 2006HI,
I have a field that contains mainly numbers, but the data type is set to Text because there are a few records where the numbers have a letter suffix.
Eg. Invoice numbers 100, 101, 102, 102A, 103 etc.
These suffixes are a result of poor data entry when the data was stored in Excel.
What I was to be able to do is sort this field and treat all entities as numbers. Currently when I sort it, it shows:
1
10
11
2
20
21
3
...etc
Is this possible, even though the data type is Text?
Text + Numbers In 1 Field
Feb 17, 2005Is it possible to have text and numbers in one field as access asks you specify the data type of that field?
View 1 Replies View Related