Getting The Max Value After Grouping Alpha-numeric Values

Jan 8, 2007

Hi,

I am trying to create a query that will return the max value of small groups of alpha numeric fields. The numeric portion is the same for a series/group of items, but the alpha increments. Example: (I color coded the numeric groups for ease of reading)

The following:

...
0001210-0015A
0001210-0015B
0001210-0015C
0001211-0001A
0001211-0001B
0001211-0001C
0001211-0001D
0001211-0002A
0001211-0002B
0001211-0003A
0001211-0003B
0001211-0003C
0001212-0001A
...

would have a query result like this:

...
0001210-0015C
0001211-0001D
0001211-0002B
0001211-0003C
0001212-0001A
...

Unfortunately, I do not have any other fields that I could use to help. I was able to create two calculated fields one with the numeric portion and one with the alpha portion, but then didn't know how to remove the unwanted ones. Also, every value is the same size, 7 numbers, one dash, four numbers, and one letter. My table has around four thousand records or so.

I thought of writting a VB app to single step and compare, but I am hoping there is a better method.

I have been pouring over this site and trying all kinds of things that end up failing. I am not a novice, but obviously not an expert.

Any help would be greatly appreciated.

Thanks in advance.

View Replies


ADVERTISEMENT

Forms :: Sort Numeric In Alpha-numeric Text

Mar 11, 2014

I have a text field having data i-e HO-1, HO,2, ACW-25 and so on. The field name is nBadge_num and is Unique. The data in this field is sorted automatically like 1, 10, 11, 12, 13, 2, 3, 4, 5...because this is the text field.

The number on the form is automatically generated, when the user type HO- for example on field exit event. The last number will generate like HO-5.

Code for automatic number generation is:

Dim dbs As Database, rst As Recordset, Response
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Max(Right([nBadge_Num],Len([nBadge_Num])-" & Len(Me.NBadge_Num) & ")) AS MaxNo " _

[Code]....

My problem is when the number is generated it give HO-5 instead of HO-14, How can I sort the numeric part of the field ?

View 2 Replies View Related

Require Specified Numeric Lenght Or Alpha Input

Jul 26, 2007

I'm trying to create a validation rule that requires an input of 5 numeric characters OR if the entry begins with a letter it can be any length. For example if the record begins with a number the length must be 5. If it starts with a letter any length is acceptable.

Any help is appreciated!

Thank you!

View 2 Replies View Related

Queries :: Alpha Characters In Numeric Field

Feb 6, 2014

I have been downloading .csv files from a construction website that we use to following projects as they develop. I import the file into Excel and then want to bring it into my database. The issue is with two fields I have that are numeric, however, when downloaded into Excel, the information in the two fields now have an (')added to the beginning and end of the string, i.e. and the second field has an (') and (-) to the data.

'201400409710'
'201300697683'

The second field is a zipcode field that comes in like this:

'14063-1127'
'14222-1004'

I would like to design a query to strip the ' from each field as well as the (-) in the zipcode field.If I have to write 2 separate queries that is fine.

View 4 Replies View Related

Modules & VBA :: Limit A Field To Only Alpha Numeric Characters

Sep 25, 2013

I would like to know how to limit a field on a form to only Alpha Numeric characters.

Example: ~AAUZNTO

This would be scanned by a bar code and I want the field to show only this when scanned: AAUZNTO

View 14 Replies View Related

Tables :: Alpha Numeric Auto-number Field

Jul 26, 2014

I need some kind of function (I been told) that generates 3 different alphanumeric autonumbers in the same field when adding a new record, starting such field from A-1, B-1 and C-1 to infinite.Because the record gets inserted in the table with an append query and not manually through a form, I believe the function should be placed in the Default Value setting of the field.

View 3 Replies View Related

Modules & VBA :: Increment Variable Length Alpha Numeric Field By 1

Jul 8, 2013

I have a database that is used for tracking changes to numerous courses. Part of this database create a unique tracking number for each course problem developed. Currently I have the form do a comparison using the highest most number to compare against the current number assigned and prevent the form from saving until the number is incremented and not a duplicate, it would be a lot easier if I could just have it increment plus 1. I have seen various answers but they all seem to depend on the alpha portion of the field being a set value, in my instance it is variable in length. The only part that is fixed is the last four characters to the right which are the numeric portion I would like to increment. For example the field can equal:

