Hi there. I'm just jumping into Access and have the following question. In my Purchase Order Table, I have a date field calcualated as text, i.e. "01/12/04". I need to convert this to a date format in my queries so I can do calculations, i.e. 01/12/04 - 01/05/04 = 7 days. Can someone help me with how to convert this text date to a date format. Sorry if this is an easy question. - AJS
Hi I would like to manipulate some date information. I have a start date field and an end date field and I would like to draw up a schedule for payment where the Acess takes the finished date and the start date to get total days appart then devides this by a value set by the operator. say If the operator wanted 6 paymes then my total days would be devided by 6 giving me the number of days to add on to the start date. Then with these dates a schedule is made up and the code I have tried looks something like this.
Dim valuation1 As Date Dim valuation2 As Date Dim valuation3 As Date Dim valuation4 As Date Dim valuation5 As Date Dim valuation6 As Date Dim valuation7 As Date Dim valuation8 As Date Dim valuation9 As Date Dim valuation10 As Date Dim valuation11 As Date Dim valuation12 As Date Dim startdate As Double Dim findate As Double
Dim valcounter As Double
'retrieve start date and end date startdate = CDbl(Me.Start_Date) findate = CDbl(Me.Completion_Date)
'calculate evaluation periods Dim valCount As Double Dim valDays As Double
Select Case valcounter Case "1" Me.valuation1 = startdate Case "2" Me.valuation2 = startdate Case "3" Me.valuation3 = startdate Case "4" Me.valuation4 = startdate Case "5" Me.valuation5 = startdate Case "6" Me.valuation6 = startdate Case "7" Me.valuation7 = startdate Case "8" Me.valuation8 = startdate Case "9" Me.valuation9 = startdate Case "10" Me.valuation10 = startdate Case "11" Me.valuation11 = startdate Case "12" Me.valuation12 = startdate
I was wondering if somebody could help with a problem I have come across. I am currently developing a database for a small charity that I work for. The aim of the database is to record when staff undertake counselling.
I have created a query that calculates the current age of a client in the database uing the following expresion:
Age: Date()-[Date of Birth]
The Date of Birth is formated as a Short Date
In the query I have set the format of the column in the query to 'yy'. This then just displays the age of a client. This work fine apart from ages which are less than 30. If the age is less than 30 e.g. 29 it displays the date as 1929.
Does anybody know how I might be able to solve this or an alternative way in which to calculate dates and display it as 'yy'. (Its been a few years since I last work with access so skills a little rusty)
The reason behind the yy is that the monthly & yearly stats have to group by age ranges.
I have a field for 'deadline' date and I need to show all records where the deadline date is within 7 days of todays date. (Obviously todays date will change daily...)
I have tried subtracting the date from todays date but it doesnt work.
Just a quick request for a bit of help. Tries a few things but didnt work.
I have a form which has an employee start date. What I need to do is work out the holiday entitlement of each emplyee. The math behine this is, if they have been here less than a year, we do the following. 20 Days holiday entitlement per year, Divided by 52, then multiplyed by the ammount of weeks remaining in the financial year. eg, (20 / 52 = 0.384615384) * 31 = 11.92 So they would have 12 Days holiday entitlement.
But if they have been here for 12 months or more, they are entitled to the full 20 days holiday.
How would I express this as an expression or query or VBA to calculate this. I just cant figure it out, especially the if been here 12 months or more.
I have the start date, in a tbl called tbl_employees if that helps. ( also in the form frm_employee_details ) Our financial Year runs from the 1st December - 31st November
Several questions here, trying to lead up to the easiest code...
First is I have a table with some dates, and I need to do some date calculations, but one of the dates I need will vary. So I was thinking of having a parameter ask for it. Is this the best method?
Second, then once I have this date in, I'll need to have it subtract from another dated reference in the table to figure out the numbers of days difference. e.g. 05/31/06 - 05/01/06 = 30 Can you get two dates to subtract like that and come up with a general number field? If so, then will the dates have to be text, converted to numbers, or can stay as dates?
I've tried several methods and have been quite unsuccessful in each attempt.
I need to perform a query that works out how far in advance people book holidays. I have the arrival date and also the booking date, and I think I should be using DateDiff. However, I tried to put it in an existing query and it hasn't worked:
SELECT Bookinfx.[Park Name], Bookinfx.[Accom Type], Year([Start Date]) AS FilterYear, Count(Bookinfx.[Park Name]) AS Bookings, DateDiff("d", [Booking Date], [Arrive]) FROM Bookinfx GROUP BY Bookinfx.[Park Name], Bookinfx.[Accom Type], Year([Start Date]) HAVING (((Year([Start Date]))=2004));
I am trying to write a query that will return all records whose field expiration_date meets my criteria. The criteria is If expiration_date - 5 years < today's date then.... do you subtract the total days? months? Any help would be appreciated.
I am building a database and in it I need to do date calculations. Without too much detail I input a date based on the built in calender from the date/time field and based on that date I need to have the following data returned into other individual fields: 6 months from that date, the days until that date, 2 months from the date input, 4 months from the date input, 5 months from the date input, 60 days from the end date, 45 days from the end date, 30 days from the end date, and 15 days from the end date.
Example: I input "Sep 10, 2015" and I would get "Mar 10, 2016", "181 Days", "Nov 10, 2015", "Jan 10, 2016", "Feb 10, 2016", "Jan 10, 2016", "Jan 25, 2016", "Feb 9, 2016", "Feb 24, 2016".
I have read through websites regarding date calculations within Access but I cannot find the specifics for the application that I need.
I need to calculate (in a query) the Actual Time of a job. I have the start date, start time, end date & end time (all separate fields). This seems to works ok by subtracting the start from the end.
Then I need to calculate the Estimated Time (time it should have taken). This is done by taking the Quantiy divided by the Rate-Per-Hour. This seems to work out ok in the Query, e.g., 101000 / 15000 = 6.66667 hours. But when I display this result in a report as a "Short Time", I get 16:00 instead of 6:40. How can I get the correct display? The other problem is that I need to divide the Estimated Time by the Actual Time. This also gives me wierd results - I suppose because one is a decimal format time and the other is a "Short Time" format. How can I get the correct answer?
I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error; and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
I have an odd problem that has stumped me for several days. I'm working on a form that contains a chart. The chart is based on a query, which is based on another query, which is based on a table. In the top query, I need to put some calculated fields that operate on other fields. But when I try to multiply two fields together, I get Null. I'm pretty confident that the fields I'm operating on are numerical.
I want to create a table via SQL. This table should contain records that begin after a specified date.
Something is wrong with this syntax.
Code:
Sub TEST() Dim t As Date t = 1 / 3 / 2014 DoCmd.RunSQL "SELECT TRP.Customer, TRP.Material, TRP.Product_Class, TRP.TRP as Price, TRP.Valid_from, " & _ " TRP.Valid_to INTO [New_Prices] " & _ " FROM TRP " & _ " WHERE (((TRP.Customer)= 1223) AND ((TRP.Valid_from)>#t#))" End Sub
My goal is if the current date = 1st - 4th of the month to return the failure_date if it is >= 1st of the prior month and <= 4th of the current month (ie, 01/01/14 - 02/04/14)
this works: >=DateSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),4)
But if the current date > 5th of the month to return the failure_date if it is >= 1st of the current month and <= 4th of the next month.(ie 02/01/14 - 03/04/14)
this works >=DateSerial(Year(Date()),Month(Date()),1) And <=DateSerial(Year(Date()),Month(Date())+1,4)
But when I put it in the iif statement if will not work:
IIf(Date()>DateSerial(Year(Date()),Month(Date()),4 ),>=DateSerial(Year(Date()),Month(Date()),1) And <=DateSerial(Year(Date()),Month(Date())+1,4),>=Dat eSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),4))
SQL = SELECT table_testing_dates.Failure_Date, table_testing_dates.Failure_Date, table_testing_dates.FailureGrouping FROM table_testing_dates WHERE (((table_testing_dates.Failure_Date)=IIf(Date()>Da teSerial(Year(Date()),Month(Date()),4),(table_test ing_dates.Failure_Date)>=DateSerial(Year(Date()),M onth(Date()),1) And (table_testing_dates.Failure_Date)<=DateSerial(Yea r(Date()),Month(Date())+1,4),(table_testing_dates. Failure_Date)>=DateSerial(Year(Date()),Month(Date( ))-1,1) And (table_testing_dates.Failure_Date)<=DateSerial(Yea r(Date()),Month(Date()),4))));
I'm trying to create a button that posts an order and before it post certain criterias need to be meet. One of them is a date needs to be selected in the order, if not a popup show to explain. The trouble I'm having is referring to todays date in the VBA code. I've been reading and trying different things but not getting anywhere, hopefully someone can fill me in.
This is the code I'm working with.
If [PayOrderMoneyReceivedSubform].[Form]![OrderDate] = TodaysDate Then dosomething else donothing end if
The part I'm having trouble with is TodaysDate, what should I be putting there?
I've been banging my head of a brick wall with this one. Its probably straight forward... but I can't see the solution! A problem shared is a problem halved... or so they say!
Ok I have this table called CustomerComments. In it are the following columns
CustID Comment CommentDate
For each custID I want the most recent commentdate and its matching comment from that row... (there can be multilple comments recorded per custID)
Problem is I can't seem to get this to work.. instead its returning all rows. I have run a query that will sort and group this list. The top line of the grouping gives the most recent comment and commentdate per customer.
Does anyone know how I could just get that top line per custID rather than all rows returned?
I have a schedule table which contains a field for the start date of the schedule and another field that contains the start time of the schedule. I want to combind these two fields in a sql statement so that I can query for a range of schedules based on the date and time. The below sql statement works fine in a query, however, when I tried to use it in a opening statement in ADODB, it does not work. ADODB somehow does not like the combinding of the date and time fields. Can someone point me to the right direction in achieving the same result.
mysql = "SELECT tbl_Customer_Site_Schedule.*, CDate([startdate_sch] & " " & [starttime_sch]) AS StartShift FROM tbl_Customer_Site_Schedule WHERE (((CDate([startdate_sch] & " " & [starttime_sch]))>=#11/5/2007 20:0:0# And (CDate([startdate_sch] & " " & [starttime_sch]))<=#11/7/2007 20:0:0#)) ORDER BY CDate([startdate_sch] & " " & [starttime_sch]);"
I have a database that enables the banking details for a small club to be generated as an Excel spreadsheet that is then emailed as an attachment to the treasurer. The event procedure below works fine but I have been unable to incorporate the banking date from a control called BankDate to end up with the Excel file being in the form DGC Banking dd-mm-yy - is this possible?
I am trying to calculate the time between two dates where one date field might be blank or not. Where the field is blank I want to use the current date to perform the calculation. So far I have the following but I keep receiving an error message saying that the expression has a function with too many arguments. Is there a simpler solution to this?
IIF([LastOfEnd_Date] IS NULL,(DateDiff(w,[LastOfDischarge_Date],NOW()))/4, IIF(Not isnull([LastOfEnd_Date], Abs(DateDiff("w",[LastOfDischarge_Date].[LastOfEnd_Date]))/4
The Timestamp Field I have is formatted like this: 11/4/2014 5:56:46 AM. The field name is [Timestamp] it is in a table named INPUT_RedSheets_Plates. I have a query right now that pulls in certain fields from this table based on criteria of the [Type] field Like This: