Queries :: Order By Letters And Then Numbers

Dec 31, 2013

I have a column containing an id that consists of the first two letters of a weekday followed by an incrementing number. For example, for Monday, I have "MoA1" "MoA2" "MoA3" ... "MoA11".

The problem is that when I sort my list, it is ordering it: "MoA1" "MoA10" "MoA11" "MoA2" "MoA3" etc. Currently, my order by property is set to

MID(TABLENAME.SORTFIELDNAME, 3, LEN(TABLENAME.SORTFIELDNAME))

View Replies


ADVERTISEMENT

Queries :: Combination Of Letters And Numbers = Data Type Mismatch In Criteria Expression

May 16, 2013

I am working on a fairly ancient manufacturing database that identifies items using a combination of letters and numbers. The usual format is to have a letter (which suggests something about the item type) followed by a sequence of numbers.

I am trying to write a query that looks up all the records beginning with a prefix or arbitrary length, strips away the text, and finds the highest number.

Code:

SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,Len(LocalID) - 1)=True)

This query produces the error given in the title of this thread, whilst the following works:

Code:

SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,5)=True)

This related query also works and shows a load of -1s and 0s correctly

Code:

SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix,
IsNumeric(Right(LocalID,Len(LocalID) - 1)=True) As Alias
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND

But once again shows the error message when I try to filter the field Alias to -1 or 0 only through the right-click menu.I have tried piping Len(LocalID)-1 through CLng, CInt, Int, CDbl and CSng; this changes the error to 'Invalid Use Of Null' I have also tried removing the '=True' from the IsNumeric() term.

View 2 Replies View Related

Remove Letters From Numbers

Dec 15, 2005

Hello again.

I’m trying to find a way in a query to remove any data except for numbers

Examples


NET 90
NET 10
NET 10 DAYS
NET 110
NET 15


Any help would be greatly appreciated

Corey

View 5 Replies View Related

Criteria For All Numbers Or Letters

Jan 18, 2008

Hello, I am a new member...thanks for any help anyone can provide.
I have a table with a column in which the fields will come in with various lengths and combinations of characters. I need to look up certain values from this field, and assign a value to another field based on that. For example if field1 begins with an "H*", then field2 will equal "INS".

My problem is that I need to write a statement saying something like if field1 starts with "H" and is followed by only numbers for any length, then assign field2 "blank"

so I cant use "H*" because that includes letters...and Ive tried criteria like "H[!a-z]*" but cant get anything to work(i know the last example only looks at the space after H) Does anyone have any ideas?

View 4 Replies View Related

How To Show Letters And Not Numbers

Sep 16, 2015

In the attachment. I want to show the letters and not the numbers that is stated in step 4. How will I do this....

View 1 Replies View Related

Setting Field Criterias (numbers And Letters)

Feb 13, 2008

The data that is to be entered in the field "Rank", is either RK1, RK2 or RK3

How would I make the validation in a table make the user enter the format RK(Number), with only numbers from 1-3?

Thanks!

View 4 Replies View Related

Identifying Words Spelt Incorrectly With Numbers Instead Of Letters

Nov 15, 2007

Hello

I have some data I need to clean.
It is in an address field the majority of the data is fine but there are some records with words that have been spelt with numbers:

2 9RAMBLEGATE
2 Forest V1EW
2 KEM8LE Close
2 BELLEMOT4TE Road

Is it possible using a query to find instances where this has happened so I can flag these?

Any help would be greatly appreciated

Tim
:confused:

View 7 Replies View Related

Modules & VBA :: UDF In Excel Template - Turning Letters Into Numbers

Jun 12, 2015

I have this UDF in my excel template that changes a set of numbers in to letters corresponding a code.

For example the code is "EUCHARISTO" it would simply mean

E=1, U=2, C=3, H=4, A=5, R=6, I=7, S=8, T=9, O=0 and by default the tenths position (".0")=X the hundredths position (.0"0") = Y and If a number repeats it becomes G.

Examples
12.50 = EUAY
123.00 = EUCXY
12.25 = EUGA
99.00 = TGXY
99.50 = TGAY
999.00 = TGTXY
999.99= TGTGT

Here's the UDF:

Code:

