Solved

All hours represented within a time range - Revisited

Posted on 2014-10-22
27
200 Views
Last Modified: 2014-10-31
Experts Exchange  is the only resource I have found that has a solution to my problem in it;'s archive of several years ago.

I am developing a DB that tracks fleet vehicle maintenance.  The vehcile will be placed OOS(Out Of Serverce) fro the duration of their maintenance.  Data entry will be In Date/times (InDT) and Out Date/Time (OutDT) I need to break a time line into 1 hour blocks and count the status odf all vehehicle in the 1 hour block.

Grayl has already solved the issue from a previous thread several years ago for another OP.  I trying to fllow his solution and have changed code, but I am not getting any output, though no errors.  I'm sure it is something very basic like inputting a date field, but I've tried in several places and have no joy.

Link To Grayl's solution is here:
http://www.experts-exchange.com/Database/MS_Access/Q_21922016.html#accepted-solution

My adaptation of Grayl's code:

TRANSFORM Count(qryCmbDate.Status) AS CountOfStatus
SELECT TimeSerial([tbl_Hours].[HrFrom],0,0) AS TimeOfDay
FROM tbl_Hours, qryCmbDate
WHERE (((DateValue([InDT])+TimeSerial(tbl_Hours.HrFrom,0,0)) Between DateAdd("n",-Minute([InDT]),[InDT]) And DateAdd("n",-Minute([OutDT]),[OutDT])) And ((qryCmbDate.OutDT) Is Not Null))
GROUP BY TimeSerial([tbl_Hours].[HrFrom],0,0)
PIVOT Format(InDT,"dd-mm-yy");

Attached is my sample query in TEST3_Exchange
In my research I have not come across anything like what Grayl has written, so it is quite a learning curve.

The Anticipated result is the following:

Date      Hour      Count
5/10/2014      9:00      1
5/10/2014      10:00      1
5/10/2014      11:00      1
5/10/2014      12:00      2
5/10/2014      13:00      2
5/10/2014      14:00      2
5/10/2014      15:00      3
5/10/2014      16:00      3
5/10/2014      17:00      3


Attached is the DB.  I hope you can help with a bit of a deconstruc of the code.  Am I able to contact Grayl and alert him to this problem that he would be familiar with?
LOCO-DB-Exchange.zip
0
Comment
Question by:Johnni I
  • 13
  • 11
  • 3
27 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40396645
I think Ray has left the building.

However, it seems as you have time values in your hour table, thus TimeSerial messes this up.

If you adjust to:

TRANSFORM
    Count(qryCmbDate.Status) AS CountOfStatus
SELECT
    tbl_Hours.HrFrom AS TimeOfDay
FROM
    tbl_Hours,
    qryCmbDate
WHERE
    (((DateValue([InDT])+[tbl_Hours].[HrFrom])
        Between DateAdd("n",-Minute([InDT]),[InDT]) And DateAdd("n",-Minute([OutDT]),[OutDT]))
    AND
    ((qryCmbDate.OutDT) Is Not Null))
GROUP BY
    tbl_Hours.HrFrom
PIVOT
    Format(InDT,"dd-mm-yy");

you receive this output from your sample data:

TimeOfDay      05-10-14      06-10-14
9      1      1
10      1      1
11      1      2
12      2      2
13      2      2
14      2      2
15      3      2
16      3      2
17      3      2
18      4      2
19      4      2
20      4      2
21      4      2
22      4      2
23      4      2

/gustav
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40396652
I created a table (tbl_Numbers) and from that created two queries (qry_Days and qry_Hours)  I then combined these with your query to come up with the results.  In this particular case, you really did not need a cross-tab query, an Count on the day and hour was all you needed, but you needed to add add a parameter to add in the date value as well.

I create two queries (qry_OutOfService_Count) which lists the count for all days and hours, and qry_OutOfService_Count_on_Day, which is a parameter query and asks you for the day.

Hope this helps.
LOCO-DB-Exchange.zip
0
 

Author Comment

by:Johnni I
ID: 40396864
Awesome.  I didn't expext a reply so quickly.  I was about to go to bed when I noticed a mail notification.

It is midnight here and I will go through both response with a clearer head tomorrow.

