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


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.


1 1
2 2
3 3

2 1
2 2
2 3

View Replies


Subform Line Numbers

Nov 17, 2004

Hi! This is my first time writing in. I have been struggling with Access for the past 2 months... I am stuck with a problem. I have a variation of the Order form in the Northwind Example, with a subform called OrderDetailsExtended. The primary keys for the OrderDetails Extended subform and its corresponding table are the orderNumber (which is the link with the mail Orders form) and Item. Basically, my question is.. each time i want to add a new record, I want the Item number to automatically increment (but i am not using autonumber for this). The item field is currently disabled.

However, each time i enter a new record, the item number automatically becomes 0. And if I close the form and reopen it, the number changes to 1... Here is the VBA code i used...

Private Sub ProductCode_Enter()

Dim vItem As Variant
vItem = Nz(DMax("Item", "orderDetails", "orderNumber = ' " & Me.orderNumber & " ' "))

If Me.Item = 0 Then

If IsNull(vItem) Then

vItem = 1


vItem = vItem + 1

End If

Me.Item = vItem

End If
End Sub

Please help me....

View 3 Replies View Related

Queries :: Function To Output Line Numbers In A Query - YTD

Apr 18, 2014

Is there a built in function which can be used to create line numbers in a query?

I've written a query to calculate year to date (YTD) points for yachts in a series of races and sorted it in descending order - so yacht 1 is coming first, yacht 2 is coming second etc. I'm looking for a way to add sequential numbering (starting at one and increasing by one for each line) into the query to represent their YTD places. Or this this something that should be left to the report which uses the query?

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

Numbers Stored As Text Convert To Numbers?

Jan 10, 2007

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

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

End If

Even when text250 =8 label253 is visible.

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:

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

Modules & VBA :: Calculate Numbers In Query

Mar 1, 2015

I am looking to calculate numbers in query like this if the field name in query is Unit and the second field name is amount and i want that the amount field to calculate the unit field on this criteria..Suppose if total unit is 393 so the calculation should be like this;

First hundred should be calculated with 100.00 X 5.79 = 579.00
Second 200.00 X 8.11 = 1622.00
Third 93.00 X 12.33 = 1146.69

Total should be displayed in the amount field = 3347.69

View 14 Replies View Related

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.


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:

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

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


View 3 Replies View Related

Modules & VBA :: Finding All Numbers In A Range?

Jul 16, 2014

I need to create a very simple database that would just store records and produce a couple of reports. I have three tables: one with the roster , one has records of the inventory items people from the roster receive and another one contains types of inventory we have. Everything is very simple except for one part. Every time we make a record of an inventory item given to someone, it requires not only employee id and inventory type from the existing tables. It needs us to enter a serial number of an item. This serial number contains a letter and a number.

Looks something like this - M100. Many people receive a consecutive set of inventory items. For example, from M100 to M150. There is no way to have a separate table with all serial numbers because they constantly change. That is why we need to have two text boxes that would allow us to input a range of serial numbers or just one number. Then the program should separate numbers from letters, evaluate the range, create new records of numbers and then put new numbers and a letter back together into one field in the table where we have all inventory records.

I found the following code online that allows me to find numbers within a range, but it only works for numbers.

Dim varRange As Variant
Dim lngLow As Long
Dim lngHigh As Long
Dim lngCounter As Long
DoCmd.Hourglass True


I then found a piece of code that is supposed to separate numbers from letters, but I can't find a way to make it work.

Public Function FindNum(strName As String) As String
Dim strTemp As String
Dim i As Integer
For i = 1 To Len(strName)
strTemp = Mid(strName, i, 1)
If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then
FindNum = Right$(strName, Len(strName) - i)
End If
Next i
End Function

how to alter the code to make it work for my specific situation.

View 6 Replies View Related

Counting Frequency Of Numbers And Comparing To Frequency Of Another Set Of Numbers In Same Row

Oct 28, 2014

I have a table with fields like this one but the weeks go all the way up to 52. What I am trying to do is count the number of consecutive zeros and if it is more than five, count how many of the following fields have a number in them and if that number is less than the number of zeros preceding it identify that person.

For example Joe would be identified below because he had 6 consecutive zeros and then he had 5 weeks of numbers immediately following the string of zeros. Bob would not be identified because he had 5 consecutive zeros and then 5 sets of numbers immediately following the string of zeros so the zero frequency isnt higher than the number frequency immediately following.

Date of Hire


View 5 Replies View Related

Modules & VBA :: Converting Dates Into Week Numbers

Oct 2, 2013

I am trying to write some VBA to convert a date into a week number.

My work colleagues do not not what system is used to define the week numbers. So I have gone through all their records for a few years and deciphered this pattern:

I need the code to define that there are 52 weeks in a year. The last sunday of Dec is the beginning of Week 1 for the following year. Except when it is a leap year where Week 53 exists. otherwise it only goes up to week 52.

Start day for the week is Sunday. End day is the Saturday.

Some examples to check against:

22nd December 2013 = Week 52 2013

29th December 2013 = Week 1 for 2014

21st December 2014 = Week 52 for 2014

28th December 2014 = Week 1 for 2015

20th December 2015 = Week 52 for 2015

27th December 2015 = Week 1 for 2016

25th December 2016 = Week 53 for 2016

1st Jan 2017 = Week 1 for 2017

