Queries :: Calculating Different Date Formats

Jul 18, 2014

I have a query based on two tables that calculates days elapsed between two dates. Problem is one date field is date/time and the other is short date, so the result is always infinite decimal places. I don't want to change the date format for the date/time on the one table. I tried the format function in the query, but still ended up with decimal places.I just want to make that one long date to act like a short date for calculating "days", not hours, etc.

View Replies


ADVERTISEMENT

Queries :: Calculating Most Current Date

Nov 10, 2014

I have a problem with my query to calculate a most current date. I have try everything to explain all current data at each TubeNumber, but there are no calculated answers at TubeNumber 7, IC2, IC3 and IC4. Why not these TubeNumbers? See below my formula to find a current date:

Code:
FindACurrentDate: IIf([Time]=DMax("Time";"qlkp_FindACurrentDate";"[Date]=#" & DMax("Date";"qlkp_FindACurrentDate";"[TubeNumber]='" & [TubeNumber] & "'") & "# And [TubeNumber]='" & [TubeNumber] & "'");"This is most current date!")

I have send my database as an attachment with my question.

View 3 Replies View Related

Date Formats In Query

Mar 20, 2007

Hi,
and again I have a problem :)

so,
In Form I have two calendar controls, and two text boxses. Based on selected date, text boxes are showing day of the year (format(Date(),"y")).

Query is based on table with records of days in year (numbers),
so, when Im trying to sort Between two numbers from Form (in this case nubers of days in year) query is reciving (imo) full date, not the number and im not getting any results.

Any non VBA ideas?

tnx

View 4 Replies View Related

Qry Nested If's And Date Formats

Jul 11, 2007

I'm tring to build a queary based on a field called "PLAN_CODE" and a policy issue date field. There are 3 plan codes ...select1, select 2 and select 3.
I want to pull information as follows
Select1 has an issue date 11 months ago (day the queary is ran)
select2 has an issue date 23 months ago
select3 has an issue date 35 months ago

I built a field to calculate the duration of the policy based on the issue date
Duration: (Round((POLICY_ISSUE_DATE]-Date())/365,1)). I think the format is wrong? need help with that!

I built a field to create a nested "IF" statement but the first "IF" is not pulling correctly, so I cant built the remaining "IF" statements.
Expr2: IIf([PLAN_CODE]="SELECT01N" [Duration]=-0.9,0).

Anyone have a better approach? Any input how to handle this? I'm not real good with VB code but have a general understanding of it. It's easier for me to build in the QBE.

View 2 Replies View Related

Problems With Date Formats

Feb 23, 2005

Hi Folks,

I have a table of dates and a "frm Dates" based upon it. I have copied a very useful calendar control which I found on a sample datebase elsewhere on this excellent site. I want to have an [OK] button which jumps to the correct date in my frm Dates when I choose that date on my calendar, but it's getting the months and days the wrong way round. Here in the UK our favoured approach is dd/mm/yy, but to try and figure this thing out I've formatted everything as Long Date.

Further details are on the form of the attached database, I hope you can help. Non-technical answers most appreciated as I'm pretty much a greenhorn with this stuff.

Thanks all!

View 2 Replies View Related

General :: VBA Date Conversion To SAP Formats?

Oct 13, 2013

I've been trying to convert a date format like dd/mm/yy to a SAP used format like dd.mm.yyyy .

a simple string conversion like

Code:

pp = day(datefield) & "." & month(datefield) & "." & format(year(datefield),"YYYY")

is not working, the year is converted wrong.

thus 17-07-62 should be converted to 17.7.1962 ( European date format )

View 1 Replies View Related

Forms :: Data Entry Textbox - Date Formats

Jan 26, 2015

I have a date entry textbox.

The user is copying a date from an old form.

Sometimes the form will have the date entered as 00/00/97

How can I change the month and day to 01/01 and of course leave the year as it was.

I've tried using split with no success.

View 12 Replies View Related

Queries :: Different Time Formats In Same Column

Oct 18, 2013

I have a query that is drawing info from a union query. There is a time column that is bringing over time in long form (ex 10/17/2013 4:00:00 pm) and just the time (4:00:00 pm). This actually works for me b/c it lets me see which table each piece of data came from but I need a helper column w/ a formula in it to identify it for me. Basically, I need a calculated column that will bring back the word "scheduled" and if it's in short form "actual".

View 4 Replies View Related

Calculating A Date Six Months In Advance Of A Date

Jan 20, 2005

Not sure if this is actually done with a query (hell, im not sure if it can be done) as my Access knowledge is limited but I've got a list of Patient Appointment Dates and I need to calculate a list of 'Next Appointment' dates that are six months on from the appointment dates not including saturdays and sundays (as the health center is closed those days)