Thank you again

Cheers
0
 

Author Comment

by:Johnni I
ID: 40401920
I've tested both versions and the different approaches handle the data output differently.  I changed  the in time for the first record to 09:05 and end time to 11:00 which creates no records at 11:00 & 12:00 slots

Dale's solution is the closest to what I was after that covers all the ranges, but there are a few problems that I can't resolve myself
- the times are not rounded to the hour.  
I tested with a start time of 09:05, and that became a new number starting at 09:05 incremented every hour from hh:05 with a single count for thatrange only.
-If there are no records at 11:00 & 12:00, Then, those times are omitted rather than show the times with zero record count.

I intend use this information in a calandar type format that all date and time ranges will need to be shown.

Originally I was after count of status, although Count of  OOS_ID is good to get a total count. I have created 2  extra colums for Status Count.  For this excercise it will be Status count value "X" and Status Count value "Y".  I've tried a few things, but I can't seem to get the tally of the 2 field values for each date and time.

I have been trying work things out for myself and that is why I have taken so long to respond.  The closest resource I have found is the Cartesian Product effect, but still not clear how the the table or queries Day hours created work .  I have looked at the queries you created, and still working it out without clearer understanding. Any explanation would be appreciated so we can all learn.

Gustavs Code does round times to the hour, and the blank record spaces have date and time with blank count field.

I have attached the DB and Dale's query_OutOService_Count and also Gustav's code qry Exchange3

Dale's Code with my additional fields

SELECT DateValue(DateAdd("d",[intday],DateAdd("h",[intHour],[InDt]))) AS [Date], TimeValue(DateAdd("d",[intday],DateAdd("h",[intHour],[InDt]))) AS [Time], Count(qryCmbDate.OOS_ID) AS CountOfOOS_ID, Count(qryCmbDate.Status) AS CountOf_X, Count(qryCmbDate.Status) AS [Count)f_Y]
FROM qry_Days, qry_Hours, qryCmbDate
WHERE (((DateAdd("d",[intday],DateAdd("h",[intHour],[InDt])))>=[InDT] And (DateAdd("d",[intday],DateAdd("h",[intHour],[InDt])))<[OutDT]))
GROUP BY DateValue(DateAdd("d",[intday],DateAdd("h",[intHour],[InDt]))), TimeValue(DateAdd("d",[intday],DateAdd("h",[intHour],[InDt])));

Open in new window

LOCO-DB-Exchange4.zip
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40401979
to get the range of dates and hours between two dates you would use something
like:
PARAMETERS [StartDay] DateTime, [EndDay] Date/Time;
SELECT DateValue(DateAdd("d",[intDay],[StartDay])) AS DateRange, qry_Hours.intHour AS HourStart, DateAdd("d",[intDay],[StartDay])+TimeSerial([intHour],0,0) AS DTHR
FROM qry_Days, qry_Hours
ORDER BY DateAdd("d",[intDay],[StartDay])+TimeSerial([intHour],0,0);
WHERE DateAdd("d",[intDay],[StartDay])+TimeSerial([intHour],0,0) < DateAdd("d", 1, [EndDay])

Open in new window


This would give you all of the days and hour start times between [StartDay] and [EndDay].  You could then use this as a subquery in a broader query which returns your counts by status based on when the car goes into service.  If the car comes out of service after the Date/Hour combination starts or goes into service any time within that hour, it will be counted.
PARAMETERS [StartDay] DateTime, [EndDay] DateTime;
TRANSFORM Count(qryCmbDate.OOS_ID) AS CountOfOOS_ID
SELECT T.DateRange, T.HourStart
FROM (SELECT DateValue(DateAdd("d",[intDay],[StartDay])) AS DateRange, qry_Hours.intHour AS HourStart, DateAdd("d",[intDay],[StartDay])+TimeSerial([intHour],0,0) AS DTHR
FROM qry_Days, qry_Hours
WHERE DateAdd("d",[intDay],[StartDay])+TimeSerial([intHour],0,0) < DateAdd("d", 1, [EndDay])
ORDER BY DateAdd("d",[intDay],[StartDay])+TimeSerial([intHour],0,0)
)  AS T, qryCmbDate
WHERE (((T.DTHR)<[OUTDT]) AND ((DateAdd("h",1,[DTHR]))>=[InDT]))
GROUP BY T.DateRange, T.HourStart, T.DTHR, DateAdd("h",1,[DTHR])
PIVOT qryCmbDate.Status;

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40401999
I ran my query with your revised test data, and it returns:

