Use Vb To Create New Records Based On Number Range

Apr 16, 2014

I am looking for a way to use vb, or any other way, to allow my users to enter a railcar initial and then a number sequence and add new records to the end of the table.

For example in a form the user can enter the car initial and the number sequence
Railcar initial: GATX
Number sequence : 290001 - 290100

I would like a way to create a temp table that then has 100 records
GATX 290001
GATX 290002
GATX 290003
etc etc.

I can then use this temp table in an append query to add them to my main table.

View Replies


ADVERTISEMENT

Create Multiple Records Based On Date Range

Jun 28, 2006

I have developed a Debt Management program for work but I am stuck on a feature that we need to add to it. Basically I have a table which holds "planned future transactions". These are ad hoc transactions that need to be added from time to time to the clients budget. For example the client might have $500 a quarter for school fees for the next two years or he might receive a bonus every six months for the next 5 years. Currently we are putting these in manually but it is tedious and is causing complaints. The feature I require is to be able to add a record such as the school fees, nominate that this fee is paid every three months for the next 8 quarters and get the system, say through an append query? to create 8 records, each with the appropriate date 3 months after the previous one.

Any suggestions or assistance would be appreciated.

Thankyou

View 1 Replies View Related

Modules & VBA :: Count Number Of Unique Records Based On Range Of Date

Jan 19, 2015

Code:
' count records in query
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim beginDatum As String
Dim eindDatum As String
Set db = CurrentDb

[code]....

View 4 Replies View Related

Modules & VBA :: Create Multiple Records Based On Date Range - Button Click Event

Jul 31, 2014

I work for a company that manufactures home appliances & electronics. When those products fail within the warranty period, we are obligated to repair the units for the customers.

We have around 200 factory technicians who make those repairs. Each tech is able to make approximately 8 repairs each day. We have a dispatching system that assigns the repairs to the techs based on their availability each day. But the problem is that we have to manually enter and adjust the schedule for all 200 techs every single day, and this takes a lot of time. And of course technicians get sick, take vacation, etc, so we have to adjust the schedule so no techs will be assigned calls when they're off.

The problem: For example, currently when a technician takes off for 2 weeks, the user has to enter 14 individual records for the tech, which is somewhat time consuming. What I need is to program a button click event to determine the two dates (startDate & endDate) and append multiple records from one single entry in the form for each date in between and including the two date fields.

I know I'll probably need to create a loop that will loop through the two dates on the form and append a record for each day, so I can then cross reference the dates to the master schedule dates to make sure that no availability is opened for the techs taking time off.

View 3 Replies View Related

Tables :: How To Create ID Based On Number Of Records

Nov 26, 2014

I have a table with an empty column called ActionID. I need to generate a numerical number that begins with 5000 and goes up by 1 number on each saved record. I have a basic form that links all of my other field to the table except the one I need to generate (ActionID) but will need that number saved to the table once its created.

View 11 Replies View Related

Forms :: Create Set Number Of Identical Records Based On User-defined Text Box

May 12, 2015

I'm trying to create a database for a construction company, as an experiment more than anything, and my problem is this:

I need to create a set of identical records in the table "buildings" based on the information entered into a text box by a user on the form for registering a new project. I want each record to be identical except for the "plot number" field, which I want to start at one and increase to the number of buildings defined in the text box.

"Projects" is one table and "Plots" is in the other.

View 1 Replies View Related

Value Based Upon Number Range

Apr 14, 2006

I am creating a query which determines the number of days a payment is delinquent. Is there a function I can use in a query field that would look at the numeric field and if the value of the subject field was:
< 60 Days "30-59 days"
< 90 Days "60-89 Days"
<120 Days "90-119 Days"
>=120 Days "120 Days +"

It was my understanding that the IIF function tests for a condition and can only return yes/no results. I seem to remember that SQL had something like a least or most function that would allow me to set maximum value for range of days. I apologize that my question is confusing and hope you can understand what I am trying to accomplish.
Thanks for any help