Function LetterCode(ByVal Numbers As String, Letters As String) As String
Dim X As Long
Numbers = Format(Numbers, "0.00") * 100
Letters = UCase(Right(Letters, 1) & Left(Letters, Len(Letters) - 1))
If Numbers Like "*0" Then Mid(Numbers, Len(Numbers)) = "Y"

[Code] ...

Also, where best to put this code, In a query or in a module.

View 6 Replies View Related

Generating Order Numbers

Dec 12, 2006

Anyone have have any suggestions on how to create personlised job numbers instead of relying on auto numbers?

At the moment I have to freetype them in when creating a record & I would like to work out how to automatically generate them.

maybe something like

1030:101
1030:102
1030:103
1030:104......and so on.

Any suggestions would be much appreciated

thanks. :)

View 13 Replies View Related

Auto-numbers Are Not In Order For Tables

Nov 21, 2012

My autonumbers are not in order for tables.As in for example for a customer

customerid customer first name customer last name

17 john parker
18 james johnson
19 alex scott

This is right at the top of the table the first sets of data,i want it to be

customerid
1
2
3
4
5
,etc

Why does it have a random number like "17" when I DO NOT have the first 17 customers !

View 6 Replies View Related

Tables :: Come Up With Automatic Line Numbers For Each Specific Order?

Oct 2, 2014

I am trying to come up with automatic line numbers for each specific order. So for example, I have job number 123456 that has ordered 3 items, what I would like is that item 1 has a field with a 1 in it automatically, and item 2 has a 2 in the field and so on. But the trick is that when order 123457 gets entered and has 5 items entered, I would like it to start over at 1 and go to 5. Does this make sense? Is it possible to do this in a table? Or can this happen on the form? That I assign a value to a field. I am using Access 2010, have been for a few years now, but I have lots to learn.

View 14 Replies View Related

Queries :: Search For Multiple Plot Numbers Preferably In One Parameter Prompt With Comma To Separate Numbers

Aug 12, 2014

I'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.

View 14 Replies View Related

Queries :: Finding Duplicates Using Last Name And First 3 Letters?

Apr 2, 2014

I need to create a query that will pull duplicate names out of my db.

I would like it to pull all names that have:

duplicate LastName and duplicates of the first 3 letters of the FirstName.

For example, if I had the names:

Bland, Abe
Brown, Abe
Brown, Bill
Buster, Jon
Buster, Jonathon

I would like my query to return only Buster, Jon and Buster, Jonathon.

View 4 Replies View Related

Queries :: Return Number Before Letters

Mar 12, 2015

I would like to run a query which takes a code which contains a mixture of number and letter and returns all the number before the first letter. I was using the left function (=Left(([Codes],2)) but sometimes there is one number sometime two. The desired results are shown below;

7pol2try36 = 7
12cet9fre55 = 12
10yea3gtr77 = 10

Is this possible?

View 3 Replies View Related

Queries :: Stripping Letters From A String To Extract Information

Feb 15, 2015

I have a need to strip of letters from a string but i needs to look for / as the length would change, below is an example of the data I am working with

record 1 example ) REP/1349/999/426066/XX/9
record 2 example ) REP/UDKBS01N/1/448174/XX/

what i need to extract is

1) = 1349
2) = UDKBS01N

I need to get the information between the first / and the second / is there a function in access to get this.

View 5 Replies View Related

Queries :: Pull Letters From Notes Field If Empty?

Oct 29, 2014

I have a query that holds data based on a field. If the field [Device In] is "TimeStation-1" in TblTime for example it holds "AV" in the field [House]. Trouble is some fields are blank and when this is so I want it to pull the last two letters from the [Notes] field. I have attached the database. The query is [QryDeductionsandSleep Ins].

View 4 Replies View Related

Queries :: Searching For A Value Between Numbers

Nov 23, 2014

In the Table I have in the trailer database has, among many, 2 fields called 'Maximum Length' and 'Minimum Length'. Say, the data in these fields could be 5 and 4 respectively. I would like to create a search box where I can type in any length such as 4.4 and the database would return the trailer that can fit this length. (Sot the searched value is between the Min. and Max. length) What would I need to type into the query box and the search form to type in the length?

View 6 Replies View Related

Queries :: Date Order In A Query

Jun 24, 2013

I have started a table to manage expenditure with dates payments and headings. Set up a query to summarise the monthly amounts by each heading. The Query is sorting the date in date in alpha order not date order so I get February before March etc. How do I get it to show chronological order?