TimeOfDay      05-10-14      06-10-14
09:00      1      1
10:00      1      1
11:00      1      2
12:00            2
13:00      1      2
14:00      1      2
15:00      2      2
16:00      2      2
17:00      2      2
18:00      3      2
19:00      3      2
20:00      3      2
21:00      3      2
22:00      3      2
23:00      3      2

All hours are present, and the hour count is rounded, so what is missing?

/gustav
0
 

Author Comment

by:Johnni I
ID: 40402249
Thank you again everyone.

Dale,  I have started to go through and try out what you sent.  I'm getting errors on the first code, removed "/" between datetime, in parameters, but still syntax error.
The second code sets the in time as <start time for some reason.  so instead of 9am, it starts on 8am?
Will have a look tomorrow.   It's 1am here and things are getting blurry.

Hey Gustav,
The thing that is missing is 00:00 to 09:00 status count on 6/10/14.  The service is on a 24 clock.  
The dates are up to 12/10/06 and you show only 2.
The counts of status do not add up either.  
I don't know enough to change things ( Itried, but nothing worked for me), and I do appreciate your input as it gives me a different approach.
and another resource.  
tbl_Hours was created to try to replicate GrayL's solution.  I have no idea if it is correct.
a snippet for out of service Count:

Date                    Time            count
6/10/2014      3:00               3            
6/10/2014      4:00       3            
6/10/2014      5:00       3            
6/10/2014      6:00       3            
6/10/2014      7:00       3      
6/10/2014       8:00         3      
6/10/2014      9:00              4      
6/10/2014      10:00      4      
6/10/2014      11:00      5      
6/10/2014      12:00      5      
6/10/2014      13:00      5      
6/10/2014      14:00      5      
6/10/2014      15:00      5      
6/10/2014      16:00      5      
6/10/2014       17:00      5      
6/10/2014      18:00      5      
6/10/2014      19:00      5      
etc............

Cheers
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40402281
I don't know what "/" between data time parameters you are talking about.  There are no "/" in the code I sent you just now.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40402318
I see. Then Dale's solution will work.
It works here and neither do I see any "/", indeed not in code, as no code module exists.

/gustav
0
 

Author Comment

by:Johnni I
ID: 40404391
Finally understand whats is going on. I didn't realise that you had already put a query inside the query.  I haven't quite got the hang of crosstab queries, so am deconstructing what you have written.

I need to include dateranges/time where there are no records. I am missing 11:00 on 05/10/14, where There are no records. I changed the TRANSFORM statement to

TRANSFORM Nz(Count(qryCmbDate.OOS_ID),0) AS CountOfOOS_ID

Open in new window


But this only gave me "0" where the status count was Null, but not sure how to include for all date/times.

What needs to happen is that the initial parameters need to enter a start date and start  and then a time span from that date in days/24hr periods.  In the final model, I would like the start date/time to be controlled by the computer system clock showing a 24 hour period and changing view every hour as a default.

The InTimes are definitely an hour earlier. The first record is 09:00 and the status counts are from 08:00.  I  change the time to 09:05, then all is Ok. It needs o be => InTime which it seems is what is in the WHERE statement. I think I am missing something

The OutTime is working fine. If the OutTime is exactly say 11:00, then it would be the hour before that would be counted.  If the OutTime is >11:00, then it would fall into the11:00 hour period which is what it is doing.

And finally, I tried to add another column as a calculated field, like the total count of OOS_ID for that Hour.  I don't understand why something that seems so easy is giving me pain.  In the Select query I hadno issue, but in crosstab I need to change the way I think about the data flow.

I have spent considerable time trying to implement these changes, but everything I try causes an error or doesn't give me any results.  I hope you can show me how to achieve these results