View 2 Replies View Related

Queries :: Create Sums Based On Weekly Range?

Jun 26, 2014

I'm creating a query for a someone who wants quantities summed by a weekly range and correspond to a week number. This person wants Access to do something that is a simple function on Excel.

I have no clue how to do this or if Access is even made to do something like this.

This is an example of what I have so far

Quote:

P/N QTYWork DateWW (Work Week)
25COMP16/11/201428
25COMP26/20/201425
25COMP27/9/201428
25COMP36/20/201425

....And so on

My code is

Code:
SELECT dbo_JBKLG.JKPRT AS P/N, dbo_JOB.JBQOR AS QTY, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4)) AS [Work Date], DatePart("ww",[Work Date]) AS [WW]
FROM dbo_JBKLG LEFT JOIN dbo_JOB ON dbo_JBKLG.JKJOB = dbo_JOB.JBJNO
GROUP BY dbo_JBKLG.JKPRT, dbo_JOB.JBQOR, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4))

There are more part numbers and the data from the other parts must be in the same format in the same query.

Quote:

P/N QTY WK WW

25COMP 15506/09-06/15WW24
25COMP 22706/16-06/22WW25
25COMP 16006/23-06/29WW26
25COMP 20006/30-07/06WW27

.....And so on

View 14 Replies View Related

Queries :: Assigning A Number Based On A Range Of Dates?

Mar 19, 2013

I have a table called StockTable with the following fields Location, Status, Serial, Make, Model, LastDate, DotNumber

I also have a table called FCDateRange with three fields

DateStart DateEnd and DotNumber

For example

3/7/13 - 3/13/13 - 1
3/14/13 - 3/20/13 - 2

Im trying to figure how to write a query that if the lastdate from the StockTable falls during the DateStart and DateEnd fields it will assign it the number in the DotNumber field

View 2 Replies View Related

Forms :: Auto Generate File Number Based On Number Of Records In Year

Jan 21, 2014

I have a form [IUDATA]

I have a add record button.

I have a date field [DATEIN]

I have a text field [DRPNO]

If the [DPRNO] field is empty, I would like the user to have the [DPRNO] field be automatically populated after the user enters a date.

I'd like the format of [DPRNO] to be "dpr YY-XXX"

Where:
YY is the year of the [DATEIN] field and
XXX is number of records in that year.

So for example, if it was the 4th record with a 2013 date the [DPRNO] would be dpr 13-004.

View 12 Replies View Related

Modules & VBA :: Create Unique Reference Number Based On Field Values For Record

Jun 18, 2015

I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.

The first field is called Operation Number.

The second is Bag Number.

The reference number needs to be in this format: 19C.3.1

Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.

This is kind of like library catalog numbers. Not sure how to do this.

View 2 Replies View Related

General :: Create X Number Of Records With A Button

Apr 8, 2015

I know this has probably been gone over, but I'm just looking for a super-simple way to put a button on my form that will create a number of records equal to the number of days in the month listed in a field. For instance, if MyField is "4/1/2015", I'd like the system to create one record for each date between 4/1/15 and 4/30/15.

I have scoured the forums, and I find many threads on creating multiple records, but none of them deal with the same type of thing I need. I have a field, called MyField, and I have a table called MyTable with a column called MyDate. I want to enter a date into MyField, then click a button, and the button will run code/macro/whatever that will create multiple rows in MyTable, one for each date in Month([MyField])

View 8 Replies View Related

Queries :: Create New Records Based On Query?

Dec 24, 2013

How do I create new records based on the results of a query?

I opened the query results in a form then I want to have a button that basically creates new records based off the query data ... is this possible?

View 1 Replies View Related

Modules & VBA :: Create X Number Of Duplicate Records According To A Field On Subform

Jun 13, 2013

I have a code that works great from the parent form but I decided to change the format and call it from a lostfocus event in the subform instead. Now I keep getting error 3314:"You must enter a value in the tbGuests.LastName field".