QACP-M-PIQ-6059
QACP-M-PREF-6002

how to extract just the numeric portion, increment it by one and save?

View 2 Replies View Related

Modules & VBA :: Separate Numbers And String From Alpha-numeric String

Jun 7, 2013

MS-Access VBA code to separate numbers and string from an alphanumeric string.

Example:

Source: 598790abcdef2T
Output Required: 598790

Source: 5789065432abcdefghijklT
Output Required: 5789065432

View 13 Replies View Related

Queries :: Exclude Records Where Last Char Is Alpha Or Same Without Alpha?

Jul 31, 2014

how to exclude those records that have an alpha as the last character and also exclude the records that have the same id code without the alpha.

Example Listing:

id_code
ak_12345
ak_12345a
ak_12346
gl_2391
1009123
1009128
1009128a
1009128b

Desired Output Result:

id_code
ak_12346
gl_2391
1009123

The length of characters in the id_code is not consistent. Some id_codes are longer and some are all numeric and some don't have underscores.

View 14 Replies View Related

Converting Numeric Values Into Time Values

Jan 15, 2008

I have a travel time database that should tally the number of minutes traveled and convert them into a time (hours and minutes). I, however, am having difficulty converting the numeric values cleanly. Is there anyway to convert 102 minutes + 100 minutes + 110 minutes = 312 minutes to 5 hours and 12 minutes cleanly? I need to take averages of time traveled and hours worked but cannot do this correctly. Thank you for any help in advance.:eek:

View 2 Replies View Related

Validating Numeric Values!

Oct 15, 2006

How can I check if the user is entering numeric values?

I can do it in the code, i.e. the lost focus event to the text box but I want to make a check in the "validation rule property" of the text box...!! How is it possible??

Thanks

View 1 Replies View Related

Option Group: Help With Numeric Values

Apr 26, 2007

Hello,
I am not sure what cateogory to choose for this question but I would greatly appreciate your help.

I have 5 buttons in my option group:
1. very often
2. often
3. occasionally
4. rarely
5. never

I have to produce a report using description of each button in my report, not numeric value. I heard something about global variable but I am not sure how to do it.

Many thanks,
Debbie

View 1 Replies View Related

Reports :: Grouping Total Values On Footer?

Nov 2, 2013

Placing the grouping total values on the bottom of the last page of this report, such as below?

Common . . . . . . 44.55%
Convertable Bond .14.55 %
Preferred . . . . . . .40.91%

View 1 Replies View Related

Replacing The Numeric Values Of A Field In Querry

Apr 25, 2007

Hi

I am trying to replace the numeric values of a field with a text in querry using Choose function, something like this aa5:

Choose([a5]-1="one";[a5]-2="two";[a5]-3="three";[a5]-4="four")