Cheers
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40404426
OK, The problem with the previous query was that I used a WHERE Clause which filtered out some of the date/time combinations.  This new query is a bit more complicated to understand, because of your intention to rerun the query every hour.  Here is the entire query.
PARAMETERS [StartDayTime] DateTime;
TRANSFORM Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>=[InDT],1,0)) AS Expr1
SELECT T.OOSDate, T.OOSHour
FROM (
SELECT DateValue([DTHR]) AS OOSDate
, Hour([DTHR]) AS OOSHour
, DateValue([StartDayTime])+[intDay]+ [intHour]/24 AS DTHR
FROM qry_Days, qry_Hours
WHERE (DateValue([StartDayTime])+[intDay] + [intHour]/24) >=DateValue([StartDayTime]) + Hour([StartDayTime])/24
AND (DateValue([StartDayTime])+[intDay]+ [intHour]/24) <=DateValue([StartDayTime]) + 1 + Hour([StartDayTime])/24
ORDER BY DateValue([StartDayTime])+[intDay]+ [intHour]/24) AS T, qryCmbDate
GROUP BY T.OOSDate, T.OOSHour, T.DTHR
PIVOT qryCmbDate.Status;

Open in new window

To explain, lets start with the subquery, which generates the day/time combinations starting with the date and hour of the time you enter, and continuing for the next 24 hourly values, to give you a total of 25 records.  The core of the subquery is the calculation of the each hourly increment of the day and time.  This is done by taking the DateValue() of the date/time passed into the query and incrementing that by every value of [intDay] and intHour.  The WHERE clause ensures that those combinations are >= the Day/hour passed (if you pass in 11:15, it will start at 11:00) and <= the time 24 hours from that value.  If you only want 24 hourly values change the <= to <.
SELECT DateValue([DTHR]) AS OOSDate
, Hour([DTHR]) AS OOSHour
, DateValue([StartDayTime])+[intDay]+ [intHour]/24 AS DTHR
FROM qry_Days, qry_Hours
WHERE (DateValue([StartDayTime])+[intDay] + [intHour]/24) >=DateValue([StartDayTime]) + Hour([StartDayTime])/24
AND (DateValue([StartDayTime])+[intDay]+ [intHour]/24) <=DateValue([StartDayTime]) + 1 + Hour([StartDayTime])/24
ORDER BY DateValue([StartDayTime])+[intDay]+ [intHour]/24

Open in new window


The second major difference between this query and the one I sent you yesterday is that that query used a WHERE clause in the outer query, which is what caused the skipped hours.  This query replaces that WHERE clause with an IIF( ) statement in the TRANSFORM column.  This IIF( ) statement returns a 1 if the particular car was out of service at any time during the hour, or a zero if not.  The TRANSFORM operation sums those values rather than counting them to get the actual number of cars out-of-service during any hourly period.

The other major difference with these queries is that in yesterdays version, if no cars were out of service for a particular Status code, that status would not show up in the query results.  With this query, all of the status codes present in qryCmbDate will show up in the resulting query.

I've configured this version as a parameter query, which allows you to enter a date/time value for testing purposes, but if you are going to automate this update process, I would delete the parameter part of the query and replace all of the references to [StartDayTime] with the Now() function call.  

If I were going to do what you have described above, I would probably configure the form that is going to display these results with a TimerInterval set to 60000 (1 minute).  In the Timer event, I would declare a static variable that keeps track of the last time the query was updated, and would then check to see whether the Hour() of that time = the current hour.  If it is, then I would exit the event.  If it is not, I would requery the form (or more likely a subform) and then reset this static variable.  That might look like:
Private Sub Form_Timer

    Dim dtLastUpdated as date

    if Hour(dtLastUpdate) = Hour(Now()) Then Exit Sub

    dtLastUpdated = Now()
    me.subformName.form.Requery

End Sub

Open in new window

Adding another computed column to the query above can get convoluted.  If you can explain what you are trying to add to these results, maybe I can point you in the right direction.
0
 

Author Comment

by:Johnni I
ID: 40404466
Thanks for this lastest version.  

I have done a quick test and everything is as you have explained. There is still the issue with the first record of the InTime of  09:00 being counted in the 08:00 hour slot which is the same for all InTime records being in the slot before the InTime.

