Form->Report->Query

[spoedniek]

Hi,

I've been plodding my way through designing and implementing a database
that I need to organise my work, and I've been more successful than I
thought I'd be. That's the good bit. Now, however, I am completely
flumoxed (if that's the correct spelling), and I have been for a couple
of days now. This is the scene (bear in mind that this is my first
proper exposure to Access and databases in general):

I have a form with a button that calls up a report linked to a fairly
nasty looking query. The idea is that the button calls up an Invoice
(the report) which has to gather all the necessary information from the
database using a query. The form works, the report works and the query
works. But they don't work together. I'm using Access2002.

The query looks like this (appologies for the length/size but I'm
posting it since I suspect it may be the query that's the problem):
SELECT
tProject.projID, First(tProject.title) AS Project,
First(tProject.clientRefNum) AS Reference, First(tDeliverable.name) AS
Deliverable, First(tProcess.name) AS Process, Avg(tProcess.rates) AS
Rates, Sum(DateDiff("n",tTimeStamp.timeStart,tTimeStamp.timeStop)) AS
Minutes,
Round(Sum(DateDiff("n",tTimeStamp.timeStart,tTimeStamp.timeStop))/60)
AS Hours,
(Round(Sum(DateDiff("n",tTimeStamp.timeStart,tTimeStamp.timeStop))/60))*First(tProcess.rates)
AS Cost, First(tClients.CompanyName) AS Company,
First(tClients.VatNumber) AS [VAT Number], First(tContacts.firstName) &
" " & First(tContacts.lastName) AS [Contact Name],
First(tAddresses.Address) AS Address, First(tAddresses.City) AS City,
First(tAddresses.PostalCode) AS [Postal Code],
First(tAddresses.Province) AS Province, First(tAddresses.Phone) AS
Phone, First(tAddresses.FaxNumber) AS Fax, Min(tTimeStamp.timeStart) AS
MinOftimeStart
FROM
(tContacts INNER JOIN ((tClients INNER JOIN (tAddresses INNER JOIN
txAddressClient ON tAddresses.AddressID=txAddressClient.addressID) ON
tClients.clientID=txAddressClient.clientID) INNER JOIN tProject ON
tClients.clientID=tProject.clientID) ON
tContacts.contactID=tProject.contactPrimary) INNER JOIN ((tDeliverable
INNER JOIN tProcess ON tDeliverable.delivID=tProcess.delivID) INNER
JOIN tTimeStamp ON tProcess.processID=tTimeStamp.procID) ON
tProject.projID=tDeliverable.projID
GROUP BY tProject.projID, tProcess.processID, tDeliverable.delivID;

The query works. When I add parameters for the period for which I want
to display the invoice, it still works and produces the desired
results. Here's the WHERE clause I added to test:

WHERE tTimeStamp.timeStart BETWEEN [Start of period:] AND [End of
period:]

The report (rInvoice) works perfectly (only quite perfectly, I fear)
with this query (with or without the WHERE clause).

In the form, using the query without the WHERE clause above, I call the
report with the following (what I presume to be standard) code:

Private Sub btnViewInvoice_Click()
On Error GoTo Err_btnViewInvoice_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rInvoice"
stLinkCriteria = "((tProject.projID)=" & Me![projID] & ") AND ((" &
_
"[tTimeStamp].[timeStart]) BETWEEN #" & _
Me![dateStart] & "# AND #" & Me![dateEnd] & "#)"

DoCmd.OpenReport stDocName, acPreview, "qViewInvoice",
stLinkCriteria
..
..
..

When I use a MsgBox to show stLinkCriteria it looks fine:
((tProject.projID)=9) AND (([tTimeStamp].[timeStart]) BETWEEN
#01/01/2005# AND #01/02/2005#)

BUT suddenly an input box appears out of the blue (background theme)
asking for:
tTimeStamp.timeStart
As if it's a parameter query. Why? Why is it suddenly looking for
tTimeStamp.timeStart when the query, separately, doesn't seem to be too
concerned about tTimeStamp.timeStart? Where am I going wrong?

If anyone can just give a hint, I'd much appreciate it.

Cheers,

Henry Holland
Grahamstown
South Africa

 

[L5hopes]

Looks like you're saying the query works with the WHERE clause when
tested outside the realm of the report, but not when the WHERE clause
is used as stLinkCriteria. If that's the case, the problem is only that
you didn't actually include the tTimeStamp.timeStart field as a field
in your query. And the record source of the report has only the fields
actually selected in the basic query (without the report's
stLinkCriteria). The criteria used in opening the report is actually
acting as a FILTER, not a query "WHERE" clause. I may be wrong about
that. But try adding the field itself to the SELECT portion of your
query and see if that fixes it.

 

[spoedniek]

Thanks for the reply.

I've been thinking along the same lines, except I did include
tTimeStamp.timeStart in the query. Only I had to include it as a
Min(tTimeStamp.timeStart) aggregate field because of the Group By
clause in the query (it is in there somewhere). That is also why I
tested it exhaustively (or more like exhaustingly) in the query itself.
Other fields in the query behave the same way (tClients.CompanyName) -
in a WHERE clause in the query itself there's no problem, but in the
stLinkCriteria the field isn't recognised.

Henry

 

[spoedniek]

Okay, I see now what you meant. Once the button is clicked, the query
is run and THEN filtered using the stLinkCriteria text. This is why I
get the parameter input box, 'cause there's no 'tTimeStamp.timeStart'
field in the query results (it's been aggregated).

So now I used the aggregated field MinOftimeStart (it's somewhere
hidden in the query above):

stLinkCriteria = "((tProject.projID)=" & Me![projID] & ") AND ((" &
_
"MinOftimeStart]) BETWEEN #" & _
Me![dateStart] & "# AND #" & Me![dateEnd] &
"#)"

No more parameter input box, but 'no data' ensues. Looking at the query
results there are definitely entries for dates between the
Me![dateStart] and Me![dateEnd]. Any other ideas?

 

[L5hopes]

Yes, but you called the resulting recordset column "MinOftimeStart", so
try that in your report criteria instead of "tTimeStamp.timeStart".

 

[L5hopes]

Sorry, cross-posted there.

Did you test the query with the WHERE clause using the "MinOfTimeStart"
column (before using as link criteria)?

 

[spoedniek]

Yes, I did, and it doesn't work. A parameter input box for
'MinOftimeStart' appears.

 

[L5hopes]

So, for report filter, MinOfTimeStart is the way to go. You said the
report opens with all records matching your initial query when the
filter (/where clause) is not added, yes?

Does tTimeStamp.timeStart data have actual dates or just times? Problem
could be there: in the difference between your form's Me![dateStart]
and Me![dateEnd] and your query's MinOfTimeStart. What does
MinOfTimeStart data look like?

 

[spoedniek]

I've gone through so many things now that I can't remember anymore what
worked how.

I also thought the date/time thing may be an issue, but they're both
full general format dates with time. So that's not it either.

Something interesting is that when I type the stLinkCriteria text (with
specific values for projID and dateStart/dateEnd) into the Filter
property of the Report, and Filter On property as Yes, I get exactly
what I want, if I use the alias for the aggregate field
(MinOftimeStart) - which is as expected for a filter after the query.

I think what I'll do now is create a simple database with a similar
query (joins and aggregates) and work it out that way.

Will let you know if I've worked it out. Thanks for the help.

 

[L5hopes]

Another useful debugging test is to debug.print the stLinkCriteria
string in your vb Immediate window, and see how it looks when you open
the report.

Good luck,

--eon--
Maine, USA

 

[spoedniek]

Thanks, yes, that's quite useful.

I think I've wittled the problem down to a date time format issue. And
this is actually a bit much for me at the moment.

One thing that's driving me mad is that there is a discrepancy between
the date format shown in the SQL window and that shown in the query
grid design window. For instance, the grid window will show dd/mm/yyyy
(generally how we show dates in South Africa), but then that value will
become mm/dd/yyyy in the SQL window. Which complicates things. Which
one does the internal (is that JET?) query engine use? How do I get
these differences resolved? Sometimes it works right (e.g. if I use the
query as a filter in the report, it works, but if i paste that filter
into the query as a where clause it doesn't). I'm so confused I don't
know where I stay or what my name is...

It's late, so I'll work on that again tomorrow. Thanks again for the
help. I think I'm almost there now.

Henry

 

[spoedniek]

I finally go it to work!

Not sure if I understand all the details, but what I've done is to use
the unambiguous vbLongDate as input to the filter/where clause in the
VBA code for the button. My stLinkCriteria therefore look something
like this:

"(tProject.projID=" & Me![projectID] & ") AND
(((tTimeStamp.timeStart)>=#" & FormatDateTime(Me![dateStart],
vbLongDate) & "#) AND ((tTimeStamp.timeStart)<=#" &
FormatDateTime(Me![dateEnd], vbLongDate) & "#))"

And the resultant stLinkCriteria for any particular period and project:
((tProject.projID)=9) AND (((tTimeStamp.timeStart)>=#01 January 2005#)
AND ((tTimeStamp.timeStart)<=#26 January 2005#))

So far so good.

Eon, thanks again for the help.

Cheers,
Henry

 

[L5hopes]

Glad you got where you wanted to go. Date formatting is always a bitch.
That's where I would have pointed you to next (after your May 19
posting), but you figured it out first. Now try some date math! Loads
of laughs. Then, there's extracting just parts of dates (datepart
function) to find things anywhere in the same month or year, for
instance. More fun than... smacking yourself with a brick.

Have a project I currently work on where I'm constantly testing whether
some date occurs during the same academic year as another date. You
just have to write custom functions for nasty stuff like that.

--eon--