View 3 Replies View Related

Queries :: Order Query For Charts

Sep 8, 2014

I've created some queries on some data that return summary information to then chart over time. The fields being returned are YearGroup, Academic Year, Term, TotalI want the data to get sorted by YearGroup (Year7, Year8, Year9 etc) however, when it sorts the data it sorts it starting with Year10, Year11, Year7, Year8, Year9.

View 4 Replies View Related

Queries :: Order Of Conditions In WHERE Clause

Jun 12, 2014

From a performance perspective, does it matter in what order a number of clauses are specified ? For example if many records satisfy ConditionA but few records satisfy ConditionB, is it better to put ConditionB first ?

SELECT Fields FROM Table WHERE ConditionA and ConditionB
or
SELECT Fields FROM Table WHERE ConditionB and ConditionA

View 1 Replies View Related

Queries :: Specific Sort Order

Sep 13, 2013

I have a query that is returning running conditions for a machine over multiple shifts. the table is set up like this

Code:
date shift parameter condition
09/13/13 1 front 500
09/13/13 1 middle 450
09/13/13 1 back 475
09/13/13 2 front 510
09/13/13 2 middle 460
09/13/13 2 back 490

this is just a small example. there are about 24 different parameters reported on in a shift..I want the query to sort by date ascending, shift ascending, and then by parameter in a specific custom that i define. I want it ordered "front, middle, back" for each shift. Again this is just an example, there would be 24 parameters I want in a specific order. Then do I need to define the sort order again for the report based on this query?

View 3 Replies View Related

Queries :: ORDER BY Statement Seems To Have No Effect

Oct 15, 2014

SELECT Count(Query1.Route) AS NumberofReRoutes, Query1.Route
FROM Query1
WHERE [Start Date] <= #9/30/2014# AND [End Date] >= #9/1/2014#
GROUP BY Query1.Route
ORDER BY "NumberofReRoutes" DESC;

The last line seems to make absolutely no difference. I can change it to ASC, or I can remove the line entirely, and nothing changes. I need these sorted by the NumberofReRoutes field, but this method isn't working!The output at the moment is two columns: Route, and NumberofReRoutes.

View 3 Replies View Related

Queries :: Locating All 3 Digit Numbers

Dec 19, 2014

Is there any code to be able to be used within a query to locate the 1,2 or 3 digit numbers?

such as 1 ,11, 111 - 2, 34, 834 etc...

View 4 Replies View Related

Queries :: Count Duplicate ID Numbers

Sep 15, 2013

I have the following code and I am trying to show a COUNT of duplicate ID numbers, so for example

ID ID_COUNT
2000 2
2000 2
2001 1
2002 1
2003 1

Code:

SELECT [DaisyServiceData-Import].*, Companies.CompanyName, Company_vs_Services.[Tariff Code], IIf([ClashForce]=True,"",IIf([ClashOveride]=False And [ClashClear]=False,IIf([DaisyServiceData-ClashExc].[DaisyServiceData-Import].[ID] Is Null,"",1),"")) AS ClashExc, IIf([Sell Price]="","",IIf([SellRateForce]=True,"",IIf([CompanyName] Is Null,"",IIf([SellRateOveride]=False And [SellRateClear]=False,IIf([Total Cost]>[Sell Price]+0.001,1,""))))) AS SellRateExc, IIf([Tariff Code]='1',[T1Rate],

[Code] ...

A GROUP BY query will not work for me as the recordset needs to remain updatable.

View 2 Replies View Related

Queries :: Identifying Whole Numbers Vs Integers

May 2, 2013

I currently have a calculated field that will result in either a whole number or a decimal. From there, I need to set up a field that identifies if the previous field is either a whole number or a decimal.I have tried several different formulas, but always seem to get an error or a query that does not return any results.

View 3 Replies View Related

Queries :: Formatting Numbers To Have A Leading Zero

Apr 24, 2014

I have an INSERT query like so:

Code:

INSERT INTO table1 (column1, column2, column3, column4, column5)
SELECT (field1, field2, field3, field4, field1 & "" && field2)
FROM mastertable

According to the format desired by a client, column5 from table1 needs to have field1 and field2 concatenated and represented as two-digit numbers if they're a one digit (has a leading zero).

View 7 Replies View Related







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