The code should copy the parent form fields and create x number of duplicate records according to a field on sub-form. It then runs an append query to add the information from the subform.

Code:

Private Sub GuestsInParty_LostFocus()
Dim partymsg As Integer
Dim dbs As dao.Database, rst As dao.Recordset
Dim F As Form
Dim intHowMany As Integer
Dim intCounter As Integer

[code]....

View 1 Replies View Related

Create An Entry In A Table Based On Records Matching

May 23, 2006

Where a many to many relationships is resolved with a linking table...Is it possible to create an entry in the linking table where the two linked tables have the same value e.g. create a entry in the linking table where client table has same value as a job profile table i.e. both are in insurance. Therefore want to create an entry in the middle table with foreign keys from either link automatically

ta

View 1 Replies View Related

Number Of Records Based On Table Value

Jul 14, 2006

We want to send letters to clients on behalf of our salespeople on a weekly basis from a list of clients in a table. But some salespeople will have 5 letters go out and some will have 20 go out (depending on what amount the salesperson is able to followup on). I can store the weekly letter quantity number in the table no problem, but I am just not sure how to have the query show 5 letters for Bob and 20 letters for Sue.
I know about the "Top N" setting in query design, but not sure how to set it to read from the table.

Any ideas?

View 4 Replies View Related

Excluding Records Based On A Number Of Criteria

Sep 20, 2005

Hello all,

I'm having some difficulty obtaining the answers I want from my query. I'd have been more comfortable using filters in excel to find the answers, but my dataset is too large in this instance.

I devised a query which I thought should achieve the results, but based on a subset analysed in Excel the answers are significantly different.

My table has about thirty fields, the query I've produced is this:
SELECT AH_orphans.Product, AH_orphans.Comments, AH_orphans.ESP, AH_orphans.[ESP Parts],
<snip, all other fields in table>
FROM AH_orphans
WHERE (((AH_orphans.ESP)="n") AND ((AH_orphans.FRU)="y") AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a")) OR (((AH_orphans.ESP) Is Null) AND ((AH_orphans.FRU) Is Null) AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a"));

What I'm hoping for is the subset which match the following conditions:
- ESP field is not "Y". Possible values Y, N, null
- FRU field is not "N". Possible values Y, N, null
- Only those records from EntitlementName which include the substring 'uptime'
- Only those records from Account which start with the subtring nation
- Only those records from Type which include the substring primar
- Only those records from [Is a parent?] which equal #N/A.

Asking the same questions in Excel I get around 1.5k records. If I use the quoted query on the same dataset in Access I get precisely 11.

Thanks for reading this far! If anyone can spot the flaw/s in my query, or indeed advise that I'm going at it completely the wrong way I'd be very grateful of some advice.

Cheers,
Alex

View 4 Replies View Related

Queries :: Create Query To Find Certain Records Based On Data In CSV File

Aug 14, 2014

This may not even be possible, but I am looking to create a Query that can locate records in an Access Table based on 2 columns of data that I have stored in a CSV file.

My table contains several fields, 2 of which are "Dept" and "SKU" and has over a million records.

My CSV file contains 3 fields: "Dept", "SKU" and "Total" - total being the number of times that particular Dept/SKU combination is used.

I need to be able to parse the dept/sku values from each row in the CSV to the query and locate only the records that contain the same values in the Access table.

The plan being to delete out those that are identified by the query.

View 6 Replies View Related

Need Report To Show A Number Of Records Based On A Value In A Field Of A Table

Aug 9, 2006

Hi,

I have a table with a field that shows the number of pieces that a parcel contains. It looks like this:
ParcelID, Pieces, Description

Data example:
1001, 5, Jackets
1002, 10, shoes
etc

I need to print labels for each piece that shows to which parcel it belongs. The report will look like:
1001, Total pieces = 5, 1 of 5 pieces, Jackets
1001, Total pieces = 5, 2 of 5 pieces, Jackets
1001, Total pieces = 5, 3 of 5 pieces, Jackets
1001, Total pieces = 5, 4 of 5 pieces, Jackets
1001, Total pieces = 5, 5 of 5 pieces, Jackets