BTW this is for coursework, I don't really work for a health center :S

edit: I seem to have put this in the tables forum, not the queries forum. Apologies for that, I'm not an Idiot, Honest!

View 1 Replies View Related

Calculating Working Date Between Orderdate And Due Date

Jan 11, 2007

I have a query in which I have a long list of orders and their entry date and due dates. I would like to calculate the number of working days between the both in order to see if the supplier keeps his theoretical delivery term.

Can some one give me a way to achieve this in my query:

DUE DATE.........ORDER DATE.........DELIVERY
03-03-2007 -/- 24-10-2006 = working days

many thanks in advance

View 2 Replies View Related

Calculating Date

Jul 17, 2006

I'm suppose to create a list of 20 clients and let 5 accounts be 90 days past due, 5 over 60 days, 5 over 30 days three of each of the accounts have a balance over $75. Then it asks me to create a total of all account balances so that the total amount of recievables can be known. The second part is calculate the number of days each balance has been outstanding.My question is this can you calculate dates in a table or can you only calculate in a query. I'm new at access so have patience with my question(s)

View 3 Replies View Related

Calculating A Date Lag

Feb 23, 2007

HI there,

I have a dataset that contains a date 'dd/mm/yyyy' and a timelag indicator eg; '1' = 1 day after the date specified in the record, 2= 2 days after the date specified in the record etc, etc.

I'm trying to calculate what the 'lag date' would be utlising the original date and the timelag indicator.

Hoping someone can assist.

Many thanks

View 2 Replies View Related

Calculating A Date

Oct 20, 2005

Hi all
I have a text box in a form which is displaying the date with a default value : =date()

Now what i want is another text box which will display for example 30 days after of the todays date.

Lats say today is 20/10/2005 the second text box will calculate 30 days after and will show 20/11/2005

Thank you.

View 1 Replies View Related

Calculating Age At A Particular Date

Aug 19, 2007

Please help me calculate the age of a person at a particular date without having to go into the expression every year and change the cutoff date year.

A basketball player in our league must be a certain age as of Sept. 30 each year to play in leagues divided by age. I am using the following to calculate age:

