Access :: Trying To Create A Multi-criteria Incrementing Number

Modules & VBA :: Multi Criteria DLookup - Return ID Number Of Entry

Dec 4, 2013

I am trying to make a DLookup function to return the ID number of an entry that matches 2 or 3 criteria but I am struggling to get the syntax correct for the second and third criteria.

Here is what I have so far:

1 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] =" & Forms![Mainform]![p11] )

2 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " And [timedata]![BGSnum] = 1001" )

BGSnum is a numerical value but it changes for each form I load, so what I want to do is use the form location value as the criteria.

E.g.

=DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " AND [BGSnum] = ' " & Forms![Mainform]![BGS] & "' ')

and possibly a third criteria too. This is where I am having problems and the syntax is wrong so MS Access wont let me save the macro.

View 6 Replies View Related

Incrementing Number

Jan 16, 2006

Hi all

I am adding two records to an empty table using an append query (or make table query). I would like the first record to have 1 in the ID field and the second to have 2. I will then delete 2, append 1 elsewhere and empty the table and start over again.

Any suggestions? It needs to be 1 and 2 each time not just an autonumber.

Thanks

Greg

View 1 Replies View Related

Incrementing Number Fields

May 5, 2005

Hey, long time reader, first time poster.

I have a database created that is used to catalog different projects. When the user adds a new record and that project is already in the database it gives the user a message stating that that project already exists. It gives the option of cancelling and goes back so the user can name it something different. Or the user can continue and it creates a duplicate copy.

Instead of creating a duplicate copy, I want it to increment a number field named [Duplicates] by 1.

Example:
If project 'A' is already in the database and the user adds it again it increments the duplicate field by one. So now the properties of project 'A' shows that there is 1 duplicate. Does that make sense?

Here's my duplicate code so far:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Response As Integer

If Not IsNull((DLookup("Project_Name", "Development_Projects", "[Project Name]= '" & Me.Project_Name & "'"))) Then
Response = MsgBox("That Project already exists" & vbCr & vbCr & "Select Yes to duplicate record, No to cancel.", vbCritical + _
vbYesNo + vbDefaultButton2, "Duplicate Data")

If Response = vbYes Then
[Duplicate] = [Duplicate + 1]
Exit Sub
Else
Cancel = True
End If
End If

End Sub

View 2 Replies View Related

Incrementing Number For Old Records

Nov 8, 2006

I have 4 tables on my dbase. On the last table underlies all records pertaining to customer payments.

tblCollectionData

ColID - PK
PolID - FK
Amount
ORDate
ORNumber

I want to add another field which i would name -- CollectionNumber. In this field, i need an incrementing number for each PolID.

For each PolID (no duplicates), there could be 1 to 6 records of payment. I need the number to start at 1 for each unique PolID.