The extra calculated field I am after is the sum of OOS for the hour (or sum of Status).  Again I tried a few things and cannot extract this information without upsetting things.  I don't undersatnd why there is a problem if you are calculating this field in the Transform statement anyway.  A condition would be attached to the sum total that when it reaches  => a certain tally, it turns red and every one panicks.  This is part of an overall score board type of display also, as a reference of what is happening on the ground.

I'll keep all the timer information close by for when I get to the next phase.

I am especially grateful that you have taken the time to explain the processes and reasoning behind everything.  Because of this excercise I have a greater understanding when I look at SQL statements now and realise I have a long way to go to reach your proficiency.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40404760
I'm still not certain I understand what you want this other calculated field to provide.  Can you create an Excel spreadsheet and, using the data you provided, give me an example of what you are looking for?

Frequently, if you want more columns to show up in a query that includes a crosstab, you must generate the other data in another query, then join the two queries in a third.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Johnni I
ID: 40404809
Being able to break the dates and time into hour intervals was my main mission. This is being achieved so I know the data is available in this format. This is a precusor to a timeline approach that is my next phase.


[embed=file 879I296]
Capture.JPG
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40404827
I simply added another computed column, and used the same IIF() statement that I used for the individual Status columns, but set the "Crosstab" value of this new computed field to Row Heading.

PARAMETERS [StartDayTime] DateTime;
TRANSFORM Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>=[InDT],1,0)) AS Expr1
SELECT T.OOSDate, T.OOSHour, Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>=[InDT],1,0)) AS Total
FROM (SELECT DateValue([DTHR]) AS OOSDate, Hour([DTHR]) AS OOSHour, DateValue([StartDayTime])+[intDay]+[intHour]/24 AS DTHR FROM qry_Days, qry_Hours WHERE (DateValue([StartDayTime])+[intDay]+[intHour]/24)>=DateValue([StartDayTime])+Hour([StartDayTime])/24 And (DateValue([StartDayTime])+[intDay]+[intHour]/24)<=DateValue([StartDayTime])+1+Hour([StartDayTime])/24 ORDER BY DateValue([StartDayTime])+[intDay]+[intHour]/24)  AS T, qryCmbDate
GROUP BY T.OOSDate, T.OOSHour, T.DTHR
PIVOT qryCmbDate.Status;

Open in new window

If you use this as the RecordSource for a form, you could set the conditional formatting of the Total control so that it is highlighted as appropriate.
0
 

Author Comment

by:Johnni I
ID: 40404913
Pure GOLD!  
I copied, added  and embellished your computed column and now I have a number count for InService as well.

10-Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>=[InDT],1,0)) AS Total_InService

Open in new window


I've looked at all the => and =<  statements and can't figure out why the InTimes counts occur the hour before the Intimes.

It's 2am.  will have a look again tomorrow to see how you are making this work

Cheers
0
 

Author Comment

by:Johnni I
ID: 40409976
I managed to make the changes to get the InTime to start counting from the right place. In the TRANSFORM and SELECT statements I changed the >= to > and all works even when I change the InTime to 9:05. All my counts are correct with my sample data.  

I have been deconstructing everything so that I understand the process.  I have been cross referencing with my google searches and although I have expanded my knowledge, I can't say I could replicate by myself- yet.

I can safely say that the problem has been solved and exceeded my expectations.

Thank you Dale for your patience and time in assisting to resolve my issue and open a new frontier in SQL for me that I never really knew existed.

And the solution that works for me.

PARAMETERS [StartDayTime] DateTime;
TRANSFORM Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Expr1
SELECT T.OOSDate, T.OOSHour, Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Total, 10-Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Total_InService
FROM (SELECT DateValue([DTHR]) AS OOSDate, Hour([DTHR]) AS OOSHour, DateValue([StartDayTime])+[intDay]+[intHour]/24 AS DTHR FROM qry_Days, qry_Hours WHERE (DateValue([StartDayTime])+[intDay]+[intHour]/24)>=DateValue([StartDayTime])+Hour([StartDayTime])/24 And (DateValue([StartDayTime])+[intDay]+[intHour]/24)<=DateValue([StartDayTime])+1+Hour([StartDayTime])/24 ORDER BY DateValue([StartDayTime])+[intDay]+[intHour]/24)  AS T, qryCmbDate
GROUP BY T.OOSDate, T.OOSHour, T.DTHR
PIVOT qryCmbDate.Status;

