wrong data in field

[pops49]

I have a query from a table with "hour" for one of the fields. The problem that I am having is the "hour' field is in military time. 0=12 midnight and so on. what I would like to do is make "0"=8am. Is this possible.

Thanking you in advance.
Pops49

 

[dhookom]

Is this an actual Date/Time data field? Or is it numeric? How about providing about 5-10 records and what you would like to "make".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pops49]

This is a date/time data field coming from a plc, is it possible to change it in a query? When I give it multiple dates it works fine, but when I give it a range for times (24hrs)(8am-8am) instead of midnight to midnight it will not work. I will try to send you an example on 1/18/07

Thanks
pops

 

[dhookom]

Adding .333333 to 0 will approximately equal 8:00 AM. You can also use DateAdd("h",8,[YourField])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pops49]

Thanks dhookom for the last response, but it didnt work. I replied on the 18th, but I dont see it here. I'll try to send the records again. (cant copy small amount of data, it is taking the whole database) suggestions?

Thanks

 

[dhookom]

Just open a query with significant fields and a limited number of records. Copy and paste the records into a reply.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pops49]

dhookom,
This is some of the records, I made sure the times were included. 8am-8am is what I need.
Thanks
DateFld HourFld Sum Of FI_F6002
1/13/2007 0 10.97
1/13/2007 1 12.12
1/13/2007 2 13.97
1/13/2007 3 15.74
1/13/2007 4 15.94
1/13/2007 5 16.19
1/13/2007 6 17.68
1/13/2007 7 20.84
1/13/2007 8 21.27
1/13/2007 9 20.86
1/13/2007 10 29.52
1/13/2007 11 27.40
1/13/2007 12 28.11
1/13/2007 13 27.07
1/13/2007 14 26.32
1/13/2007 15 26.06
1/13/2007 16 27.29
1/13/2007 17 27.36
1/13/2007 18 27.27
1/13/2007 19 27.46
1/13/2007 20 27.48
1/13/2007 21 27.52
1/13/2007 22 27.48
1/13/2007 23 27.49
1/14/2007 0 27.53
1/14/2007 1 27.50

 

[dhookom]

It would help if you would have added the "what you would like to make". If I understand correctly, you can use a calculation like:
TimeValue(DateAdd("h",8,[HourFld]/24))

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pops49]

dhookom
every morning at 8am I must read a sum of the last 24hrs. The records that I sent were snapshots of the hourly data. Whatever the total is for 8am -8am will be divided by 24.
I hope this helps.
Thanking you In Advance.
pops

 

[pops49]

dhookom
I am still having problems,where do I insert the formula that you suggested. "timevalue(dateadd("h",8,[hourfld]/24)?

 

[dhookom]

You can create a query based on your query above like:

SELECT DateFld, HourFld, DateValue(DateAdd("h",-8,[dateFld]+[HourFld]/24)) AS ReadingDate, SumOfFI_F6002
FROM qselPops49;

This will subtract 8 hours from a complete date+time. You can group by the ReadingDate field and sum the other field.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pops49]

Thanks dhookom it works!

 

[pops49]

dhookom,

I am trying to sum values coming from a plc on a hourly basis. I would like a running sum of the hourly data e.g. after hr #1 + hr #2 + hr.... I can put this on a form and at any given moment the total sum of the hourly data can be seen.

Thanking you in Advance.

 

[dhookom]

Reports easily provide running sums. Forms would require you to create the value in the record source query. This could be done with a subquery.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pops49]

Thanks dhookom, I am going to try to do it in the form version.

pops49