I have the following code that updates the end date to be the same as the start date after the user tabs out of the start date. Instead of making it the same date, I want it to be six days later. How do I add that?
For example, user enters 11/10/13 in the start date. I want the end date to automatically update to 11/16/2013.
Code:
Private Sub StartDate_AfterUpdate()
Dim datDate As Date
If Nz(Me!StartDate.Value, #1/1/1900#) = #1/1/1900# Then
'The user removed the START data as criterion, so remove the END date.
Me!EndDate.Value = Null
I'm not sure if it's a simple task, keep in mind I'm new to using access. We have a need at work for a small db just to keep track of some things and access seems appropriate.
I was attempting to set up the primary key in a date / increment format.
Basically the format would be year, month, day, increment (starting at 001)
060125-001 060125-002 060125-003
etc
next day I would like the key to change according to date and reset the increment to start again at 001
060126-001 060126-002
etc
I do realize they are different data types, I'm just not sure how to attack setting it up. Thank you in advance for any help / suggestions. :)
Very simple question: I just moved a simple daily rainfall record spreadsheet to my Access gardening database. Fields are date, rainfall in mm, temperature etc. and a single record is added for each day. How do I get the date field to increment automatically to 'previous day's date +1'. (No, I cannot use the shortcut key [Cntrl-;] to insert today's date in the table because records are only entered periodically, several days at a time, from a notebook.) Advice appreciated.
I'm building a database to organize the editing of a massive report my office is working on. I've got a big table of all the sections. Each section has a unique ID and a version number. I wrote the queries so that they return only the most recent version (i.e. the Max version # of each section.) The results of the query appear in a subform.
I was able to use VBA to allow the user to double click on the record in the subform, and look at the record in another form. (I was really proud of this.)
Anyway, it turns out what I need is to double click on the record in the subform, and create a new record based on this record, but increment the version # by one.
I've created an unbound form for this, and googled around. I need to use VB to store the variable, and then put it in the unbound text box.
I have inherited an old database with a new field to add some new invoice numbers and increment the number to each recordset by + 1. Starting at 001. I have formatted the field to a Number Field and formatted it to 000"/2015". I now need to populate over 2000 records with the sequential numbers for each record.
I have created the following code as my attempt. Of course it doesn't work!
Code: Sub AddNumbertoRecords() Dim db As DAO.Database Dim rst As DAO.Recordset Dim counter As Long counter = 1 Set db = CurrentDb()
I have a form that is being used for sample submission. Ideally, the user will be able to put in some information (3 or 4 fields) and the number of samples that they're taking, click the arrow and it will insert that number of samples into a temporary table that is then displayed on a continuous form. From there, they can edit samples, add more samples, and generate a report to submit to the genotyping group. Once their sample list is final, I will append it to the master list and clear the temporary table.
I've got the basics working. A looped "INSERT INTO" using a counter inserts the relevant information. I cannot, however, seem to get the numbering field to work.
Ultimately, the numbers need to look something like MySample-001, where MySample would be one of the other fields. Incrementing the 001 is the problem. In another situation in the database, I can successfully use DMax+1 to assign a single number on a single form, but I can't get it to increment the sample numbers.
Ideally, I'd like it to happen when the form is generated. However, I'm also open to having them assigned when the sample list is finalized, before inserting into master table.
User enters data relevant to placement on data card, in series of 20. Sometimes record lines are skipped on card, meaning user needs to be able to override default value, and then have code +1 for next record. For the output I need, Autonumber won't work, Recordsetclone won't work.
example columns: weight/length/record
VBA code is set:
Private Sub record_BeforeUpdate(Cancel As Integer) Me.record.DefaultValue = Me.record + 1 End Sub
User keying in a number activates the code, but won't recognize the default number.
weight length record 34 34 1 <----user enters 34 34 2 <--- auto populates from code 34 34 2 <---- auto populates from code, doesn't recognize previous record default set from code to output 3.
I'm making up a stock system for the independent jewellers I work for. Each stock pattern needs an individual code which can quickly be recognised read as tickets are often taken off the jewellery and can be mixed up easily. To this end we categorise based on two criteria:
1. Material 2. Type
At the moment these are as follows:
MATERIAL
IDMaterial 0Base and Miscellaneous 1Silver 2Gold 3Palladium 4Platinum 5Pewter
TYPE
IDStock Type 0Previously Owned 1Ring 2Pendant 3Chain 4Necklace 5Bracelet or Bangle 6Earrings 7Brooch 8Gents 9Gift, Clock or Miscellaneous
So if I had three pairs of silver earrings I want the first to be No. 161, the next 162, the next 163. If I had three Gold Bracelets I'd want them No. 251, 252, 253 and so on...Material and type are both selected form separate Comboboxes on a form.
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?
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
I have a form with Date of Death (DOD) field. I would like update DOD from a table dbo_patient into Z_Patients table.
I have set the datatype as Date/Time in the form for Date of Death.
Code: Private Sub Update_DOD() Dim rcMain As New ADODB.Recordset, rcLocalDOD As New ADODB.Recordset Dim DOD As String rcMain.Open "select distinct PatientKey from Z_Patients", CurrentProject.Connection
[Code] ....
However I am getting some error Run-time error '-2147217913 Date type mismatch in criteria expression in section below.
Code: CurrentProject.Connection.Execute "update Z_MAIN_Processed_Patients set DateOfDeath = '" & rcLocalDOD!date_of_death & "' where PatientKey = " & !PatientKey
I have some code that filters job raised I have 2 text box's txtdatestart and txtenddate after entering. date range between the too text boxs it shows me all job raised with in the period.i have entered what I would like is filter it again by client field using combo box cboclient so if the user enter's client name in cboclient combo box and date range in txtdatestart and txtenddate it will only show jobs raised with in the date range of the client enter in the combo box but if the combo box is empty show.
Code:
Private Sub cmdPreview_Click() 'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working. Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long Const strcJetDate = "#mm/dd/yyyy#" 'Do NOT change it to match your local settings.
I want to find out the last 6 months date from todays date. So as todays date is 27th january 2015 so the code should give me the date which is 6 months back from todays date so it will be something like 27th July 2014.
I have attached a sample database. Basically I want to have some lines of code that generate the result table, which is tbl_readmit_result.
As you can see, the difference is the addition of a new column called re_admit_status.
Rule is:
Status = "y" when the admission date, compared to the previous discharge is less than 7 days, otherwise "n", for the same pt_id. You cannot compare the two dates on different pt_ids.
Is there a way that this can be done automatically without having to go through the record manually?
How to assign value to a field based on the value of the same field in the previous record? Autonumber has its limitations for my purpose. Because, after I start filling up a form, I some times cancel the form filling process, and it affects the continuous numbering.
I guess the issue can be resolved by using some thing like record count facility. i.e. current' record's field value = number of records till previous record + 1.
How to exactly codify or execute the above by vba coding.
The Field Name is "SerialNo.", and Table Name is "StudentData"
I have a database that has a few tables and in one of them (Originators), there is all the info about my loan originators including there comission level. I would like it when they turn in a certain amount of loans there commision level will increase. Is this posible? Here is my DB setup.
Tables:
Originators- 1 to many- OriginatorID,FirstName,LastName,OrigSSN, Address,City,State,PostalCode,HomePhone,WorkPhone, CellPhone,FaxNumber,EmailName,BirthDate,Level(This is the commision level, and its in %).
Customers- 1 to many-CustomerID,FirstName,LastName,SSN,HomePhone,MobiPh one,FaxNumber,Address,City,State,Zip,Email,BirthDa te,CreditScore,SpouseFirst,SpouseLast,SpouceSSN,Sp ouceBirthDate,SpouceCreditScore,OriginatorID.
Loans- many to 1- LoanID,LoanType,StartDate,EndDate,LoanLender,Custo merID.
Lenders- 1 to 1- ID,LoanLender.
When a customer is added to the DB it is done with a form that has OriginatorID(which is autocreated), CustomerID(also auto) and some other of the customer stuff like phone numbers. When this happens I would like to have the originators % level increase, say after every 4 customers entered. Is it possible?
I am importing an Excel file with existing data using the Import wizard to build my table. It has an ID field (CID) which I would like to keep and set to auto increment starting with the next number. Can this be done ?
Need a little help with rounding up. In A2K I have a form with a textbox that displays a security deposit. Security deposit is calculated by rounding the payment to the next $25 increment, hence a payment of 324.53 should have a security deposity of $325.00, but a payment of $325.01 should have a security deposit of $350.00. The code I am using for a datasource for txtSecurity deposit usually works correctly, but for this payment ($324.53) it rounds to $350.00. Here is the code..
=(([txtPayment]25)*25+[txtSecDepRndTo])
txtSecDepRndTo holds the $25 incremental value.
Incidentally, if the payment is $324.49 my code rounds the Security Deposit to $325.00 as it should.
i have 2 field "CLIENT ID" and "CLIENT CODE". when i post a new record, i want the "CLIENT ID" and "CLIENT CODE" to merge to give me 1 field "CLIENT". the CLIENT CODE should be prompted for eg. THO or BLT and added to the CLIENT ID which will compose of 001 or 002. The CLIENT ID should also increment when each new CLIENT record is posted.
I have a numeric field called FileNo and an autonumber field called FileID in a table called tblFile. I also have a lookup numeric field called FileTypeID (with values 1 or 2 or 3) on another table called tblFileType.
I need the value of FileNo which I am showing on a Form frmFile to change dependant on the value of the FileTypeID i.e. If FileTypeID = 1 FlieNo should start at 100 If FileTypeID = 2 FileNo should start at 200 If FileTypeID = 3 FileNo should start at 300 Then when I create a new record I need to increment by 1 the value of FileNo according to the FileTypeID
I've setup a button and attached this code to its onclick event but it only works as long as I don't change the FileTypeID
Dim B As Integer Dim H As Integer Dim N As Integer
Do While FileID > 0 DoCmd.GoToRecord,,acPrevious If FileTypeID = 1 Then B = FileNo Else If FileTypeID = 2 Then H = FileNo Else If FileTypeID = 3 Then N = FileNo End If End If End If Loop
DoCmd.GoToRecord,,acLast If FileType = 1 Then FileNumber = B + 1 Else If FileType = 2 Then FileNumber = H + 1 Else If FileType = 3 Then FileNumber = N + 1 End If End If End If
Can someone tell me how I can change the increment in an existing database.
The PK is currently Auto ID with each new record currently 1001, 1002 and so on.
What I am trying to do is say starting monday the next new entry begins at 5003 versus 1003, in essence I am trying to change my first number to a 5 versus a 1.
in an initials field... i want it so that it could automatically increment the 3 letters to uppercase... how? if it can't be done in a table where can it be made (i.e. in a form, query etc)
