Re: Populating report from code

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Aino wrote:

I am grateful that you are taking the time to try to solve this.

I think a query along these lines will return the cars that
are available on the specified day and time. (The subquery
finds cars that are busy and the Left Join and Is Null
criteria excludes them.)
...

That was the same I was going for with my strCriteria in the code:
strCriteria = "[Day] = '" & strDay & "' AND NOT [Car] IN " _
& "(SELECT [Car] FROM [qryTimes] WHERE [Day] = '" & strDay _
& "' AND [TimeMax] > #" & datFrom & "# AND [TimeMin] < #" &
datTo & "#)"
I am not sure though, why you have the last "And X.TimeMax < Forms!
theform.txtStart".

My thought was to also eliminate any records where the
MaxTime was before the start time since they seem like they
are irrelevant to the question.


Then, I think another query can then find the data for the
previous and next route:

SELECT Day, Car, TimeMax, AdrMax, TimeMin, AdrMin
FROM GetCars As G
WHERE (TimeMax = (SELECT Max(X.TimeMax)
FROM GetCars As X
WHERE X.Car = G.Cars)
Or TimeMax Is Null)
And (TimeMin = (SELECT Min(X.TimeMin)
FROM GetCars As X
WHERE X.Car = G.Cars)
Or TimeMin Is Null)

I am sorry to say, it doesn't work. I do not get all the cars
available. TimeMax and TimeMin are never null in GetCars.

You're right, there won't be any Null times, so you can get
rid of those Or expressions. Like I said Access went south
before I could test all that stuff so I never got a chance
to test it. The idea I was working toward is to eliminate
the entries with availablity that does not include the time
requested.

And the
times (and adresses) I want returned, are not neccessarily max and min
for the given car on the given day. If for instance the car drives 5
routes that day, and is available in the requested time interval
between route 2 and 3, I would like returned TimeMax and AdrMax for
route 2 (so I know when and where it is available from) and TimeMin
and AdrMin for route 3 (so I know when it is no longer available, and
where it needs to be at that time).

Right. That was the intent of the
And X.TimeMax < Forms!theform.txtStart
criteria in the first query.

...so I will leave it to you to test
this idea and decide if you want to pursue it or go back to
using code to calculate the data (which may be even more
complicated).

Actually the code in my original post gave the correct results, only
since they came out as variables, I could only present them in the
immediate window or a message box.

I am beginning to think that if I can get the results into a none-
editable form by creating an adhoc recordset, maybe I should use this
instead of a report. After all, I can give the form a white background
and set all fields to locked, maybe I can make the user think it is a
report?

If you have the desired recordset, then assigning it to a
form's Recordset property might be all you need.

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • Re: Populating report from code
    ... finds cars that are busy and the Left Join and Is Null ... previous and next route: ... WHERE (TimeMax = (SELECT Max(X.TimeMax) ... And (TimeMin = (SELECT Min(X.TimeMin) ...
    (microsoft.public.access.reports)
  • Re: ID cards
    ... They'd wait for the bus. ... are unable to walk only because they have become dependent on cars. ... majority of Americans walking a mile is something out of the ordinary. ... the best route out to get me back pointed home. ...
    (rec.arts.sf.fandom)
  • Re: Parking Ticket
    ... cars around? ... I was circling looking for a place to park. ... He must have had a strange route then. ... You have already told us that there are parking restrictions in this street. ...
    (uk.legal)
  • Re: Parking Ticket
    ... cars around? ... I was circling looking for a place to park. ... He must have had a strange route then. ... You have already told us that there are parking restrictions in this ...
    (uk.legal)
  • Re: Virgin drop plans for Shrewsbury
    ... have one route to London and one route only because we say so" is the ... If railways are going to compete with coaches and cars they should ... distance rail services. ...
    (uk.railway)