Open in new window


There is still endless fun to be had and insights to be gained as I still try to understand it all.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40410397
Glad I could help.

Sometimes, on the forums, responders will simply post an answer, which for some people is all they are looking for.  But if you don't understand the answer and want to learn, post back and you will generally get an explanation.

Yes, there is a lot to SQL.  And even when it works, there might be a more efficient or quicker way, so don't hesitate to ask.  There is a great SQL reference, "SQL for Mere Mortals" that provides a lot of examples of the types of problems you will encounter when dealing with referential data.
0
 

Author Comment

by:Johnni I
ID: 40410713
I have a huge problem.  The Parameter Query is driving me nuts.  How can I input a date for testing so I don't have to enter it ever time (i've already gone through enough of it).  I just want to have #05/10/14 07:00# as its default [StartDayTime] for now.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40411139
easiest way it to probably create a tempVar.

TempVars.Add "StartDayTime", #05/10/14 07:00#

Then remove the Parameter line from the query, and replace all references to [StartDayTime] with [Tempvars]![StartDayTime]

A TempVar is similar to a global variable, but instead is part of a collection that stays resident even when your "application" is just in design view.  You set it once (you can do this in code or in the immediate window) and can then use it in code, queries or anywhere you would refer to a global variable.  The good news is that:
1.  you can refer to it in a query
2.  it doesn't lose its value when you encounter an unhandled error

Then, when you actually run this in your code, you would simply set the TempVars value (I think I discussed putting a static variable in your forms Timer event) instead of using the static variable.
0
 

Author Comment

by:Johnni I
ID: 40412435
Not Having joy at trying to set the Tempvars.  I have changed all reference from[StartDateTime] to [Tempvars]![StartDayTime] in main sql statement.

I open vba editor and in immediate window I got desired out come
TempVars.Add StartDayTime, #5/10/2014 7:00#

?[Tempvars]![StartDayTime]
10/05/2014 7:00:00 AM 

Open in new window


Tried creating a module with the Tempvars.add and has errors.

Is there anything else that I need to do to get it to work?

TRANSFORM Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Expr1
SELECT T.OOSDate, T.OOSHour, Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Total_OOS, 10-Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Total_InService
FROM (SELECT DateValue([DTHR]) AS OOSDate, Hour([DTHR]) AS OOSHour, DateValue([Tempvars]![StartDayTime])+[intDay]+[intHour]/24 AS DTHR FROM qry_Days, qry_Hours WHERE (DateValue([Tempvars]![StartDayTime])+[intDay]+[intHour]/24)>=DateValue([Tempvars]![StartDayTime])+Hour([StartDayTime])/24 And (DateValue([Tempvars]![StartDayTime])+[intDay]+[intHour]/24)<=DateValue([Tempvars]![StartDayTime])+1+Hour([Tempvars]![StartDayTime])/24 ORDER BY DateValue([Tempvars]![StartDayTime])+[intDay]+[intHour]/24)  AS T, qryCmbDate
GROUP BY T.OOSDate, T.OOSHour, T.DTHR
PIVOT qryCmbDate.Status;

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40412706
"Tried creating a module with the Tempvars.add and has errors. "

Can you post the code for that module?  Another way to set the tempvars value is:

Tempvars![StartDayTime] = #10/5/14 07:00:00#

but if you are going to do this using a control on a form you must actually use the Value property of that control, something like:

Tempvars![StartDayTime] = me.txt_StartDayTime.Value

I also found a reference to [StartDayTime] in row 3 of your SQL above that does not include the TempVars! part.  It currently reads:

DateValue([Tempvars]![StartDayTime])+Hour([StartDayTime])/24

but should read

DateValue([Tempvars]![StartDayTime])+Hour([Tempvars]![StartDayTime])/24
0
 

Author Comment

by:Johnni I
ID: 40412990
I have corrected the 3rd row. From what I understand, TempVars do not need to be declared.  I've spent most of the day researching this and am more confused than enlightened.  Tempvars need to be assigned and then they create their magic of going into some list in the ether of the DB and gets drawn upon and that is why there is a method of removing it also.  

