Modules & VBA :: Pull Numbers From String And Get Max Value?

Jan 3, 2014

I have a series of IDs in an 'articles' table stored as text, e.g.

hb-123456789-e-068
hb-123456789-e-0069
hb-123456789-e-70
hb-123456789-e-00027

and I'm trying to pull the max value of the number after the -e- for a given set of them. In this example, I'd want to return the number 70. I'm then going to use that to create the next ID and populate another field.

The IDs are not used as the primary key. And while the previous IDs used leading zeros inconsistently, new IDs will not have leading zeros.

Here's what I have so far, but it doesn't seem to pull the number after the -e- at all. I think this section here is the problem, even though the same logic works in a query:

Code:
selectedERef = Val(Right(rs![masterArticleID], Len(rs![masterArticleID]) - InStrRev(rs![masterArticleID], " - ")))

Code:
Public Function MaxArticleERef(hbID As Long) As Variant
On Error GoTo err_handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String
Dim maxERef As Variant

[code]....

View Replies


ADVERTISEMENT

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

Modules & VBA :: String Into Numbers - Using Array For Lookup

Jan 15, 2014

I have to decode a string into numbers and to avoid to find out the values for 47 options by select case I though about an array.

I want to decode

Number Letter
10 A
11 B
12 C
13 D
14 E
15 F
16 G
17 H
18 I
19 J
20 K
...

For example the string "ADEG" would give as result
10 13 14 16

So I would have to loop through the string and "decode" each letter into a number.

As I have still problems to understand array, need to define the dimension of the array, it has fix 47 entries to decode

Dim myarray (47,2) as variant
mayarray=(10,"A",11,"B",...)

Correct?

View 10 Replies View Related

Modules & VBA :: Define And Setup Array - Find Respective Numbers For A Text String

Apr 21, 2015

I need to find the respective numbers for a textstring when for

abcdefghijkl stand the numbers
79 81 82 83 84 85 86 87 88 89 91 92

The textstring to "decode" is for example is 'adgjk'

The result (79 83 86 89 91) should be added into a table by Looping.

rs.Addnew
rs("Letter")= myarray??
rs("corNumber")= myarray?
rs.update
rs.movenext

Something like this.

But I cannot define and Setup the Array, which should be the best way for doing this.

The Array does not change its Content nor its Dimension.

Both, letters and numbers are strings.

View 14 Replies View Related

Using A String To Pull Up A Record On A Form

Feb 28, 2005

Sorry if this has been asked before but I have searched and couldn't find an answer

I have a string that is set on startup (username) and have another form(hidden) that loads on startup this form contains information on what forms and options the user can access. Just having a little trouble using the string to pull the correct record, the information in the string would be from the primary key field on the table.

Thanks

Ryan

View 2 Replies View Related

Queries :: Pull Text From String?

Apr 2, 2015

I have a text field in a Table and on a Query called "Notes" In that field that has data like below:

[04/02/2015:BD] Project is to be assessed by Solutions Planning
[03/27/2015:BD] Project prioritized
[03/14/15:BR] Entered to system

Im trying to find a way to pull just the most recent line of text, in this case

[04/02/2015:BD] Project is to be assessed by Solutions Planning

into the field next to "Notes" or wherever - an empty field in the query. I searched around, found some stuff and I was thinking of having the code look at the first "[" and count the length to the next "[" and pull out whats in between. Looks like the bracket causes issues in the module.

View 10 Replies View Related

Pull Out Part Of The String Within Cell

Sep 10, 2013

I'm trying to find a function in access that could do the following:

ZAB103-3012
ZAF405-HD-0001

Turn those cells into:

AB103
AF405-HD

Pulling everything to the right of the Z in the last step is the easy part but I can't figure out what function would be able to find the last "-" in the cell and pull everything to the left of it.

View 6 Replies View Related

IIF Statement To Pull String From Memo Field

Sep 12, 2006

I have a 3rd party database where I have no control over how the data is entered. I've been given the task of creating a Crystal report that would need to gather data from two databases. The link between the two databases in my report would be on a quote number. In one database, the quote number is in it's own field. In the 3rd party database, the quote number is stored in a memo field along with other data.
Here's an example:

4000 C7875
9003267 T7761
90000167/4010/T6895
4010 T7152A
TPCA #1756/2914

The data I'm after is
C7875
T7761
T6895
T7152A
*No data from the 5th row since the data after the "T" isn't numerical

So far I'm thinking of using an IIF statement to check for the existance of a C or T, then if true, use a nested IIF statement within the first to check for numeric, if true, use the Mid function to pull out the quote number.

My first concern is this could become a complicated IIF statement and was wondering if there was another direction I should be looking in acheiving this.