Please help me on this. I've been looking at different posts but i still can't find answer. :-(

Thanks!
Sheila

View 8 Replies View Related

Incrementing A Number Without Autonumber

Mar 26, 2006

I want to generate a unique Patient Number by using the first three letters of a particular field and then increment it by 1.

ie If the [CountryName] field is Australia I want the Patient ID to be AUS1 then AUS2 and so on.

I can do the first bit
PatientID=UCase(Left[CountryName],3) but how can I then add the number...?

(or maybe this isn't possible).

I need to avoid autonumbers becasue of the PDA program I am then transferring the database to.

View 5 Replies View Related

Modules & VBA :: Create Duplicate Records With Incrementing Dates

Jun 27, 2014

I have almost no experience of VBA outside of working a little with codes generated for me like docmd etc.

I am trying to create a database for resourcing staff.
I have a form where the user inputs a start date and an end date which updates a table.
I have a query that works out the number of working days between those dates.
I have a table with all the working dates between now and 2016
I want to put a duplicate record button on the form (I can do that bit)

The tricky part is I want it to create a new record for each date in the dates table between the start and end dates.
Example 23/06/2014-18/07/2014 = 20 working days. I want to end up with 20 records with start dates as below and the same end date.

23/06/2014, 24/06/2014,25/06/2014,26/06/2014,27/06/2014,30/06/2014,01/07/2014,02/07/2014,03/07/2014,04/06/2014,07/07/2014 etc up to 18/07

View 9 Replies View Related

Multi-table, Multi-criteria: Avoid Repeating Records

Apr 10, 2008

Hi everyone. Apologies if this has come up before, but the search terms I've tried here and on google keep turning up the wrong information.

At work I manage a large database with many tables. It stores data for participants in a research study. Each table stores the data for a different test, so one participant may have multiple records. Primary keys for these tables are defined by a combination of the participant and date of test fields. (Everything is dependent upon a table that stores the static info for participants, so the database is normalized.)

I want to be able to make a table that lists target participants and dates, and then create a query that looks at this table and pulls all the available data from various tables for those individuals that was recorded within one year of the target dates.

I've successfully made queries that meet these criteria while pulling data from only one table. The problem I'm having is that when I try to pull from multiple tables, each with it's own date field that needs to be used as a criterion, I end up excluding almost all the data, because most of the target participants do not have all the requested data within the target dates.

I've tried being inclusive with my criteria (using ORs), but then I end up with tons of data that I don't want and I need to filter through it, which defeats the purpose of the query.

Any advice on handling this issue, or do I basically just need to create a separate query for each table?

I'm sorry if this is too vague, but it's illegal for me to upload any of my own dataset. I could probably come up with an example if it's helpful, though.

Thanks!

View 7 Replies View Related

Append,Incrementing Number Problem

Oct 23, 2006

Hi All

Hope you can help, I have been looking on the forum but with no luck. I am trying to design an append query that adds any number of records to another table but need one of the fields to increment by one for each record, so for example;

I append 10 records and the other field increments from 060,061,062 etc for each record

Thanks

HITC

View 8 Replies View Related

Incrementing Number Based On 2 Other Fields

Dec 9, 2005

I want my db to generate our reference # automatically based on input from other fields. The number is the date record entered, the users initials, and an incremental number. Ex. 120505GMM02

My form is based on a query and I have the query generating the first two components from the date entered in reqdate and the initials entered. How can I get an incremental number generated to go with the rest.

The number should start with 01 for each day and also for each user. Therefore when GMM is entering his third record on the 5th it should generate 120505GMM03 even if there were 10 other records entered on the fifth by other users. Each users numbers would start at 01 for each day.

I asked this earlier but thought I would restate my question in hopes of getting my thoughts around this. I appreciate your patience with me.

Thanks.

Gary

View 3 Replies View Related

Auto-incrementing Number That Starts Over

Nov 30, 2011

I have a table, Purchases, which has a 1:N relationship with another table, Items. Each purchase can have multiple Items.

'PurchaseID' is the primary key of my Purchases table.

The Items table has a composite primary key, made up of PurchaseID (making it an identifying relationship) and ItemID.

As you can see, I have ItemID starting reverting back to 1 each time PurchaseID increments. PurchaseID is an autonumber on the Purchases table, however ItemID is just a Number and has to be entered manually. If I set ItemID to an autonumber, it will continue to increment even after PurchaseID changes.

What I am trying to do is to get ItemID to auto-increment but revert back to 1 each time PurchaseID increments.

The entry form for both looks like this:

The Record Source of the main form is the Purchases table, and the subform is a Multiple Items Form based on the Items table. Filling out the form will create a new Purchase record with an auto-incremented PurchaseID, and a number of new Item records with that same PurchaseID, and whatever ItemID's are specified in the subform. Currently the ItemID's have to be entered manually as 1, 2, 3, etc.

Is there a way to get them to auto-increment, but begin from 1 for each new Purchase record?

View 1 Replies View Related

Custom ID With Year And Incrementing Log Number

Dec 16, 2014

Been looking around for a solution for a custom ID and can't quite find one that's specific to my problem.

I am creating a log and I want to give each new log entry a new log number. The format would be: IEyy-xxx. The ID is the letters IE, then 2digit year, a dash, then 3digit incrementing number. The ID should reset back to 001 every year (e.g. if IE14-623 is last log entry of 2014, first log entry of 2015 would be IE15-001).

My table has an autonumber that I will use as the primary key- which is not the log number- and looks like this:

These are the fields in my table:

Index Log Number (autonumber and primary key)
IE number
Program Name
Date Created

View 1 Replies View Related

How To Create Incrementing Text Field Tied To Multiple Tables

Aug 28, 2015

This is a errand database where each client can have several errands and some errands are related to each one and other.

Since each client can have several errands and and each errand is related to each one and other, then the client. I thought i need key which can work as a ClientErrandID, so i can tie the errands together. My idee is to use the ClientErrandID as an text auto incrementing field with a string like: (Errand-A-xxxx).

Lest say the range would be from (Errand-A-0000 to Errand-A-9999) And when the string hits "9999" at the end the "A" will switch to a "B" and the number counter would start over from "0000"

How should the table relationship look like?When a user is adding a new errand to a client, how should the function work?

Should all the associated errands be created in the background and granted the same Errand_ID when the main errand is created?Can i create a incrementing text field with vba & queries?

How would the code structure look like?Is there any examples on a incrementing text field with code available?

View 4 Replies View Related

Assign Incrementing Number Based On Common Field

Apr 16, 2013

My access database is used to process vehicle trips. Each shift, a vehicle is assigned a docket number (paper based), and on this docket the driver fills out each trip he/she makes. In a shift (thus on a docket) there may be 5 - 15 trips. I want to create an incrementing number for each trip in a new field - can be created as they are processed or after the fact by looping through the resultant data - whatever is easier.

So in simple terms, return all trips for each docket number then number each trip.

View 5 Replies View Related

Modules & VBA :: Copy Record N Number Of Times - Incrementing Date

Jun 3, 2015

Is it possible to copy a record 'n' number of times, incrementing the date by either days, months, weeks etc?

I have a regular payments table that will need to be edited at some point should either dates, or amounts change, but... regardless of the size of the table, it's not a problem, as these will be moved to a different table and deleted once paid.

Given the example, would it be possible to copy this record and additional 5 times, incrementing the date by one week.

View 11 Replies View Related

How To Create Multi-user Database

Apr 12, 2013

I've read instructions for making a multi-user Access 2007 database. Summary is:

1) Split the database
2) Put back-end part of the split onto shared drive
3) Make copy of front-end of the split and give to each user of the database
4) Open Tools, Options, Advanced Tab. Set the default open mode to shared and the default record locking to edited record. While in options area, go to general tab and turn off compact on close and name autocorrect.