Tried various methods already

iso 8601, wrong week start day

intWeek = DatePart("ww", datDate, vbSunday, vbFirstFourDays) Was right for 2013 but wrong for later dates.

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


View 10 Replies View Related

Modules & VBA :: Auto Numbers When Priority Change

Jul 3, 2014

I have a very basic application to manage the tasks. In the main form "Tasks" one of the field is the priority (Order by) and I have this example:

Task Priority

B 1
A 2
C 3
E 4
D 5

If the user change one priority, I need a code to automatically change the others too respecting the new priority order. For example if the user change the priority 3 to 1 for the task C, the other numbers have to change to become:

Task Priority

C 1
B 2
A 3
E 4
D 5

There is a way to do this?

View 5 Replies View Related

Modules & VBA :: Declare Variable With NOT Rounding Numbers

Oct 20, 2014

I need to declare a variable (max_nog_leveren ) but that variable may not be rouded or only after 5 digits so as example 15,xxxxx

I am now using it as a "Integer" but that is not working very well..

Dim iRet As Integer
Dim strPrompt As String
Dim strTitle As String
Dim max_nog_leveren As Integer
Dim strQuery As String
Dim nettohoeveelheid_toevoegen As String

[Code] ....

View 5 Replies View Related

Modules & VBA :: Calculate Total And Convert Numbers To Words

Apr 7, 2014

I have a form that have textbox that calculate total and then i want to convert numbers to words. Iv got the code with function currencytotext but when i set the code source of another textbox to:


I have an error. I saw this on internet but i think bcz I am using access 2013 may be the syntax is different a bit.

View 5 Replies View Related

Modules & VBA :: Database For Quotations - Modifying Sequential Numbers

May 29, 2015

I have a database for quotations. The database automatically generates a new quotation number every time a new quote is started. This works great, and I am very happy. My next task is to allow the employee to pull up a quote that has already been generated and edit it. I would like for the new quotation process to be followed step by step, but with all of the information already filled in.

This will allow for any edits that need to be made, and keep from having to re-enter a lot of data. I want one thing to change, which is the QuotationNumber. It is currently formatted by "yyyymmdd-01" for the first quote generated on that day. I want the edited quote to have a QuotationNumber formatted by "yyyymmdd-01a". For every edit that letter change going through the alphabet in order. How would this new QuotationNumber code differ from that of the Other?


View 10 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.

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:


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

Modules & VBA :: Assigning Reference Numbers To Records Where Same Customer Appear

Nov 21, 2013

I'm trying to put together a DB for creating notifications to customers. The source file is pulled out from another system as an Excel file, which is then uploaded to Access via VBA. The user runs a query, and then sends the results (customer information) to an Excel file where we have set up a mail merge document (the notification itself).

A customer may appear in multiple records (having multiple accounts), but will only receive one notification.

What we're trying to do is give each notification an unique ID that will become its document reference number once the document is created. The number will need to be the same for all the records in which the customer appears, and they will need to be consecutive, as the documents themselves will be stored in PDF form later on in the process.

So I would like to assign an unique ID to each customer, each time it shows up when querying the uploaded source file (the query is done in a form, that has a sub-form for showing the results). Then, when exporting the query results, they will be also copied and stored to another table, along with the assigned reference number, for monitoring purposes.

The Access file itself will be split and used by multiple users (up to 9 users at a time). In theory, each user will see their own customers (each customer account is assigned to a certain user), but the number assignation will need to also depend on when the query has been run. For example, if user 1 queries the DB at 9:30AM they will get records numbered 1000 to 1050; user 2 (who queries the DB at 9:31AM) gets numbers 1051 to 2100 etc.

View 2 Replies View Related

Modules & VBA :: Automatically Check A Box If Certain Fields Are Within Range Of Numbers?

Nov 2, 2013

I have 3 fields on a form PU_POINTS, SU_POINTS & 2_MILE_RUN_POINTS. The user enters a number 1-100 in each of these fields. If the user enters a number in any of the 3 fields of anything less than 60 then I want it to check a fail box. If the user enters all numbers 60 or more then i want it to check a pass box.

View 2 Replies View Related

Modules & VBA :: Make Table - Assign Custom Formatting To Numbers

Oct 6, 2014

I am using VBA to execute a 'Make Table' (named 'DT'). One of the fields in DT (named 'Dollars') contains numbers that have 6 to 8 digits; some are positive and some are negative.

These large numbers with no commas (or parentheses when they're negative) are incredibly hard to read.

Any VBA coding that will change both the "Format" and the "Decimal Place" Field Properties on the table I make (i.e. "DT"). I want to the Format property to read (#,##0.00;(#,##0.00)) and the Decimal Place property to read "2"). This will allow me to display a number like -10326786.41 as (10,326,786.41) which is incredibly more readable.

Is it possible to do this programmatically; I've search the internet high and low and could find anything remotely useful.

View 8 Replies View Related

Modules & VBA :: Sequence Numbers - Automatically Generate 5-character Value For Business Key

Apr 7, 2015

I need to automatically generate a 5-character value for my Business Key. Without any user interaction.

2 character -> from 01,02,03,04 to 05
3 character -> Sequential Number (001,002,003).

The Sequential Number must reset on each new day.

View 8 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("Letter")= myarray??
rs("corNumber")= myarray?

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

Copyrights 2005-15, All rights reserved