Calculate A Persons Age Code Not Working.
Oct 20, 2006
Hi all I have another code Problem, Can someone help me with this.
I have two fields
One called cust_birthday ( date field )
And one called cust_age ( text field )
The code below I put in a module name age function.
The Problem, is I get no return from this code.
What is wrong with this code ????
Option Compare Database
Public Function Age(cust_birthday As Date, Optional cust_age As Variant) As Integer
Dim dteBase As Date, intCurrent As Date, intEstAge As Integer
If IsMissing(SpecDate) Then
dteBase = Date
Else
dteBase = SpecDate
End If
cust_age = DateDiff("yyyy", dteDOB, dteBase)
cust_age = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
Age = cust_birthday + (dteBase < cust_age)
End Function
John527
View Replies
ADVERTISEMENT
Apr 25, 2012
I am using 2010 and have a bound field called Policyholder ID number in a table called Policy Information. I would like to calculate how old a person is by using the identity number which is 13 digits (South African ID) and the format is as follows - 7009215069084. Using the first 6 digits this person was born on 21 Sept 1970 and is 42 years old ( Current year of 2012 minus year of birth of 1970 = 42)
How I can include a calculated field so once I type in the identity number the age of the person reflects in a field called Current Age.
View 13 Replies
View Related
May 11, 2007
I hope you can help. I have a database and I am trying to work out the working days between the start date and the end date of a programme. This will not take into account any weekends or bank holidays. I have created a separate table called tblHolidays which has a list of the Bank Holidays. The majority of it works but out of 1700 records 50 don’t. These are ones in which it starts on 1 month finished on another or has a bank holiday. It works with certain bank holidays but not all of them. Bank holidays go back to the beginning of 2006
The following is the code I have used:
Public Function WorkingDays2(Start_Date As Date, End_Date As Date) As Integer
On Error GoTo Err_WorkingDays2
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
Start_Date = Start_Date + 1
intCount = 0
Do While Start_Date <= End_Date
rst.FindFirst "[HolidayDate] = #" & Start_Date & "#"
If Weekday(Start_Date) <> vbSunday And Weekday(Start_Date) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If
Start_Date = Start_Date + 1
Loop
WorkingDays2 = intCount
Exit_WorkingDays2:
Exit Function
Err_WorkingDays2:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select
End Function
I then used the SQL
Select WorkingDays2 ([start_Date], [End_Date]) As CountDays
This is driving me crazy!!:mad:
View 12 Replies
View Related
Oct 11, 2006
Hi all ,
I am trying to calculate a person’s age.
Went to Customer Form
Have a field called cust_birthday ( date/time ) ( 99;00;00;>LL;0; )
Have a field called cust_age ( Number )
Went to Madules / New / then add this code,
Public Function Age(dteDOB As Date, Optional SpecDate As Variant) As Integer
Dim dteBase As Date, cust_age As Date, cust_birthday As Integer
If IsMissing(SpecDate) Then
dteBase = Date
Else
dteBase = SpecDate
End If
cust_birthday = DateDiff("yyyy", dteDOB, dteBase)
cust_age = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB))
Age = cust_birthday + (dteBase < cust_age)
End Function
Not Working get no return. Can someone help me. Thank You if you can….
John
View 4 Replies
View Related
Oct 6, 2013
The below function returns correct time difference between workdays. However, it is excluding Saturday as per the code.
It is calculating 06:30 am to 22:00 pm time for weekdays but I also want it to calculate the time from 10:00 to 13:30 on a Saturday.
I am trying to use the NetworkMinutes function to achieve this. However, there is a problem getting the time for Saturday.
Code:
Option Compare Database
Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : NetWorkMinutes
' Author : Rod
' Date : 13/12/2012
' Purpose : Returns the number of work minutes between two date-time arguments.
[code]...
View 14 Replies
View Related
Oct 26, 2006
I have searched the forum for this answer but no luck. :(
I'm trying to calculate the amount of WORKING time between two dates in an Access database. At the moment i am just subtracting one date from another but this gives me all of the time in between including weekends and evenings.
I need this time to be calculated in hours.
In Excel i know there is a NETWORKINGDAYS function which does something similar but with days rather than hours.
At least if i could get the working days i could then convert it into hours.
Please help!
View 7 Replies
View Related
Jul 8, 2015
I need to calculate the passed working days and show them in a form. I should be also able to use the number in a query later on. in excel I use the formula to get the days passed:
Code:
=IF(ISERROR(MATCH(F10,Dictionary!C:C,0)),NETWORKDAYS(D10, TODAY(), Dictionary!$E$2:$E$43),"Status Excluded")
D = "Date_uploaded" in access table "tbl_All_Cases"
F = "Status_Case" in access table "tbl_All_Cases"
c:c = dictionary case status
E:E = dictionary holidays
"Status Excluded" will show up in the cell if a case has one of the status from the dictionary..I created a table: tbl_Dictionary where there are 2 fields: "Case_Status" and "Holidays".How can I translate the above formula into something that access will understand?
View 14 Replies
View Related
Jun 6, 2007
Hello, all! Can you please look at the following code and tell me why it throws a compile error of user type not defined. I'm trying to use this code to automatically pull in the previous room # and associated data into the next record, so that I don't have to enter the data every time, as sometimes there are fifty entries with the same room # and associated data. Can you tell me what it's wrong with it? Thank you!!
VB Code: Original - VB Code Private Sub Form_Current()Dim s As StringDim db As New ADODB.ConnectionDim rs As New ADODB.RecordsetSet db = CurrentProject.Connections = "SELECT Last(Room #) AS Room # FROM [tblFF&EBudget] WHERE Budget Line=(select max(Budget line) from [tblFF&EBudget])"Set rs = New ADODB.Recordsetrs.Open s, db, adOpenDynamic, adLockOptimistic'add to the tableWith rsMe![Room #] = .Fields("Room #")End WithSet rs = NothingSet db = NothingEnd Sub Private Sub Form_Current() Dim s As StringDim db As New ADODB.ConnectionDim rs As New ADODB.RecordsetSet db = CurrentProject.Connection s = "SELECT Last(Room #) AS Room # FROM [tblFF&EBudget] WHERE Budget Line=(select max(Budget line) from [tblFF&EBudget])" Set rs = New ADODB.Recordsetrs.Open s, db, adOpenDynamic, adLockOptimistic'add to the table With rsMe![Room #] = .Fields("Room #")End WithSet rs = NothingSet db = Nothing End Sub
Thanks!
KellyJo
View 1 Replies
View Related
Aug 1, 2005
Maybe this should be posted under VBA.... not sure. Havent seen answer that works yet.
Its a form with an option group "PickWO" When you pick an option, different fieilds become visible-non visible. I created a field "PickWOvalue" to store the option value "1" or "2". That part works fine.(visible) The problem is when you reopen the form to view the records the option box value does not show or change the fields visible properties. And the on open cuauses an error. Anyone see the problem?
Private Sub Form_Open(Cancel As Integer)
Me.PickWOvalue.Value = Me.PickWO.Value
End Sub
`````````````````````````````````````
Private Sub PickWO_AfterUpdate()
Call NotVisible
Select Case Me.PickWO
Case Is = 1
Me.ReInspectionDate.Visible = True
Me.Price.Visible = False
Me.WOPreview.Visible = True
Me.PrtWO.Visible = True
Me.WBMInvoice_.Visible = False
Case Is = 2
Me.ReInspectionDate.Visible = False
Me.Price.Visible = True
Me.cmdPreTag.Visible = True
Me.cmdPrtTag.Visible = True
Me.WBMInvoice_.Visible = True
End Select
End Sub
View 6 Replies
View Related
Aug 18, 2004
I have an access database that links to a SQL server.
My problem is when I made a copy of the Access database,
made changes to one sub report and mapped to test tables and now I have VB code that
runs the overall report where the SQL gets jammed on the join, (which I noticed through debugging)
but when I take the same SQL and run it in the Query view - the SQL works.
has anyone seen anything like this before?
View 6 Replies
View Related
Apr 17, 2006
Hi All,
I have Header form and subform subform in it with following fields
Subform fields are :
1. MQE_No : (Text field as it will be like MG-120, MG230), Duplicate OK)
2. RPO_No : (Number field – Double, Duplicate OK ) The are project no.
3. ForemanNo (Number field – Long Int – Duplicate OK)
Header form fields are ;
1.ForemanNo – Long Int – No Duplicate
2.Other fields…
The subform field have ForemanNo One-To-many relationship with Header form.
Following project MG-411 need to enter (assign) to foreman no. 641
Actual data to store in subform
MG-411 (MQE_NO)
5421654 (RPO_NO)
641 (FOREMANNO)
I started to enter data into subform selecting the ForemanNo at header so records to enter in specific foremans account. Everything is fine here.
What I need is:
I would like to select / add another Foreman into the header and start entering same above data for another Foreman. Bcz sometimes we need to do like this in case previous Foreman may go on to a week vacation. We can not stop project for a week. So need to assign project to other foreman.
When selected the Project (MG-411) thru a combo to assign it with other foreman, it should give a short msg to user that this project has already been assigned to “XYZ” forman. Need to assign again “ (Y/N box )
If Y then data entered otherwise cancelled.
To achieve this, I did the following but only 50% success. (Used Cmbo CboRPO2 select MQE_NO). Tried to satisfy both condition but no use.
Private Sub CboRPO_AfterUpdate()
If DCount("*", "T_RPO_Footer", "RPO_No = " & RPO_No) > 0 And DCount("*", "T_RPO_Footer", "ENO = " & ENO) Then
MsgBox "RPO ALREADY ASSIGNED TO SOMEONE / FOREMAN", vbOKCancel, "WARNING!!!"
Me.Undo
Exit Sub
Else
Dim Msg, STYLE, TITLE, HELP, CTXT, Response, MYSTRING
If DCount("*", "T_RPO_Footer", "RPO_No = " & RPO_No) > 0 And DCount("*", "T_RPO_Footer", "ENO <> " & ENO) Then
Msg = "RPO ALREADY EXIST WITH OTHERFOREMAN, ASSIGN AGAIN TO ANOTHER ?"
STYLE = vbYesNo + vbInformation + vbDefaultButton2
TITLE = "!! ATTENTION !!"
HELP = "TEST FILE"
CTXT = 1000
Response = MsgBox(Msg, STYLE, TITLE, HELP, CTXT)
If Response = vbYes Then
Me.MQE_NO = Me.CboRPO.Column(0)
Me.RPO_No = Me.CboRPO.Column(1)
Me.WORKSHEET_NO = Me.CboRPO.Column(2)
Me.WORKORDER_NO = Me.CboRPO.Column(3)
Me.WORK_DESC = Me.CboRPO.Column(4)
Me.PL = Me.CboRPO.Column(5)
Me.PipeLineKM = Me.CboRPO.Column(6)
Me.DiaMeter = Me.CboRPO.Column(7)
Me.PipeLength = Me.CboRPO.Column(8)
Me.PipeLineArea = Me.CboRPO.Column(9)
Me.P = Me.CboRPO.Column(10)
Me.RPO_AMOUNT = Me.CboRPO.Column(12)
Me.INV_AMOUNT = Me.CboRPO.Column(13)
Me.Status = "WIP"
Me.StatusID = 2
Me.CboStatus.SetFocus
Else
Me.Undo
Exit Sub
End If
Else
Me.MQE_NO = Me.CboRPO.Column(0)
Me.RPO_No = Me.CboRPO.Column(1)
Me.WORKSHEET_NO = Me.CboRPO.Column(2)
Me.WORKORDER_NO = Me.CboRPO.Column(3)
Me.WORK_DESC = Me.CboRPO.Column(4)
Me.PL = Me.CboRPO.Column(5)
Me.PipeLineKM = Me.CboRPO.Column(6)
Me.DiaMeter = Me.CboRPO.Column(7)
Me.PipeLength = Me.CboRPO.Column(8)
Me.PipeLineArea = Me.CboRPO.Column(9)
Me.P = Me.CboRPO.Column(10)
Me.RPO_AMOUNT = Me.CboRPO.Column(12)
Me.INV_AMOUNT = Me.CboRPO.Column(13)
Me.Status = "WIP"
Me.StatusID = 2
Me.CboStatus.SetFocus
End If
End If
End Sub
I tried with following code also
'Dim cdn As String
'cdn = "[MQE_NO] = '" & Nz(CboRPO, "") & "'"
'cdn = cdn & " And ENO = " & ENO
'If DCount("*", "T_RPO_Footer", cdn) > 0 Then
'MsgBox "THIS RPO ALREADY ASSIGNED TO THIS FOREMAN", vbOKOnly, "WARNING!!!"
'Me.Undo
'Exit Sub
'Else
‘do something
‘End if
Can somebody help it please.
With kind regards,
Ashfaque
View 1 Replies
View Related
Nov 17, 2006
just wondering why randomly certain modules in the form code stop working..
when you accidentally press return.. or do something in the wrong order.. or apply one rule to one control.. and then another control rule stops working..
so you have to delete the code.. right click on the control's event.. and re-insert the code into the event section.. and then it works..
why?
View 4 Replies
View Related
Dec 8, 2006
Can anyone see why the sql code below is not working, it works up unitl
StrSQ4 = DLookup("[D Status]", "tbl_Delupdate", "[Cylinder Barcode Label]='" & Stringy1 & "'")
But when i try to get the latest date for that Cylinder Barcode as shown below it does not bring out a value
StrSQ4 = DLookup("[D Status]", "tbl_Delupdate", "[Cylinder Barcode Label]='" & Stringy1 & "'AND [Date of D Status]=#" _
& DMax("[Date of D Status]", "tbl_Delupdate", "[Cylinder Barcode Label] = '" & Stringy1 & "'") & "#")
View 6 Replies
View Related
Sep 24, 2014
I am using the function below to calculate a field that excludes weekends and holidays. The weekends are excluded as it is now, but when I try to add in code to exclude holidays I am getting errors. The code for the holidays is in bold and a couple of the errors are Loop without Do so I remove the Loop then I get a Else without If.
Code:
Option Compare Database
Public Function WorkingDays(Due_Date As Date, Result_Date As Date) As Integer
'-- Return the number of WorkingDays between Due_Date and Result_Date
On Error GoTo err_workingDays
[Code] ....
View 6 Replies
View Related
Jun 4, 2014
I have an issue with a code that I have in a form which adds rows in a table as many times as categories chosen from a list. However, the code is not working correctly: it adds the information and creates a row with a category in blank, in addition to the rest of the rows with one of the chosen categories. I would like for this not to happen, to add only as many rows as the categories chosen.
My code is the following:
Private Sub cmdUpdate_Click()
Dim valSelect As Variant, MyDB As DAO.Database, MyRS As DAO.Recordset
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Tasks", dbOpenDynaset)
MyRS.MoveFirst
[Code] .....
View 6 Replies
View Related
Jan 31, 2006
Please help me !
I am in the process of creating a database in access to search for a persons name. However, the results will give many ansers. For example if I search for the srname 'jones' I get many names. I eed then to be able to select one of these jones's with the information relavent information.
It has many years since I have done this although i did have quite a good knowledge at the time. Plesae can you make any suggestions and answers as basic as possible !
Many thanks in advance http://forums.aspfree.com/newthread.php?do=newthread&f=18#
Mad
View 7 Replies
View Related
Jun 14, 2013
As mentioned before on a different topic I am building a database to sort through publication authors. I currently have the table sorted in a way that only the main author is listed for the 3000 records. Now I need a way to remove duplicate entries on a new table. Each entry is a different publication. Each record has a DOI code that is unique and an author which might be a duplicate. I want a new table listed by author once and a new field that list all of the DOI numbers for that author.
I know how to do a query to show only the unique names, but how to I also get the database to combine the records that share the same author.?
View 7 Replies
View Related
Sep 18, 2014
I have a list of people in a list box. In the list box I only have the people names listed. (example below)
1. John
2. Frank
3. Tim
4. Jessica
I want to click on one of the names and have a form pop up on the same screen that lists the persons contact info. If I had a table like listed below, could the info auto populate on a separate form.
[ID] [Name] [Number] [Address]
1. John 456-4567 123 Elm Rd.
2.
I want the Form to pop up and show...
Name: John
Number: 456-4567
Address: 123 Elm Rd.
View 1 Replies
View Related
Mar 12, 2014
What I want to do instead is open an existing .XLSM wokrbook delete or update the 7 sheets it creates and replace them with the new query results from access.
I love this code below because it works really well but now I have a new requirement. I have a workbook that has a "dashboard" sheet that looks at the sheets from acccess and summerizes the data. So, I'd like Access to open that "template" excel workbook and delete the old sheets and put in the new ones..The required sheets to keep are called "Metrics", "Validation" and "Mara"
What I was trying to do for the past few hours was another work around which was to have Access run this code, then excel run some code to import the "dashboard" formulas but I can't get it to copy to another workbook because it links to the OLD workbook..Here is the working code that needs modding:
Code:
Option Compare Database
Public Function ExportAdvanced()
Dim strWorksheet As String
Dim strWorkSheetPath As String
Dim appExcel As Excel.Application
Dim sht As Excel.Worksheet
Dim wkb As Excel.Workbook
Dim Rng As Excel.Range
Dim strTable As String
Dim strRange As String
Dim strSaveName As String
Dim strPrompt As String
Dim strTitle As String
Dim strDefault As String
[code]...
View 3 Replies
View Related
May 1, 2014
I currently have a database set up with three basic forms:
Form 1 = Main menu with options to go to Form 2 and Form 3
Form 2 = Employee information form which includes email address
Form 3 = Employee document upload form
My goal is once the Employee is registered in Form 2 they can then upload a document in Form 3. When they upload this document and fill out other parameters Including their bosses name (which is captured as a record in Form 2) they click "Submit Form." I would like for this submit form button to populate an email that is updated to send to the selected "Bosses name"
Currently on the Submit form button I have:
Private Sub Submit_Record_Click()
DoCmd.SendObject _
, _
, _
, _
"email .com", _ <-- this is what I want to autopopulate with the correct persons email (as well as their name below)
, _
, _
"***A new Lab Report has been submitted for your review***", _
"Bosses name," & vbCrLf & vbCrLf & vbCrLf & "Please log into the Report Database and review the latest pending report. If you have any questions please contact the sender." & vbCrLf & vbCrLf & "This is an automated response generated from Microsoft Access." & vbCrLf & vbCrLf & vbCrLf & "Sincerely," & vbCrLf & "ESBU Lab Report Database", _
False
DoCmd.Close
End Sub
View 1 Replies
View Related
Mar 14, 2006
Dear All,
I'm new to VBA coding. A code below is copied from a friend of mine and I can't make it work. How to call up this function in my form. In my form I have 3 text boxes (StartDate, EndDate and NumOfWorkDays). My form is based on a table.
Please anyone who would help me on this, kindly give me the step by step procedure as I am really novice. Thanks in advance.
'*********** Code Start **************
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays
Dim intCount As Integer
StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above
intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate
Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount
Exit_WorkingDays:
Exit Function
Err_WorkingDays:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
End Function
'xxxxxxxxxxxxxxxxxxxxxxxx
Can someone please direct me to the right path.
Your help is highly appreciated.
qwerty70
View 1 Replies
View Related
May 16, 2006
I have witten a code in the close event of a "Primary" form that would update a Combo boxe on a "secondary" form only if the "secondary" form is open.
ie:
Private Sub Form_Close()
If Forms!frmEnquiry.Open Then
Forms!frmEnquiry!CboCustomer.Requery
Else
DoCmd.Close
End If
End Sub
This code keeps giving me an error, is anybody has got any idea why?
Thanks in advance.
View 7 Replies
View Related
Dec 27, 2014
I have a program that runs under access 2007 that I use at my work. We will soon be updating to MS office 2010 and the program will not work now because a calender file .ocx was removed from access 2010. Is there a way to get the 2007 .ocx file to work in access 2010?The program I am using is a relatively simple stand-alone and unsupported app that we use to request patient arrival and departure from various radiology tests inside a hospital. No reports are made from the app other than the number of patient transports for the day.
The app is placed on a common drive accessed from any pc in the hospital. No special permissions are required. But our app does use the calendar, time and date functions in access 2007. When I tried the app on a pc with access 2010, it basically says it (access) cannot open the app because a .ocx file is not present.Is there a way to make the access 2010 calendar file work in access 2007?
View 1 Replies
View Related
Jan 15, 2015
I have an Access 2010 database with two tables and two forms. The tables are Organizations and People. Similarly, the forms are Organizations Entry Form and PeopleEntryForm. The People are linked to the Organizations table. Several people can be linked to the same organization.On my Organizations EntryForm, I created a command button to duplicate a record using the wizard. It works fine.
I did exactly the same thing on the PeopleEntryForm, but instead of copying the record, it creates a new blank record. I don't get any error messages. Is my problem due to the fact that the People table is linked to the Organizations table?
View 13 Replies
View Related
Feb 16, 2006
Works great, but when I hit the number "3", (3 times in row) it will let me into the form. I want it to not let me in IF I don't know the password.
Where did I go wrong?
Private Sub Form_Load()
Dim pw As Variant
If InputBox("What is the password?", "Password") = "1" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
If InputBox("What is the password?", "Password") = "2" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
End If
End If
End Sub
View 14 Replies
View Related
Jan 14, 2007
I protect my code from people being able to read it by setting a password on the code from Tools > Properties, selecting the Protection tab and entering a password, and clicking "Lock Project"
Is there a way to write code that will remove that Lock Project check and check it back on?
I've looked through the Application.SetOption command and it doesn't seem to be one of the choices. It would be very helpful if someone knew how to do this.
Thanks
SHADOW
View 6 Replies
View Related