These instructions were posted by an Ms-Access MVP.My questions is about step 4. How do these settings get saved to the particular database that I am trying to make multi-user? None of these settings are in the Current Database Tab for Ms-Access 2007. Since they are in the general tab, does that mean all database I open with my copy of Ms-Access will take on these multi-user settings?

View 1 Replies View Related

Create A Multi Word Search Query

Dec 24, 2005

You guys are awsome!

Is there anyway to use Like in queries where the user could type in more than one word?

I have a text box (search title), on a form (Search).

On after update the text box, opens a query.

The criteria in the query for the Title field is:

Like "*" & [Forms]![Search]![search title] & "*"

this works great for one word or phrase.

I would like the user to be able to type in 2 words, and have the query find all the records where the first word is in the Title, and where the second word is in the Title. And so on with 3 words or more.

View 1 Replies View Related

Forms :: How To Create Multi Value Field Using Combo Box

Oct 7, 2013

How do you create a Multi-Value Field using a Combo Box on a form?

View 1 Replies View Related

Forms :: Job Plan - Create A Multi-value Field

Aug 16, 2014

I have a Job Plan form containing a selector that chooses a system number from another table. I want to (inside the same form) look in a third table that ties system numbers to equipment items (one system has multiple items of equipment) and generate a list box displaying those equipment items. I think I know how to do that bit.

I then want the user to be able to multi-select from the list and somehow to save those selections against the Job Plan number.Is the best way to do this to create a multi-value field in the Job Plan table and store them in there?

From what I read, the best way is to create a join table between Job Plans and Equipment Items, but I'm not quite sure how to connect the selected items in a list box to that - is there some sort of obvious way to do that?

View 2 Replies View Related

Multi-Criteria Filter

Jan 3, 2008

Good morning everyone,