But it is not giving me no results :(( I am missing something but I do not what. Maybe I should use some other function?

I hope someone will help me!

View 1 Replies View Related

Queries :: Numeric Comparison And Null Values

Sep 24, 2013

I have a form with a textbox, where one inputs a number and then I run a query with the form criteria. The query is

Code:
Select Blah blah from dbo_temp where A>textbox value OR B>textbox value OR C>textbox value OR D>textbox value

The columns which are linked to the textbox some times contain null values. So, A, B, C, D columns do contain null values.

The expression in the "Criteria" column of Columns A, B, C, D (all on different lines - to make sure OR criteria is fullfilled) is as follows

Code:
>IIf(IsNull([Forms]![MainForm]![Criteria]),-100,[Forms]![MainForm]![Criteria])

I've put in -100 as an arbitrary never possible number. Obviously, this does not return Null values.

Question: How can I return both Null & Numbers when the Textbox in the form is left blank? in all the columns. Currently, I am not getting Null values

Code:
>IIf(IsNull([Forms]![MainForm]![Criteria]),SHOW ME EVERYTHING INCLUDING NULL VALUES & NON-NULL NUMBERS,[Forms]![Material Finder]![txtPS])

or in other words

If the textbox is blank, show me all the data available, else if it is not blank then show me only the values that are greater than the number entered in the textbox from within column A, B, C, D ....

View 3 Replies View Related

Tables :: Different Numeric Values In One Cell Of Table

Jan 30, 2014

Is there a way to write different numerical values in single cell of table via form.

View 2 Replies View Related

Queries :: Report Returns Numeric Values

Jun 28, 2013

I am making a report off of a query. The report is returning values from a Option Group. 1=Yes, 2=No and 3=N/A. The report returns the numeric values and I want the value labels instead. So, I created a column in the query for exp1 which looks like the following:

=IIF([VSArrive]=1,"Yes",IIF([VSArrive]=2,"No","N/A"))

What the report returns is #Error.

View 6 Replies View Related

Forms :: Filter On Text And Numeric Values?

Oct 17, 2013

I've created a macro to use an unbound textbox to filter a form. In a query, I combined 3 fields to enable an easy search over. Sadly, only text characters work for the search, so whenever I search for numeric values, it returns null. This doesn't happen if I utilise the filter over just a numeric field - only when the search field combined multiple fields.

Is there a simple solution? My marco filter is

Code:
[Forms]![STAFF SEARCH FORM]![SEARCHDATA] Like "*" & "[SEARCHFIELD]" & "*"

View 1 Replies View Related

Adding Numeric Values In Design View Of Query

Mar 3, 2008

I am adding fields with numeric values in design view of my query. The only issue I am having is that the query is not showing totals when one of the fields does not have a numeric value in it. How can I show the total numeric value regardless of the null value? Thank you:cool:

View 1 Replies View Related

General :: Adding Numeric Values In A Field In A Query

Feb 25, 2013

I am writing a sports database and have a query that displays a seasons fixture list in a form. One of the fields shows the points from each game played. (either 3 for a win, 1 for a draw etc). I can not get these points to be added up and displayed in a form along side the fixture list.

View 9 Replies View Related

Queries :: Wildcards - Find Any Number Of Numeric Values

Oct 3, 2013

How do you search for any number of numeric characters using wildcards? The # symbol only searches for 1, and * obviously includes letters.

Example:
I have:
D1234
D3
D5336767
D123F

My search should only retrieve the first 3 values.

WHERE FieldName LIKE 'D[0-99999999]' does not seem to work.

View 5 Replies View Related

Sorting Table / Query - Ordering Numeric Values

Dec 30, 2013

I currently have records that end with a letter and 2 numbers. For example, A1, A2, ... , A10, A11. When I try to sort my table/query by these values, A10 & A11 come before A2. It seems that it is sorting by the first digit shown. Is there any way to fix this quickly within table/query properties so that this can be displayed in proper numeric order?

View 7 Replies View Related

Queries :: Pull Data Values In Array To Become Grouping Headers In A Report

Apr 23, 2014

I have a table with application records. One of the fields captures schools the applicant will work at. This field stores data in comma delimited format. There could be 1 school name; there could be 5 school names.

My ultimate goal is to build a report which shows me records of all applications, grouped by school choice. I want to see: School A was selected by 5 people, School B was selected by 7, etc.

Is it possible to write a query from this table that will enable this?

View 4 Replies View Related

Sort Subform Records On Numeric Values Stored In A Text Field On Main Form

Sep 25, 2015

I have developed a database but have had difficulty with sorting data within subform of a main form. The subform displays the related tasks that correspond with main form that has been selected. Within the main form I have also created a text field that defines a particular sorting sequence of the tasks found within the subform which is titled Task Sequence. What I would like to do, is use the Task Sequence field to sort the order of tasks within subform. I have tried: IIf([ID] Is Null, 0, Val([Task Sequence])) within the Advanced filter/sort but either it shows only the first task defined in the Task Sequence or it wants to filter the main form and not the subform.

View 14 Replies View Related

How Can I Remove The Last Alpha Characters In A Query?

Aug 16, 2007

I have field called [LOCATION] that varies in length. I need to remove any alpha characters at the end, if there are any. Listed are some examples.

[LOCATION] [NEEDED]
PA 109S 55W 2N 1A PA 109S 55W 2N 1
WC 155A WC 155
PB4 68N 1UNH PB4 68N 1
B 3E 1P B 3E 1
XB1 203S 14W 2N 6PU XB1 203S 14W 2N 6
PB1 6 PB1 6
Thanks!

View 5 Replies View Related

Incrementally Sequencing Two Alpha's In A Query

Jun 28, 2006

In a query I need the ability to cycle two alphabet characters in sequence. First pass is "AA", next is "AB" etc. After "AZ" it would cylce to "BA". After "ZZ" it would return to "AA" and repeat the sequence again. Any help would be greatly appreciated!

View 1 Replies View Related







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