My second concern is if I go with this method, some of the quote numbers have a space after them, some of them have no space after them, some may even have a "/" after them. How would I go about accomplishing this?

View 5 Replies View Related

Pulling Numbers From A String

Feb 12, 2006

I have a column of data set up as follows:

Review July 2003
Review October 2003
Review July 2004
Review October 2004
Review July 2005
Review October 2005...and so on.

I know the data is not normalized, but if I want to use the Year part of the string in a WHERE clause, how would I do that? or in other words how do I extract the Year from the string.

Thanks

View 5 Replies View Related

Join On Fractional Numbers/String?

Jul 6, 2006

Greetings all:

I have a database tracking customers and inventory, and I've tried to add in a feature where I can meet customers needs with future inventory. Key to this process is three tables:

tblCustomer - containing my customer information
tblInventory - with inventory data including a part number
tblNeeds - a table I've created for this purpose, containing CustomerID and Part Number.

Here's the premise: when speaking with a customer, I may learn they want a product I don't currently have in inventory, or may have never had in inventory, but that I may get in the future. I do this "on demand" in the form of a report, and it works really well ... except:

I have found that several similar products from the same supplier have similar product part numbers. What I would like to do is be able to match my query on a fractional part of the part number. In all cases, the part number would be the same over the first six characters (in a text field). Any ideas how I might do this? In my current query, I use a join, but I can't do that for fractional strings....

I've attached the query I am currently using (and running a bound report from this query):

SELECT DISTINCT tblCustomer.CustID, tblCustomer.CoName, tblInventory.ProductDescription, tblInventory.ProductPartNumber, tblInventory.UnitsAvailable, tblInventory.ProductID
FROM tblInventory INNER JOIN (tblCustomer INNER JOIN tblNeeds ON tblCustomer.CustID = tblNeeds.CustomerID) ON tblInventory.ProductPartNumber = tblNeeds.PartNo
WHERE (((tblInventory.UnitsAvailable)>0));

And what I want to do is match the tblInventory.ProductPartNumber on the tblNeeds.PartNo by matching only the first six characters....

Thanks in advance,

Don

View 3 Replies View Related

Queries :: Extract 3 Numbers From A String

Jul 11, 2014

I am trying to extract the first instance of 3 numbers from a string using an update query to update another field in the same table(Master Equipment) titled "Code". the field is called "Equipment". the strings vary in length as well as the location of the 3 numbers needed, but I have examples of all possible locations the 3 number series could be located below. As you will notice, some fields do not contain 3 numbers together, for these I don't want to return a value.

07-FLP58351
07-MFDP58201
1PBE97601
1PT91105-2
2WPF/FF438582
A-WR-1
AAV58101-01
AC47201-01

View 13 Replies View Related

Modules & VBA :: Automate Line Numbers And PO Numbers

Aug 24, 2014

I have 2 fields that I would like to automate if possible

One field is called "p/o number" and another field called "line no"

These fields are part of an ordering database

Let say I have 200 items to purchase form 10 suppliers

And form example 20 items from each supplier

What I do at present is put the order number on each line item and the line number

example

p/o number line no

1 1
1 2
1 3

2 1
2 2
2 3
2 4

What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.

Example

1 1
2 2
3 3

2 1
2 2
2 3

View 5 Replies View Related

Queries :: Returning Set Of Numbers From Custom Formatted String - Incorrect Use Of Join?

Mar 20, 2015

I suspect my design is flawed

Code:
SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address
FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address])
WHERE (((tblData2.MyYear)<1990))
ORDER BY tblData2.LineNum;

The NumbersOnly function returns a set of numbers from a custom formatted string. The above fails on the join (I think) but maybe there's another way of doing it?

View 4 Replies View Related

Modules & VBA :: Using Combo Box To Pull Data Using (Like)

Jul 17, 2015

What I want the form to do is filter on a column where I have combined 3 columns of actives together. So the form will filter if one of those values is any of those three columns. So I have a macro setup right now trying to filter for anything "Like" what has been selected in the combo box. Here is what I have in the where condition of the filter macro:

Code:

((([Active1] & " " & [Active2] & " " & [Active3]) Like "*" & "[me.cboactives]" & "*"))="'" & [Screen].[ActiveControl] & "'"

This is in Access 2013

View 2 Replies View Related

Modules & VBA :: Can't Pull Form Text Box Value Into Query

May 21, 2015

I have a perplexing problem. I'm creating a simple db to enter borrower audit checklist data with the following tables:

