access: correcting time formats

[Guest]

i have a table of data where the time interval is messed up. We are
downloading data from a meter and somewhere in the transfer, some of the 15
minute intervals get screwed up. we are querring data from this table using
the 15 minute military time intervals. the problem is that some times the
metered time data is showing ex. 00:14 instead of 00:15 or 00:46 instead of
00:45 which causes major problems in my other querries, basically leaving out
data. how can i set up a query to make the 00:14 show up under the time
interval 00:15 and so on.

 

[Guest]

Here is something that might work for you. Create a table named MIN-COR with
two fields.

Minutes Correction
00 00
01 00
02 00
13 15
14 15
15 15
16 15
17 15
22 15
24 30
28 30
29 30
30 30
31 30
32 30
43 45
44 45
45 45
46 45
47 45
58 00
59 00
Fill in for the full hour.

Use the SQL below in your query to correct minutes. It also corrects hour
for raw time before the hour (I only put in correction for 58 & 59).

SELECT [MIN-HR].[Hour-Min], IIf(Right([hour-min],2)="58" Or
Right([hour-min],2)="59",(Val(Left([Hour-Min],2)+1) & ":" &
IIf(Right([Hour-Min],2)=[minutes],[correction])),Left([Hour-Min],3) &
IIf(Right([Hour-Min],2)=[minutes],[correction])) AS [Corrected Time]
FROM [MIN-COR], [MIN-HR]
WHERE ((([MIN-COR].Minutes)=Right([Hour-Min],2)));

 

[John Vinson]

i have a table of data where the time interval is messed up. We are
downloading data from a meter and somewhere in the transfer, some of the 15
minute intervals get screwed up. we are querring data from this table using
the 15 minute military time intervals. the problem is that some times the
metered time data is showing ex. 00:14 instead of 00:15 or 00:46 instead of
00:45 which causes major problems in my other querries, basically leaving out
data. how can i set up a query to make the 00:14 show up under the time
interval 00:15 and so on.

A criterion of

BETWEEN #00:12# AND #00:18# would catch them; or, you could run an
Update query using a similar criterion to update all the "about
quappas" records to a quarter-past exactly.

John W. Vinson[MVP]

 

[Guest]

i guess i an just not that advanced, but i cannot figure out how to set that
statement up in a query. Do i put it in the Field column? do i need to show
the new table at the top and join it somehow to my table in question (All
info for excel without rain). i am sorry i am confused.

Here is something that might work for you. Create a table named MIN-COR with
two fields.

Minutes Correction
00 00
01 00
02 00
13 15
14 15
15 15
16 15
17 15
22 15
24 30
28 30
29 30
30 30
31 30
32 30
43 45
44 45
45 45
46 45
47 45
58 00
59 00
Fill in for the full hour.

Use the SQL below in your query to correct minutes. It also corrects hour
for raw time before the hour (I only put in correction for 58 & 59).

SELECT [MIN-HR].[Hour-Min], IIf(Right([hour-min],2)="58" Or
Right([hour-min],2)="59",(Val(Left([Hour-Min],2)+1) & ":" &
IIf(Right([Hour-Min],2)=[minutes],[correction])),Left([Hour-Min],3) &
IIf(Right([Hour-Min],2)=[minutes],[correction])) AS [Corrected Time]
FROM [MIN-COR], [MIN-HR]
WHERE ((([MIN-COR].Minutes)=Right([Hour-Min],2)));

i have a table of data where the time interval is messed up. We are
downloading data from a meter and somewhere in the transfer, some of the 15
minute intervals get screwed up. we are querring data from this table using
the 15 minute military time intervals. the problem is that some times the
metered time data is showing ex. 00:14 instead of 00:15 or 00:46 instead of
00:45 which causes major problems in my other querries, basically leaving out
data. how can i set up a query to make the 00:14 show up under the time
interval 00:15 and so on.