Calculate Distance Using Fields In Form?
Sep 28, 2013
However Instead of entering the postal code continually or selecting from the combo box. How do I set zipcode 1 and zipcode 2 based on fields in a form. I would like to use the current FROM (Default zip based on the current job in form) and TO zipcode [Project Postal Code] that is on the form at that time. Not necessarily combo box.
Currently you have
zip1 = Me.Combo2.Value
zip2 = Me.Combo4.Value
I want Zip 1 to be Default zip (however I can still change it if needed) ZipCode field is [Project Postal Code]
Here is the full code:
Private Sub Command6_Click()
Dim zip1 As String, zip2 As String
Dim sResponse As String
Dim sLink As String
On Error GoTo Command6_Click_Error
zip1 = Me.Combo2.Value
[code]....
View Replies
ADVERTISEMENT
May 3, 2013
I am using Access 2010 and am developing a form where the user provides a full address - street number, street name, city, province and postal code.
I want a control to calculate the distance from this address and a fixed address.
View 2 Replies
View Related
Aug 19, 2007
Dear All:
I have a problem in calculating distance's mileage.Eg: Distance from town A to town B is 10km, town A to town C is 20km and vice versa. I have a table named "Location" as per below:-
Town From , Town To , Distance
A , B , 10km
A , C , 20km
A , D , 30km
B , A , 10km
C , A , 20km
D , A , 30km
My question now is: if there are 26 locations, (Eg: A - Z), then I need to key in the distance one by one and it is very tedious. Because Town A to Town B is 10km and Town B to Town A also 10km.
Is there any method that Access will consider A to B and B to A is same distance?
Can we use crosstab to calculate?? and what is the step?
Pls help & thanks in advanced.
View 2 Replies
View Related
Aug 22, 2006
I would like to have a back-end Database on a host server and link a
front-end Database to the tables several hundred miles away. The problem is I have no experience whatsoever in doing this.
My questions are:
(1) Can tables be linked via the Internet?
(2) If they can, is this the best way to link the tables?
(3) If it is possible to link the tables by the internet or by some other way; how do I do it?
I really would appreciate any help you can give.
Kind regards
Eddie
View 1 Replies
View Related
Jun 22, 2007
I am trying to create a query that gives me a cummulative distance based on a previous footages. For example I am prompting the user to enter a Route. Along the route are several points having a numeric value. Each of these points along the route have a footage accociated to the previous point. I want the query to display the Route, each point along the route based on the users defined start and end point, show each backspan footage and provide a cummulative distance based on the progression of the route. Below is what I have created so far. Now I want to add a cummulative distance.
SELECT PED.ROUTE_1, PED.TERMINAL_1, PED.TERMINAL_1, Sum(PED.BACKSPAN_1) AS SumOfBACKSPAN_1
FROM PED
GROUP BY PED.ROUTE_1, PED.TERMINAL_1, PED.TERMINAL_1
HAVING (((PED.ROUTE_1)=[ enter route ]) AND ((PED.TERMINAL_1)>[enter start]) AND ((PED.TERMINAL_1)<[enter end]));
Thanks for any help.
View 2 Replies
View Related
May 3, 2014
Before I design an application in Access, I need to know if a certain task is even possible. I have a table of Physicians containing their names and addresses. What I would like to accomplish is to be able to provide an entry screen that would allow a user to input their zip code and have Access search for the physicians with a
1) 5 mile radius
2) 10 mile radius, etc.
Is this even possible within Access?
View 2 Replies
View Related
Mar 8, 2015
I use Access 2003 and want to make a query by specifying the start and finish within the city, I send files. a
Attached file ...
View 1 Replies
View Related
Sep 5, 2006
Hi, can someone help me with this formula.
i have one field called notice_1 this field is a combo boxs multiple choice ( YES - NO )
Then i have 1 field called score_1 ( if notice_1 = "YES" then let score_1 = 5 else then let score_1 = 10 )
Does this make sense , I am an, amateur at access 2003,
Thank You For any help i can get...
John Calcitrai
View 5 Replies
View Related
May 23, 2005
I need to pull 2 fields from 2 different queries, then calculate them. Those 2 queries has the same structure, just one is last year's data, the other is this year's. :rolleyes:
I tried using the 3rd queries to combine them, then calculate from there, but then I had no clue where to go from there. I don't sql...I was wondering is there anything to do with sql? :confused:
I'm stucked so far, how can I solved it? :mad:
View 5 Replies
View Related
Jul 23, 2007
In a query that utilizes a table containing a yes/no field, is it possible to calculate the yes/no field and get a numeric return? If so, can you share how this could be accomplished. Thanks
View 5 Replies
View Related
Aug 15, 2007
I am trying to get one of my fields to calculate this expression which includes other fields:
If "Financing Type" = 1 or 2 THEN 20% * "Loan Amount" OR
If "Financing Type" = 3 or 4 THEN 100% * "Loan Amount" = 2,000,000.
I thought that this would work, but it only works in queries or reports.
=IIF([FinanceType]<3,[LoanAmount]*.2,[LoanAmount])
I would like this to work in the table.
View 11 Replies
View Related
Apr 23, 2013
I have 2 tables - Customer and Carrier.
Each table has 3 headers (fields). Address, Latitude and Longitude.
I would like to know, how I take the value of the field (i.e Latitude) in each table for my calculation?
In Excel, I can specify the column (i.e Range("B" & x) - where 'x' is the row number)...but how do I do it in Access.
Code:
Dim db As DAO.Database
Dim rsCustomer As DAO.Recordset
Dim rsCarrier As DAO.Recordset
Set db = CurrentDb
Set rsCustomer = db.OpenRecordset("Customer")
Set rsCarrier = db.OpenRecordset("Carrier")
[Code] .....
View 2 Replies
View Related
Aug 10, 2015
I have a database that I need to calculate a query that is based on 3 dates. For example...I have an admission date and a discharge date in my database.
If there is no date in the discharge date then I need it to calculate the number of days between the admission date and "today's date." If there is a discharge date then I need to calculate the difference between admission date and discharge date.
Basically i need a length of stay figure. I can figure the formula in excel but cannot get it to work in access. Here is my formula in excel
=If(ISBLANK(DischargeDate),Sum(today()),sum(Discha rgeDate-AdmissionDate)
View 2 Replies
View Related
Apr 10, 2015
Here is an explanation of my database and what I am trying to accomplish. The tables and their fields I have in the database are as follows .....
EmployeeT
EmployeeID - Primary Key - Number (not an autonumber because they are county assigned)
LastName - Text
FirstName - Text
EmailAddress - Text
JobTitle - Text
MobilePhoneNumber - Text
Notes - Memo
[Code] .....
All users will interface with a form that will fill in data on the TransactionT table. I would like the data that is being input into the TransactionT.Quantity field to change the data ItemT.ESBuildingQty or ItemT.D3Qty based on which is chosen from StockLocation (they will only be able to choose either "ES Building" or "D3" when inputting the data).
For example, if the stock amount at the ES building of a particular item is 3 and the stock amount of that same item at D3 is 1 and a user inputs that a quantity of 3 of a specific item are taken from ES Building and Added to D3, I want the new values of ESBuildingQty to be 0 and D3Qty to be 4.
View 11 Replies
View Related
Dec 18, 2013
I am trying to calculate 5 fields within my table in a query, but for some reason some rows do not calculate as not all the fields contain a value (blank field), is there anyway that I could bypass this issue?
View 1 Replies
View Related
Oct 28, 2012
I need to calculate the average repaired time and the average response time. These are two separate fields ....
View 1 Replies
View Related
Jul 12, 2005
Hi everybody. I got a access 2000 query that lists :
1)weekno
2)year
3)project (project number )
4)QweekylyReportHeader (project description )
5)customer (customer that requested this project)
6)department (department number and name that implements this project)
7)Projectleader ( project leader name and number that is responsible for this project)
8)Task (Task number that is done for this project )
9)task description (description of task )
10)employee ( employee number who is working in this project )
11)name (Employee name and initial and last that works for this project )
12)hours ( number of hours employee worked in this task ) ==> i want cumulative for this
13)salary (amount of salary given to this employee) ===>i want cumulative for this
I want to create another query that lists :
A)cumulative value of hours worked on particular project task up that point.
b)cumulative value for wages given for that project task up that point.
http://i5.photobucket.com/albums/y180/method007/weeklyprojectdata2.jpg ( query output sample)
The above query ONLY lists hours worked and wages gives for particular project task only during
each week.But i want hours worked and wages give for particle project task up to that point in week. For
example a project task might have implemented last week but not this so i want to take that in calculation as well.
I be happy if some expert show me how i can calculate the cumulative value for hours worked and wages given for particular
project task.
Notes:
- There is a possibility that during a particular week no task been implement for particular project.
- One employee can work in more then one project
- One employee can have more then one salary (amount) for the same
project because he might get raise in salary!
- Only tasks carried this week will be printed in the weekly report
http://i5.photobucket.com/albums/y180/method007/constraint.jpg ( pic of database)
http://i5.photobucket.com/albums/y180/method007/hourlywagesroportfinal.jpg ( query output population)
http://i5.photobucket.com/albums/y180/method007/queryindesign.jpg (query in design view)
query that display hourly wages of certain project during each week
SELECT
querythisweek.weekno,
querythisweek.Year,
querythisweek.Project,
QweeklyReportHeader.Customer,
QweeklyReportHeader.Department,
QweeklyReportHeader.description,
QweeklyReportHeader.ProjectLeader,
querythisweek.Task,
dbo_Task.description,
querythisweek.Employee,
[lastname] & ' ' & [initials] & ' ' & [insertion] AS Name,
querythisweek.hours,
querythisweek.Salary
FROM
dbo_Task
INNER JOIN ((QweeklyReportHeader INNER JOIN querythisweek ON QweeklyReportHeader.projectno = querythisweek.Project) INNER JOIN dbo_Employee ON querythisweek.Employee = dbo_Employee.employeeno) ON dbo_Task.taskcode = querythisweek.Task;
code for querythis week( calcualte the salary and hours worked)
SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hours_worked.hours, (select a.amount * dbo_Hours_worked.hours
from dbo_Hourly_wages a
where dbo_Hours_worked.Employee = a.Employee
and dbo_Hours_worked.Project = a.Project
and a.Year * 100 + a.weekno = (select max(b.Year *100 + b.weekno)
from dbo_Hourly_wages b
where b.Year < dbo_Hours_worked.Year
or (b.Year = dbo_Hours_worked.Year and b.weekno <= dbo_Hours_worked.weekno))) AS Salary
FROM dbo_Hours_worked;
View 5 Replies
View Related
Apr 16, 2014
i want to use the AfterUpdate to calculate the age using two text fields
Private Sub Birthdate_AfterUpdate()
Me.AGE = DateDiff("yyyy", [Birthdate], Now()) + Int(Format(Now(), "mmdd") < Format([Birthdate], "mmdd"))
End Sub
but it wont work
View 4 Replies
View Related
Sep 22, 2011
I have a form for entering safety training which includes Training Date and Expiration Date, this form is called "Scheduled Training".
The table containing training courses with their requirements and frequency is called "Course Requirements". Within this table I have a "Frequency" field (1, 2, 3, 4, 5, 6) and another field "FrequencyPeriod" (Year, Month, Initial, As Needed).
What I'd like for the Expiration Date on the form to do: Use [ScheduledTraining].[Training Date] and look at both the [CourseRequirements].[Frequency] & [CourseRequirements].[FrequencyPeriod] to populate the Expiration Date.
View 14 Replies
View Related
Feb 11, 2013
I have two fields CountOFAppeal Description and Amount Solicited.
I just want to get the percentage of response.
So this is what I used:
=[CountOfAppeal Description]/[Amount Solicited]*100
When I go into the properties to format it to % and add decimal places I get a crazy number
2520.46% When it should be 25.20%
When I change the format to general number the decimal point is in the right place 25.20.
View 2 Replies
View Related
Feb 21, 2015
I have never created a single query that is this complicated.
I have a time card database that I created back in 2003 and over the years more and more new task have been added to track volunteer hours has been incorporated into it.
The current task at hand is to determine if each member has achieved or meet certain requirements each year.
They are attend 8 or more meeting and check into 5 or more nets and be Net Control Operator for at least 1 radio net and be involved with 1 or more Public Service events. Sounds simple right?
I have a table that contains all the information that is need to get the result I want but am having a problem getting that result with a single SQL query. Everyone you talk to says use SQL query to obtain calculated vales and never store/save them.
So from the work table below meetings consist of 4,5,6,8,9 and nets consist of 10,11,14,15 and events consist of 2, 13 and the NCOflag is T/F.
So those are all the parts, count the number of meetings, nets, events and NCO and set the meets requirement flag T/F
I keep getting "You tried to execute a query that does not include the in specified expression <name> as part of an aggregate function. (Error 3122)"
In this case it keeps complaining Callsign.
You did not enter an aggregate function in the TRANSFORM statement.
Here's the statement I created:
SELECT [Work Hours].Callsign, [Work Hours].[Work Code], [Work Hours].NCOflag,[Work Hours].SubMtg, [Work Hours].SubNet, [Work Hours].SubNCO, [Work Hours].SubEvnt, [Work Hours].ReqSubTotal
FROM [Work Hours]
WHERE (((Year([Date Worked]))=(Year(Date()))))
[Code] .....
It is failing on the first HAVING (([Work Hours].SubMtg) =Sum(). I have tried many variations and changes but don't see what is wrong with it.
View 2 Replies
View Related
Feb 7, 2013
The client db is not Normalized. I understand their requirements and the current table structure has to remain in tact. The DATA ENTRY staff need to enter cost for Jan1 to Jun30. One of the many calculations that needs to be updated daily is the SUMPRODUCT from STARTDATE to ENDDATE - which can be anywhere between Jan1 to Jun30 at any time. The example scenario below needs to calculate the sumproduct of COST between any two fields (days of week).
For example, let's say a Purchase Order has been created, and the initial entry looks like this:
StartDate = Jan30
End Date = Jun3
Jan1 thr Jan29 Cost = 0
Jan30 Jun3 Cost = 500 (average daily burn rate)
Jun4 thr Jun30 Cost = 0
... and the staff receives and update to the daily spread. The Cost Spread between Jan1 to Jun30 NOW looks like this:
StartDate = Jan15
End Date = Jun3
Jan1 thr Jan14 Cost = 0
Jan15 thr Mar29 Cost = 1000
Mar30 thr Jun2 Cost = 3000
Jun3 thr Jun3 Cost = 500
Jun4 thr Jun30 Cost = 0
Here is the code I am using to calc the SumProduct upon initial entry WHERE the 'average daily burn rate' for the entire spread is the same every day (FYI, we need to calculate the YearTo-Date (YTD) and Remaining Balance from YTD for both Commitment and Incurred cost. For this example I am only snipping the code here to reflect Incurred)
'Initial Calculations
If CurrentDate <= frmStartDate Then
SUMPRODUCTYTDIncurred = ZeroValue
Else
SUMPRODUCTYTDIncurred = Round((DailyBurnRate + DailyBurnRate * NoDays), 2)
End If
If CurrentDate <= frmStartDate Then
SUMPRODUCTRemainingIncurred = ZeroValue
Else
SUMPRODUCTRemainingIncurred = Round(DailyBurnRate + DailyBurnRate * (DateDiff("d", CurrentDate, frmEndDate)), 2)
End If
In my UPDATE SCRIPT (not shown - cause its TMI):
- I am using the For Each statement to search an ARRAY to filter for the db.TableDefs("MYTABLE").Fields I need to update.
- I am also using a Do While True statement to catch the cost for the found field.
How to catch all the cost between Jan1 to Jun30 before updating the SUMPRODUCT field and then .MoveNext until EOF (remembering that the For Each statement will stop when field Jun30 is found)?
View 1 Replies
View Related
Apr 22, 2013
I am trying to calculate the difference in dates between two fields. How do I find the difference in days between field one which contains the date 04/12/2011 and field two which contains the date 04/12/2013? I have tried to use the datediff function, but it keeps telling me it doesn't recognize the field name, even though the spellings correct.
View 3 Replies
View Related
Oct 26, 2014
I have to create a query in access that will calculate two Date and time fields [Date & Time Left]/ [Date Returned], need to figure out between the two fields. Trying to identify when the rep returned the call and the number of business hours (6:00am - 4:30pm) it takes to return a message in Ms Access 2010.
The only issue is the calculation has to be done by time and so I have to calculate what time they left the message(so the difference between [Date & Time Left] and [Date Returned) [Date & Time Left] and when the rep returned the message which is suppose to be [Date Returned] but the problem with this field is the data entry is in date format (10/9/2014, 00/00/0000) of Date and not Date and Time like the [Date & Time Left] field, so I don't know what to do now. Not sure what to do now not a database that create or have allot of control over.
View 4 Replies
View Related
Jun 2, 2015
I have a database that will take lots of data entered by an employee and calculate some additional data and generate quotations. Within each quote there is a possibility for 15 different metals (5 Precious Metals, and 10 Base Metals). There is also 5 fields that need to be filled out about each Metal (What the metal is, the market being used, weight, whether it is included in a different price, and the price). I currently have 75 fields to address each Metal and their 5 fields respectively. Is there a better way to Normalize this data, and accomplish what I need accomplished? I want at the form level the employee to tell the database whether they want to add a Precious Metal, or Base Metal, or Move on to other data entry.
Here is a Screenshot of the design view of one of my tables with too many fields : table screenshot1.PNG
View 14 Replies
View Related
Nov 18, 2014
I have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.
All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.
I've been trying with Totals in the query and crosstab queries but am not familiar with them.
View 4 Replies
View Related