Questions -- A list of 17 pre-defined audit questions, with fields QNum and Question
Audits -- One record for each audit (pk AuditKey is an Autonumber), and some borrower fields
AuditQuestions -- Linked to Audits, with pk of AuditKey and Qnum, and a Question and Answer (yes/no) field

The Audits form has the Audits table as its datasource, and an AuditQuestions subform. When I go to a new Audits record, there are initially no subform records attached. When I enter a borrower name, the field AfterUpdate event runs the following code:

Code:
DoCmd.OpenQuery "LoadAuditQuestions"
Me.AuditQuestions.Requery

The SQL for LoadAuditQuestions is

Code:
INSERT INTO AuditQuestions ( AuditKey, QNum, Question )
SELECT [Forms]![Audits]![AuditKey] AS Keyval, Questions.QNum, Questions.Question
FROM Questions
ORDER BY Questions.QNum;

I start out with both Audits and AuditQuestions tables empty. When I run the code by entering a borrower name, I get, "... can't append all the records in the append query ... didn't add 17 records due to key violations". It acts like it's getting a null value from Forms!Audits!Auditkey, but if I select the Debug option and check the value from the Immediate pane, it shows a valid number. Also, if I run the query manually with the form open, it loads the questions correctly.

The first Audits record is loaded with questions in the subform so you can see the desired results. To see the problem, go to a new record, enter a name, and hit tab. Another piece to the puzzle is, if you do this on an existing record, it works fine: Click No in response to the error message that appears, then click End on the Debug message. Go to a different audit record, then come back to the one you just created. Change the name, and it works correctly. It's as if it doesn't know what the AuditKey is the 1st time, but if you check it in debugger, it is loaded. In fact, I put in code in the AfterUpdate event to plug the LoanNumber field with the Forms!Audits!Auditkey value, and it worked, but the subsequent query still failed.

View 3 Replies View Related

Modules & VBA :: Pull X Number Of Records From A Database

Jan 23, 2015

I need to first generate a random number between 1-4.

Then I would like to use that number to pull that many records out of the database; the records pulled can be random, or whatever.

For example, we have 4 people and my goal is to choose a random # of records to pull for each person. So, for person #1, it might only pull 1 record, then for person #3, it may pull 2 records, etc.

Ideally, it wouldn't pull the same # twice when run for that "session".

After this, I'm hoping to generate all those results into emails (eg for person 3 it would generate 3 emails, etc)

Currently it's all done manually and I think it's doable with Access.