age: Int(DateDiff("d",[bdate],#9/30/2007#)/365)

I want to get rid of the 2007 and replace it with an expression that will automatically change every year.

Thank you for your time and consideration

View 3 Replies View Related

Calculating First Date Of The Month

Dec 16, 2005

hi

i need a function to work out the first date of the month if i give it a date ...


e.g.

23/07/05

returns 01/07/05

im sure this is simple let me know!! thanks!!

View 1 Replies View Related

Forumla For Calculating Date???

Apr 25, 2008

Heres one for you all...maybe you can help me...

I have a combo box that triggers a calendar, which the user selects and it then populates the combobox with the date.

Great everything is working....BUT...what I want to do next is populate another combobox with another date in regards to the one just choosen...

So say on the OnChange Event or AfterUpdate Event on the first date:
I want 2 Weeks plus a day and the next tuesday.
So basically I want to look out 2 weeks plus a day and then grab the next available tuesdays date...

Anyone have any idea how to accomplish this...

I am completly lost here...

Thanks

View 5 Replies View Related

Calculating Quarter Out Of Date

Feb 22, 2013

I have a dataset in which i have a variable Bill date like this

Bill Date

1/16/2012

11/16/2012
11/16/2012
11/16/2012
11/16/2012

1/16/2012
1/16/2012
1/16/2012
1/16/2012
1/16/2012
1/16/2012

Now I want to create another variable 'Quarter' and calculate this on the basis of Date and want the output as follow

Quarter

Q1-2012

Q4-2012
Q4-2012
Q4-2012
Q4-2012

Q1-2012
Q1-2012
Q1-2012
Q1-2012
Q1-2012
Q1-2012

I was thinking of doing this with the combination of Datepart and if-elseif combinations. But as per my knowledge access donot support multiple Ifs statement and I dont want to write VBA code for the same...

View 5 Replies View Related

Time Date Formatting For Calculating

May 15, 2006

Apologies if this has been asked before but I have a problem with being able to format a field to accept an hours and minutes value where the hours is likely to be greater than 23.

I have a form that is related to the number of hours that people spend in respite care. Entry and exit dates are provided in a standard way - date and time - and I have a calculating field that calculates the number of hours and minutes that a person spends at the respite centre based on the entries made in the entry and exit fields.

Because the calculating field is based on a module and I need to have the calculated amount included in the table, I have set up a VBA code to copy the result of the calculation into a controlled field elsewhere in the form.

However, I also need the form to do a simple calculation whereby hours spent away from the centre are subtracted from the total hours calculated by the module, so I need the copied value to be in a format where the calcualtion is able to be performed.

In a nutshell I guess I am looking for a time format for the field that will allow the recipt of a value greater than 23 hours.

Of course perhaps I need a more sophisticated VBA script to perform the calculation behind the scenes but I admit to not being particularly at ease with VBA scripting. If anybody has any suggestions about either a format or a suggestion for a VBA script, I would be very appreciative.

Thanks in advance,

Chris Montgomery

View 4 Replies View Related

Calculating Dates Between To Date Ranges

Dec 27, 2007

Calculating dates between TWO date ranges:

I've read alot of good things on here regarding the DateDiff function and some other things that have helped me out finding the difference between two dates within the same entry on a table. I have a question regarding finding the difference in days between two entries within the same table.

Example:

test table:

Date ID # Name
1/18/07 1234567 Smith
1/20/07 1234567 Smith
1/23/07 1234567 Smith


Is there a way to find the difference in days between the lowest and highest dates? I want to know how many days are between the first entry and the last entry? In this cae it would be five days. I can do it if the days are in the same entry with the datediff function, but having trouble finding it in this situation. Any pointers?

(Thanks for all the good information on this site, BTW. I've seen a few posts similar to this, but it didn't really answer my question)


Don

View 3 Replies View Related

Calculating A Date From Another Table Record?

Sep 20, 2013

Code:

DateAdd(Interval, Number, Date)

function. More specifically, I am using a bound form with a table that I need the date for. The date will be an integer from a specific record (based on a search) from a separate table, and then added to the current day's date (in days).

View 3 Replies View Related

Calculating Date Difference In Query Or Sql

Jul 29, 2014

I'm trying to work out the difference between 2 records both of which have a call out date [bas start date]..basically the structure is

equipment number call number bas start date
12345678 112255 1/7/14
12345678 112256 3/7/14

What i'm after is the 4th column to work out the date diff... in this case 2 days the equipment can be multiple times so i might see equipment number 12345678 - upto 10 times with consecutive dates - all of which i need to know the difference between the current call date and the previous call date..

View 3 Replies View Related

Calculating Age In Years And Months From Date Of Birth

Jan 19, 2006

I have a Date of Birth field in format DD/MM/YYYY and wish to set up 2 new fields one that calculates Age in Years and another that calculates Age in Months.

Thanks

View 14 Replies View Related

Calculating Date/time Remaining In A Table

Jan 12, 2007

Hi, I have never had to do any maths with access before, so I don’t know where to start!

What I need to do is calculate remaining warranty information for products.

In the table I have 3 columns. the 1st column is "Purchase date (dd/mm/yy)" 2nd column is "Total warranty period (years)" and the 3rd column is "Remaining warranty"

The 3rd column is the one that will display how long is left to run! How do I achieve this?

Thanks.

View 6 Replies View Related

Calculating New Date And Returning Dates Within A Range

May 26, 2005

I am tring to add a number of years to a dob. I'm doing this by adding my
date+years*365.26 I get a string of numbers. I then convert the number in
the next column to actual date again. I'm getting the correct date. I've tried unsuccesfully to use the dateadd function.Now I want my criteria on that column to allow me to return only date in a given to from period of my choosing. I want to determine the date range each time I run the query.

An example would be:

DOB age date I need to follow up
5/15/1935 + 75 = 5/15/2005

Then I request dates between 4/1/05 and 5/30/05 and this date is returned.


I've tried the between_and functions but nothing is returned. I'm guessing it's because the column is still a calculation and not a true date.

I've tried: Adding a column to my query to convert the number string back to
date format (criteria failed to return a result). Adding a second column
that ='s my converted number to date column (criteria failed to return a
result)
And I've tried the make table query and then run another query from the new table. I can't seemto make this happen. I think it's not recognizing my data as a date because it isn't returning any data.
Is ther an easier way to: Add a number of years to a date and have it return
a date and not the number string?
If not is there a better way to convert the string to a date?
Is there a way to get my criteria to recognize the date and return date
between my begin and end dates?

Thanks

View 14 Replies View Related

Query Calculating No Of Days Between Start And End Date

Mar 20, 2007

i am trying to run a query from a form which will bring up the no of days difference between the start and end date also on the same form.
The query doesn't bring back any results can someone please guide in what i am doing wrong.
Here is the query
SELECT DateDiff('d',[start date],[end date]) AS [no of days]
FROM [booked property]
WHERE ((([booked property]![start date])=[forms]![booking]![booked property]![start date]) AND (([booked property]![end date])=[forms]![booking]![booked property]![end date]));

Thanks

View 2 Replies View Related

Expression Help: Calculating Number Of Fridays Since A Certain Date

Feb 7, 2005

So I have a database of contacts, and it has their birthdays listed as well. What I need to do is calculate the number of Fridays from their birthday up until today's date. How do I do this? Thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved