Help with Date / Time quey

james_halliwell

Registered User.
Local time
Today, 04:15
Joined
Feb 13, 2009
Messages
211
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
 
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
 
Last edited:
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
 
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
 
Last edited:
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
 

Attachments

Last edited:
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.
 
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
 
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.
 
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
 
Last edited:
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.
 
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
 

Attachments

Last edited:
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
 
Last edited:

Users who are viewing this thread

Back
Top Bottom