Help with Date / Time quey

[james_halliwell]

Hi All,

Please could someone help me out with the Date / Time field, I want to create a query that works out the time taken from the log being created to it being completed. I have a table with a field called Date_Returned & Date_Completed I have changed the properties on both to general date
The problem is when i create the query i put in
Log_Number – (Order Ref) – (Number Format)
Date_Returned - (Date ) – (general date format)
Date_Returned - (Long Time ) – (General Date format)
Date_Completed – (Long Time) – (General Date format)
Expr—Date_Completed – Date_Returned

The times all return 00:00:00. Please could someone advise where i am going wrong

Many thanks

 

[tranchemontaigne]

You might want to try the DateDiff function

http://office.microsoft.com/en-us/access-help/datediff-function-HA001228811.aspx
________
Buy scale

 

[james_halliwell]

You might want to try the DateDiff function

http://office.microsoft.com/en-us/access-help/datediff-function-HA001228811.aspx

Can DateDiif work of times as well

Thanks for the reply

 

[tranchemontaigne]

DateDiff(interval, StartDate, EndDate [, firstdayofweek] [, firstweekofyear] )


intervals may be set using the following arguments
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

If you wanted to count days then your syntax would be similar to

=DateDiff(?d?, [Date_Returned], [Date_Completed])


If you wanted to count hours then your syntax would be similar to

=DateDiff(?h?, [Date_Returned], [Date_Completed])
________
American Motors

 

[JANR]

Also you might find this to be of use as a function: http://office.microsoft.com/en-au/a...s-elapsed-HA001110218.aspx?pid=CL100570041033

JR

 

[james_halliwell]

Hi and thank you both for your replies these will come in very handly, However the time format still comes as 00:00:00

any sugestions

 

[tranchemontaigne]

When I run the query below

SELECT Table1.Date_Returned, Table1.Date_completed, DateDiff("d",[Table1]![Date_Returned],[Table1]![Date_completed]) AS Days_Elapsed
FROM Table1;

I get the following output

Date_Returned Date_completed Days_Elapsed
======================================
1/1/2001 10:46:38 AM 1/5/2001 8:57:32 PM 4
1/1/2001 1:25:16 PM 1/4/2001 5:16:17 AM 3
1/1/2001 4:27:18 PM 3/5/2001 2:25:43 AM 63

Is the 00:00:00 result something you see in a query, a form control, or elsewhere?
________
R2

 

[james_halliwell]

I get the following output

Is the 00:00:00 result something you see in a query, a form control, or elsewhere?[/quote]

Hi tranchemontaigne

I have attached a database with 1 table 1 query

the table has 3 fields ID / Return Date / Date Completed
the query then has ID Return date / Date completed both set to long time format

the query just reutrns 00:00:00 for ever record

This maybe a newbie question but always learning

 

[tranchemontaigne]

Can you re-post as an MS Access 2000 / 2003 file? We use MS Office 2000 here.
________
Vaporizer review

 

[james_halliwell]

Can you re-post as an MS Access 2000 / 2003 file? We use MS Office 2000 here.

Sorry i have changed it to 2003 .mdb

Thanks for your help

 

[boblarson]

The dates in the table are all just date. There is no time stored with them so that is why the time shows as 00:00:00.

 

[james_halliwell]

The dates in the table are all just date. There is no time stored with them so that is why the time shows as 00:00:00.

Sorry but this maybe a stupid question but how do you store times as i have never worked with them in access

 

[boblarson]

Well, the first question I have to ask back is how are you currently placing the dates in the table?

For example, if you have the date format set as General, then you can be sending it to the table using NOW() (if you are inserting the current date/time) or you can use

#12/31/2009 4:02:00 PM#

etc.

 

[james_halliwell]

I have a pop up form which has the Date returned field, If think the problen with using Now() is if someone enters a differnet form where they can complete them it will automatticly add the date even if they are just looking at it

I try'd and changed the format to long time on both and again they show as 00:00:00

Any advise

 

[boblarson]

I have a pop up form which has the Date returned field, If think the problen with using Now() is if someone enters a differnet form where they can complete them it will automatticly add the date even if they are just looking at it

Then you need to fix that.

I try'd and changed the format to long time on both and again they show as 00:00:00

Any advise

Yes, the data has NOT been entered with time so anything that has not been entered with a time will show as 00:00:00 period. I don't know how I can rephrase that. You must enter a date with a time in order for you to have a time. The only suggestion I have right now is to upload a copy of the database with bogus data so we can get it working the way you want. We need to find the points where the date is added and add the time at the same time.

 

[james_halliwell]

Then you need to fix that.


Yes, the data has NOT been entered with time so anything that has not been entered with a time will show as 00:00:00 period. I don't know how I can rephrase that. You must enter a date with a time in order for you to have a time. The only suggestion I have right now is to upload a copy of the database with bogus data so we can get it working the way you want. We need to find the points where the date is added and add the time at the same time.

Hi BobLarson,

I have attached example version of the database. Basically someone creates a new entry on the Enter new Dna 2 log (Form). The field called DNA date on this form i want to create a date and time field (If possible just show the date but register the time for my query)

when the log can be completed you open the Edit update reprint Dna logs (Form) find the required log and click open then you fill in the completed date field (Again recording the date and time but just showing the date) not bothered if it shows both just a nice to have))

Then the query called Time to process works out how long it took

Thanks for taking the time to look at this as i have no idea

 

[james_halliwell]

could it be possible to put some sort of if statment in i could add 2 new fields time_Entered & Time_Completed and when they enter the date the if statment would complete the time fields with now() wouldn't know how to do that but could look into it if could be done

Right i have have created the two new time fields i created a text box to test i put a iif function in =IIf([DNA Date]>1/1/2010,Now()," ") it show's the word Now() instead of the time ...lol how would i get the time to show

Thanks

any help