There are other sites simply saying that place the statement in a  WHERE [StartDate] = [tempvar]![startDate] statement within the sql.. I also tried this, don't work.  There is some Reference  On This Site.   Other sites say that tempvars don't work in xtab queries.

What I placed in the Module was just the TempVars.Add code.  It was affecting the error codes, but namely that the [Tempvars]![StartDayTime] is not recognised as a valid field.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40413324
When you refer to the Tempvar in your code, use one of the following.  You don't technically need the brackets in the example below unless you have used a space in the name of the tempvar, which I never do.

Tempvars![StartDayTime]
Tempvars("StartDayTime")

You only need to wrap the Tempvars piece in brackets when using it in a query:
[Tempvars]![varName]
But now that you mention it, your point about using the tempvars in a cross-tab may be why I wrote the following function:
Public Function fnTempvars(VarName as string) as Variant

    fnTempvars = TempVars(VarName)

End Function

Open in new window

Then replace the [Tempvars]![StartDayTime] references in the query with:

fnTempvars("StartDayTime")
0
 

Author Comment

by:Johnni I
ID: 40414984
I have finally got it working!!!!

I put your FnTempvars code into a module and changed all relevant statements  to  fnTempvars("StartDayTime").  Result was  Error -data mismatch

Much researh and trying everything that I could find.  I used the Macro function to add and remove the TempVars to ensure I had the right input value and result. The immediate window confirmed the various methods of writing the statement to give me the result.

It finally dawned on me that the Datevalue function accepts a string value and my input is always day time without # tags into the parameter window.  Worth a try. Changed the StartDayTime values in Tempvars.Add without # and presto!  All is working

?Tempvars("StartDayTime")
5/10/2014 7:00:00 AM 

?([tempvars]![StartDayTime])
5/10/2014 7:00:00 AM 

?fnTempVars ("StartDayTime")
5/10/2014 7:00:00 AM 

Open in new window


TRANSFORM Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Expr1
SELECT T.OOSDate, T.OOSHour, Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Total_OOS, 10-Sum(IIf([DTHR]<[OutDT] And DateAdd("h",1,[DTHR])>[InDT],1,0)) AS Total_InService
FROM (SELECT DateValue([DTHR]) AS OOSDate, Hour([DTHR]) AS OOSHour, DateValue(fnTempvars("StartDayTime"))+[intDay]+[intHour]/24 AS DTHR FROM qry_Days, qry_Hours WHERE 
(DateValue(fnTempvars("StartDayTime"))+[intDay]+[intHour]/24)>=DateValue(fnTempvars("StartDayTime"))+Hour(fnTempvars("StartDayTime"))/24 And (DateValue(fnTempvars("StartDayTime"))+[intDay]+[intHour]/24)<=DateValue(fnTempvars("StartDayTime"))+1+Hour(fnTempvars("StartDayTime"))/24 ORDER BY DateValue(fnTempvars("StartDayTime"))+[intDay]+[intHour]/24)  AS T, qryCmbDate
GROUP BY T.OOSDate, T.OOSHour, T.DTHR
PIVOT qryCmbDate.Status;

Open in new window


I tried running another version by removing the  "Fn " in the clause(?) and an error came up stating "Undefined Function 'Tempvars' in Expression" .   I think that may have been the reason you wrote that function.  So........what does that magic code actually do?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40415370
Access allows queries to call functions and use the results either as the values of fields in the query results, or as criteria to restrict the rows returned.

All that function does is return the value of the tempVar.  As you indicated, for some reason the crosstab query doesn't like the reference to [tempvars]![varName], so I simply created a function that allows me to pass the variable name and return the associated value.

I'm surprised about the need to change the data type of the tempvar, but I'm glad you finally got that working.

Your excursion into tempvars will be well worth it.  Although I have not gone back and rewritten all my old applications, I have stopped using global variables altogether and now use tempvars whenever I have a need to use a variable in multiple locations within my applications.
0
 

Author Comment

by:Johnni I
ID: 40415399
I have completed my test of inserting  the crosstab query into msflexgrid in a form.  It works without having to do any form manipulation.  

 I'll just continue this journey and see if I can create what is in my head.

Thanks again Dale.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now