I have some code already for generating "1" email message, but do not know how to pull X number of emails all at once. (X is the random #).

View 2 Replies View Related

Modules & VBA :: Pull Data From Query (or Table)

May 4, 2015

I have a table that has four columns. Column 1 had people's names, column 2 has their email, 3 has a category, and 4 has their office.

I have a form with radio buttons, When you check a radio button and press OK it will display the e-mails for the people selected. Problem comes when it's by category, since categories are not unique to one person. I want to click the category radio button and have it display every person under that category, concatenated with a comma.

Problem is I can't make a listbox because it will show the same categories several times instead of just once, so I want the radio button with the category name on its label, and then in the code I need to tell Access to go to the table and search for the e-mails from a specified criteria, the category.

How do I do this?

View 1 Replies View Related

Modules & VBA :: Search And Pull Data From Db Stored In A Shared Path

Dec 30, 2014

I have two different database files. One is 2010 ".accdb" format where I have created a form and the inputs to the form is getting saved as records to an access.mdb file in a shared path.Now if the users want to edit the existing record I should allow them to search their previously submitted record with a unique ID number.

I know it is possible when we have both the form and table in the same db. But I want to know whether it is possible to search with a unique ID and pull the data from different db in a shared path using a command button?

View 5 Replies View Related

Modules & VBA :: Updating Dynamic Combobox - Pull Data From Table

Oct 31, 2013

Right now I have a subform with a combobox that pulls it's data from a table. I want the user to either select an existing item or type in a new item and have a macro create the new table row. What I have right now works in the sense that it prompts the user if they want to creat a new item and the new item is created (and I can see it in the combobox list), but I'm still getting an error saying that the item does not exist in the table forcing the user to manually select the newly created list item they just typed in.

Code:
Private Sub MaterialCostCode_NotInList(NewData As String, Response As Integer)
Dim rst As DAO.Recordset
'Update value list with user input.
On Error GoTo ErrHandler
Dim bytUpdate As Byte

[Code] ....

It appears that the new item doesn't always show up automatically and requires the form to be refreshed, so now I need to figure out how to get it to consistently appear right away without a refresh.

View 3 Replies View Related

Modules & VBA :: Pull One Max Date From Value In Four Different Text Boxes On Continuous Subform

Jul 17, 2015

We're looking at a way that we can easily display what stage our clients' email marketing campaigns are at - in one section of our CRM our Campaign Manager will enter information on whether the campaign has broadcast (ie: emailed out to the required circulation list(s)), if we're waiting for artwork from the client or if we're chasing for that information.

I have four text boxes (date format) hidden on a subform that I need to pull ONE max date from.

So, as an example:

Email1 (our first email to the client chasing for artwork) = 01/01/2015
Email2 (our second email to the client chasing artwork) = 08/01/2015
Email3 (our third chase email) = 15/01/2015
Broadcast (the date the email campaign was finally sent) = 29/01/2015

I would need some code to show in an unbound textbox "Broadcast: 29/01/2015"

But, on the other hand, if the dates looked like this:

Email1 = 01/01/2015
Email2 = 08/01/2015
Email3 = Null
Broadcast = Null

I would need some code to show the following in an unbound textbox "Last Chased: 08/01/2015"

So we can easily see the status of our marketing campaigns at any stage in the process.

I've tried using IF THEN ELSE statements in the subform's On Current event, but that populates the information from the record you've selected across all the other records on the sub as well.

So I'm wondering if it's worth setting the Control Source of the unbound textbox I want to display the campaign status in as a bunch of nested IIF statements instead?

View 14 Replies View Related

Modules & VBA :: Pull Data From A Table That Meets Number Of Conditions?

Jul 3, 2014

I have a module that is in Excel that is connecting to a back end database. I am trying to pull data from a table that meets a number of conditions. With the following statement, it is not returning any records,even though I know there are records that meet all of the conditions. I suspect the problem is with the last condition. In that condition I am trying to say that pull in records where it has been at least 14 days since the last review.

Code:
strSQL = "SELECT tblsojrol_oc.* FROM tblsojrol_oc WHERE tblsojrol_oc.[Status] = 'Pending' AND tblsojrol_oc.[1st Review Date] IS NOT NULL AND " _
& "tblsojrol_oc.[3rd Review Date] IS NULL AND (DateADD(Day,14,tblsojrol_oc.[2nd Review Date])) >= #" & dt & "#;"
objRs.Open strSQL, objConn, adLockReadOnly

View 14 Replies View Related

Modules & VBA :: How To Consider Numbers As Indexes

Jul 15, 2015

I have sheet names stored in a text field.

sample data: (assume the field name as SheetName)1 (considered as an index) MySheet (considered as a Name)
How to handle them both in a single statement:vartype(SampleData!SheetName) returns 'Text' as Datatype for all values.

How to consider the numbers as indexes?

View 3 Replies View Related

Modules & VBA :: Using Between And Negative Numbers

Sep 15, 2013

Code:
If Me.Text250 = BETWEEN -15 And 15 Then
Me.Label253.Visible = False
Else
Me.Label253.Visible = True

End If

Even when text250 =8 label253 is visible.

View 8 Replies View Related

Modules & VBA :: Using Mid In A String

May 1, 2014

I'm trying to get a value from a spreadsheet to import into my MS Access database. Currently I am trimming the spaces/carriage returns from it but need to strip some more data from the value.

Here is my code.

trimmed_department = Trim(Replace(new_department, vbCrLf, ""))

Example value being "123 Point 5 Finance and Accounting"

I want to use Mid (I think?) to remove the "123 Point 5" (it is always the same with no exceptions) but don't know how to use it as I don't know how to use multiple parameters within a string.

View 8 Replies View Related

Modules & VBA :: Negative Numbers In Calculations

Nov 18, 2014

Ok i know negative numbers can be a bugger in calculations:

Code:
Function FEV1pred(height As Double, ClientID As Double) As Double

Dim a As Double
Dim Age As Integer
Dim DOB As Date
Dim TestDate As Date
Age = DateDiff("yyyy", DOB, TestDate) + CInt(Format(DOB, "mmdd") > Format(TestDate, "mmdd"))
'In this case the clients Age is 39
a = -0.0244

'Now for the fun stuff...

FEV1pred = a
'returns -0.0244 so i know the dim a holds the neg number
FEV1pred = (a * Age)
'returns 1.708 (which is incorrect, how this calculation is reached.)
FEV1pred = (a * 39)
'returns -0.9516 (which is correct)
End Function

So something about the dimming of Age seems to be doing something to the equation but i can't work out what?

View 1 Replies View Related

Modules & VBA :: Converting Text To Numbers

Sep 19, 2014

I have a short text field where numbers such as "15.00", "2233.56", "-300.00" are stored. Now I want to convert the text field to numeric so that I have actually 15.00 or -300.00 stored as a double. I am going to do calculations on these fields. How can I achieve this conversion?

View 4 Replies View Related







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