I use this code to filter a subform with multiple criteria.

Private Sub Filter_Click()
Dim strWhere As String
If Not IsNull(Me.Coordinator) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[EmployeeID] = " & Me.Coordinator & ""
End If
If Not IsNull(Me.Customer) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[CustomerID] = " & Me.Customer & ""
End If

If Not IsNull(Me.Supplier) Then
'Create Predicate
strWhere = strWhere & " AND " & "Orders.[SupplierID] = " & Me.Supplier & ""
End If
Me.Track_All_Orders.Form.Filter = strWhere
Me.Track_All_Orders.Form.FilterOn = True
End Sub

This Code works great, however i want to use it to filter my report as well, so I put strWhere as a global var and i wrote this code to generate the report with the same filters criteria as the subform:

Private Sub cmdGenerateReport_Click()
Dim stDocName As String
stDocName = "Statement"
DoCmd.OpenReport stDocName, acPreview, , strWhere
End Sub

Nothing happen when i click on cmdGenerateReport!! any clue?

Million thanks in Advance,
Best Regards,

View 2 Replies View Related

Multi-criteria Queries?

Apr 8, 2008

Ive had a quick look at other threads to see if this question has been asked already, so hopefully Im not repeating a previous question!

I am completely new to Access but have been asked to perform multi-criteria queries across multiple tables. I realise it would be easier to just put all the information into one table, but if anyone could explain how to do the above I would be very grateful.

Thanks

View 6 Replies View Related

Multi Criteria Search

Oct 25, 2005

Hello,

I have been trying to produce a front end for a multi criteria search. I have used one of the sample databases from the site and amended the code as necessary, but obviously not correctly. I can't get it to show me the records based on my search criteria.

I would be grateful if somebody could have a look and let me know what I've done wrong (cut down DB attached). If I can crack this I want to do another multicriteria search for other parameters.

One other question - is it possible to take those filtered records and dump them into a report? For example, say I select one parameter and want tpo print all records associated with that parameter?

Thanks

View 4 Replies View Related

How To Return A Value On Multi Criteria

Nov 20, 2012

On a report I want to Dlook up using if a name and a date match on a query line it will return the total that is associated in that same line. What do i need to add to the below is if

=DLookUp("[Total]","qryaQFFSumOfTotal10day","[AssociateName]=Reports!rprtQFF10day![AssociateName]") & qryaQFFSumOfTotal10day","[ErrorDate]=Reports!rprtQFF10day![Date1]") if null = 0?

View 5 Replies View Related

Multi Criteria Form Search?

Aug 17, 2007

Hi all:)

Has anyone ever come across an example of a form where you can carry out a multi criteria search which not only displays the results on a subform but when you select an item from that subform the details can be displayed in text boxes etc on the main form.
I have tediously searched this forum and the web but all search examples only display on a subform only, is it even possible if so has anyone found any examples or how would I go about achieving this

Thanks Jackie

View 3 Replies View Related

Queries :: Multi Criteria Search

Aug 12, 2015

I have an events database with a description field which I would like to be able to search for multi criteria which would be determined by the user. For example, the user might want to search for all events which are Merchant Navy, Dieppe and Vimy related or they may want to search for only Veterans Week related events.

I've tried creating a form with text boxes that the user would put the criteria in and then basing the query on those text boxes using a like statement.My problem is that I don't know how many criteria the user will be using so I can have too many or too few text boxes. If too few, the user is not getting all of the records that they are looking for. If too many, the user ends up getting all the records in the database as the system uses the bank boxes to bring back all values, so basically all records.

is there anyway that I can have only one text box where users would enter in as many or as few crietia words seperated either by a , ; space etc., and the system would return all records which contain any of those words.

View 1 Replies View Related

Forms :: Create A Multi-field Search Box That Goes To Record In A Form

Jul 7, 2014

This is what the search box must do:

- Searches 3 fields (StudentID, FirstName, LastName) and jumps to the relevant record.

The closer to the search box in the navigation bar below, the better, so this is what I would really like too.

- A repeat click would take me to the next result/record (if any) with the same criteria
- Instant search as soon as I start typing

View 11 Replies View Related