and
1002, Total pieces = 10, 1 of 10 pieces, shoes
1002, Total pieces = 10, 2 of 10 pieces, shoes
1002, Total pieces = 10, 3 of 10 pieces, shoes
1002, Total pieces = 10, 4 of 10 pieces, shoes
1002, Total pieces = 10, 5 of 10 pieces, shoes

If I could populate a table where the number of records equal the value of the pieces column then it's a pretty simple report but I can't figure this out.

Can anyone help?

View 1 Replies View Related

General :: Count Number Of Records Based On Certain Criteria Using DCount

Jun 5, 2014

I have a report that I am trying to complete based on several queries. I am trying to count the number of records based on certain criteria and using the following DCount.

=DCount("[Calculated time]","IPG1","[Calculated Time] <= 0.04" And [Ship-to party] In ("SN00207PJZ","SN09162XXX","SN09324XXX"))

I want to count the number of IPG1 records that are under .04 and have the Ship-to party of the ones listed. I have tried everything that I can think of to get it to work but can's seem to get it to. I figure it's something easy but I don't see it.

View 4 Replies View Related

Forms :: How To Get Subform To Grow Or Shrink Based On Number Of Records Returned

Aug 4, 2014

All; using 2010. How can I get a subform to grow or shrink based on num of records returned? Its a continuous subform. Tried changing Vertical and Horizontal Anchors as well as some VBA suggestions but havent been able to make any of them work.

View 2 Replies View Related

Forms :: Based On User Selection In A Form / Create New Records For Subsequent Form

Feb 3, 2014

The user will be creating a new project that contains a bunch of releases. The releases have standard names which are stored in a table tbl_ReleaseNames It should be noted that the list of names is not static.

The user selects which of the releases pertains to their project and then based on their selections, new records would be created in tbl_RFP_Release and then a subsequent form would open where it would display each of these newly created releases where they could enter additional information. I thought of creating an unbound checkbox associated with each of the standard names, and then checking to see if the checkbox was checked and then creating the new records followed by opening up the new form.

View 6 Replies View Related

Job (ID) Number Range - To Be Queried?

Jun 11, 2007

Hi all,

I'm building a quick database, but some of the data to go into the database is quite... odd...

Anyway, the point is, I have one sheet that says

"Job Number 1200-1245" which would have all the same details, dates, etc.

Is there a quick and easy way to put in a range, say J1200-J1245, enter the data once, then be able to query... for example J1212 would return the same data.

Cheers :)

View 1 Replies View Related

General :: Create List Of Month / Year Between Data Range

Aug 19, 2013

I have a list of customers with multiple rent start and end dates. I need to create a query where each customer will have a record for every month/year between their respective rent start and end dates. I generally use macros but can't figure out a way to do it without VBA. My field names are Rent StDt and Rent EndDt. My query name is QCalcREndDt. I use version 2010.

View 6 Replies View Related

Date Range By Month Number

Mar 7, 2006

I am trying to make a query that finds all records by the "Month Number" and have problems.

What I did:
Month(DateField)
I got month numbers like I wanted but when I typed in the criteria below, I got February thru December Records, but when I search on anything "up to" 9 it works fine.

I want:
Between{month1] and [month2]
Month1= 9 (example)
Month2= 12 (example)
Need results to be from the requested months but any year

I have to imagine it's how the date is converted to an integer, but not sure.

Any help would be great!

View 4 Replies View Related

Queries :: Number Of Students In Age Range

Sep 17, 2013

I have this formula for counting age groups

TOTAL 29 - 31: Count(IIf(DateDiff("yyyy";[DATE_OF_BIRTH];Date())>=29<=31;1))

But id does not work, if I use between 29 and 31 also do not work

I need to get count (number) of all students that have 29,30 and 31 year of age.

View 5 Replies View Related







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