time criteria

[nicola1]

HI i was wondering if anyone could help. I am doing a database for a fuels company. They want to know when fuel has been dry for over 24 hours. There is a 'dry time' field which is actually calculated off the runout date and the delivery date. I thought i would simply have to put >24:00 in the dry time field, but this does not work. The field type has been set to text, and so im assuming this is why this does not work. There are over 8000 records so i can't obviously change these manually. I am wondering if there is anyway around this?

 

[ColinEssex]

If your date fields are text you will need to make them dates.

You can then do a DateDiff() to get the difference.

Col

 

[Bat17]

Access works in Days naturaly so just calculate from your orignal fields
iif([RunoutDate]-[DeliverDate]>1,"OverDue","")

HTH

Peter

 

[nicola1]

Ive tried changing the format to date, but over 700 field get deleted.

I have also tried taking the delivery date from the runout date, but there is a problem with this.

A runout may occur at 11pm on 05/09/05 and the delivery may arrive 10am on 06/09/05, if i took these away yes it would show up at 1 days difference, but it isnt actually a day.

Im confused! i don't think i will be able to solve this. Any more suggestions would be really helpful.

Thanx

 

[ColinEssex]

So how do you record the time? a separate field or with the datefield?

Col

 

[raskew]

...so just calculate from your orignal fields
iif([RunoutDate]-[DeliverDate]>1,"OverDue","")

If [RunoutDate] and [DeliverDate] are text fields, as stated by the poster, we're not going to be doing any date-math with them. The above just yields a 'Type Mismatch' error.

One way out would be to add two fields to your table, in date/time data type, e.g. [RunoutDate2], [DeliverDate2], then create an update query using the DateValue() function to populate the new fields with the properly typed dates.

Bob

 

[nicola1]

I don't think i have explained myself very well. In one field goes the runout date and time...in another goes the delivery date and time and then from here a dry time is worked out just on the times. Don't ask me why it has been done this way, was the guy who build the thing!

Im just not sure whether there is a solution...except for changing the field type, but it would lose so much data which is critical to the company!

 

[Bat17]

If [RunoutDate] and [DeliverDate] are text fields, as stated by the poster

Original

There is a 'dry time' field which is actually calculated off the runout date and the delivery date.

I was working on the assumption that if they had calculated from them already then they were date fields.

Still the real question is what data is stored and how!

Peter

 

[ColinEssex]

Nicola - is the data like this(albeit text) in your fields?

01/05/05 12:15
08/08/05 15:45

If so you can use CDate() in a query to convert it to the proper format

CDate([RunOutDate])

Col

 

[nicola1]

For the runout and delivery...the date are only stored and both are text/time fields.

The dry time is worked out from the times entered...but for some reason is in text format. A calculation is performed here, but the times are displayed differently...eg
3:00 , 16:32 , 06:12 .......due to this the problem which i am having is when i am trying to select the dry time < 24:00 , it is also selected times like 3:00, because it is just looking at the first number, ie 3 is obviously greater than 2...it really should be 03:00

Sorry for the confusion....i thought this would be simple...guess not

 

[ColinEssex]

Nicola -

1) is the date and time in the same field or is the date in one field and the time in another

2) Does the 3:00 you gave as an example mean 3am or 3 pm?

3) or is the 24 clock used all the way through?

What I'm trying to work out is what data is exactly in the fields so I can try to get it converted successfully

Col

 

[Bat17]

<"24:00" should work if the field is text. ignore this bit
If you want to turn your text time into a number of hours you could use functions
Left([Dry Time],InStr([Dry Time],":")-1)+(Mid([Dry Time],InStr([Dry Time],":")+1)/60)

HTH

Peter

 

[nicola1]

The time and dates are in the same unbound field! 3:00 means that the product was dry for 3 hours. The problem i am having is when i put the criteria as <24:00...it does not work

 

[Bat17]

if they are in the same unbound field then it is being calculated! can you post the calculation

Peter

 

[nicola1]

=Int(CSng(([txtDateDel]-[txtdateRO])*24)) & ":" & Format(([txtDateDel]-[txtdateRO]),"nn")

 

[Bat17]

just create a new column
=[txtDateDel]-[txtdateRO]
and set its criteria to <1

Peter

 

[nicola1]

Thank you both for your help

 

[Bat17]

Glad you have it sorted

Peter