Question -- Or more appropriately -- HELP!!

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

Hi everyone,

I have a problem and a question (related to the problem).
I have a query which reads something like

Select tablename.stringfield as alias, max
(tablename.intfield)as anotheralias, etc... HAVING
(tablename.fieldname)='" & form criteria & "';
In the query builder this query runs as expected, but if
executed as a recordset in VBA, no records are returned.
Why???
In the query builder the '" & is left out of course.

Thanks!

Kevin
 
Hi,



First, do you have the group by statement?

It seem that you need a where clause that filter the input to the summery
query.



"Having" is the condition for the total.



Like: Give me the total of all salesmen HAVING a total bigger then 5
 
Kevin said:
Hi everyone,

I have a problem and a question (related to the problem).
I have a query which reads something like

Select tablename.stringfield as alias, max
(tablename.intfield)as anotheralias, etc... HAVING
(tablename.fieldname)='" & form criteria & "';
In the query builder this query runs as expected, but if
executed as a recordset in VBA, no records are returned.
Why???
In the query builder the '" & is left out of course.
Click to expand...

It would be easier if you posted the actual SQL so we could see if
there's anything fundamentally wrong with it, but the first question
that presents itself is, is the form that is referenced by the criterion
actually open at the time you open your recordset on the query, and does
the control (or controls) have a value for which records should be
returned?

The second question is, are you in fact using the exact same query in
both cases? It sounds as though you aren't, so you may have messed up
your SQL or the way in which you are embedding the criterion. Is the
field to which the criterion is applied a text field, a number field, or
a date field? If it's not a text field, your criterion is incorrectly
specified.
 
Hi everyone,

I have a problem and a question (related to the problem).
I have a query which reads something like

Select tablename.stringfield as alias, max
(tablename.intfield)as anotheralias, etc... HAVING
(tablename.fieldname)='" & form criteria & "';
In the query builder this query runs as expected, but if
executed as a recordset in VBA, no records are returned.
Why???
In the query builder the '" & is left out of course.
Click to expand...

Well, for starters, the HAVING clause is applied *AFTER* the summing,
totaling, etc. are all done. To filter the records to be maxed,
summed, etc. change HAVING to WHERE.

As for the recordset question... without seeing the actual code and
(probably) the actual constructed value of the SQL string, it's hard
to be sure; just that the critera aren't (apparently) being correctly
passed to JET. Note that an OpenRecordset method cannot directly use a
parameter query - you need to either fill in the actual *contents* of
the form control (as opposed to its name) in the "form criteria"
above, or else create a Querydef object based on the SQL and use code
like

Dim prm As Parameter
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

before creating the recordset.
 
(tablename.fieldname)='" & form criteria & "';
In the query builder this query runs as expected, but if
executed as a recordset in VBA, no records are returned.
Click to expand...

It's only Access that has access to the GUI elements: SQL passed by VBA
directly to the Jet database does not use the expression evaluation service
and therefore knows nothing about Forms!MyForm!MyControl and such like.

If you want to pass SQL directly (and you probably do), you need to insert
the form criteria stuff into the SQL

"WHERE Age > " & Str(dblAgeCalc) & " AND etc.."

or else use a parameterised querydef and fill in the parameters

With QueryDefs("qryMyUpdateQuery")
.Parameters("Age") = dblAgeCalc
.Execute dbFailOnError

End With

and so on. Hope that helps


Tim F
 
I think you left out the GROUP BY clause in the SQL since
you need the GROUP BY clause if you use the HAVING clause.

The the JET SQL Reference topic "HAVING Clause".

HTH
Van T. Dinh
MVP (Access)
 
Hi all, thanks for the responses! Sorry for the double
post, they were apparently having trouble yesterday with
the newsgroup server. When my original post did not
appear, I thought I might have had some problem on my end
so I re-posted. After that I noticed that the last posts
showing were in the 6:00 am range. I noticed that at about
3pm EST.

Second, I did have the Group By clause in the query
string. I created the query, and it worked correctly, in
the query builder. I then copied the SQL straight from
there and pasted it into the VBA editor in the appropriate
location. I find it much easier to do this than to use as
yet poorly developed SQL skills. After I pasted it into
VBA I put the '" & around my criteria. I have done this
same thing in numerous places in my application with no
problems, but this time it does not work. I even tried a
simple select query with NO criteria and got no records
returned. There are a number of records in the table being
queried. The really strange thing here is that there are
several other queries in this section of code that work
perfectly!!!!!

Thanks!

Kevin
 
Dick,

See my second post in this thread for more information. My
criteria is text. I assign the value of my field to a
variable I use in the code for other things. This variable
is a string.

Thanks!

Kevin
 
Hi all, the Mystery deepens:

I had the recordset delcaration as a dbopensnapshot. I
removed the sbopensnapshot so my set statement reads as
follows:

rst=db.openrecordset(sqlstr)

My query string is declared as follows:

sqlstr="SELECT tbl_cntnr_listing.Container_ID FROM
tbl_cntnr_listing;"

This is the simplified form of the query.

There are 9 records in the table. This should bring back 9
records. It brings back 1. Container_ID is a text field.

???

Very confused!

Thanks for the help!

Kevin
 
Hi,



Try something like that :





"SELECT Orders.EmployeeID, Sum(Orders.Freight) AS SumOfFreight

FROM Orders

WHERE (((Orders.ShipCountry)='" & Mycriteria & "'))

GROUP BY Orders.EmployeeID "
 
Kevin said:
Hi all, the Mystery deepens:

I had the recordset delcaration as a dbopensnapshot. I
removed the sbopensnapshot so my set statement reads as
follows:

rst=db.openrecordset(sqlstr)

My query string is declared as follows:

sqlstr="SELECT tbl_cntnr_listing.Container_ID FROM
tbl_cntnr_listing;"

This is the simplified form of the query.

There are 9 records in the table. This should bring back 9
records. It brings back 1. Container_ID is a text field.

???

Very confused!

Thanks for the help!

Kevin
Click to expand...

Kevin -

How did you determine that the query only returned one record? Was it
by checking the recordset's RecordCount property? Did you call
rst.MoveLast first, or otherwise go to the end of the recordset before
interrogating rst.RecordCount? I ask because the RecordCount property
of anything but a table-type recordset will always be either 0 (no
records) or 1 (only one record examined *so far*) immediately after
opening the recordset. RecordCount is only accurate when you have moved
to the end og the recordset. Were you aware of this?
 
Kevin said:
Hi all, thanks for the responses! Sorry for the double
post, they were apparently having trouble yesterday with
the newsgroup server. When my original post did not
appear, I thought I might have had some problem on my end
so I re-posted. After that I noticed that the last posts
showing were in the 6:00 am range. I noticed that at about
3pm EST.

Second, I did have the Group By clause in the query
string. I created the query, and it worked correctly, in
the query builder. I then copied the SQL straight from
there and pasted it into the VBA editor in the appropriate
location. I find it much easier to do this than to use as
yet poorly developed SQL skills. After I pasted it into
VBA I put the '" & around my criteria. I have done this
same thing in numerous places in my application with no
problems, but this time it does not work. I even tried a
simple select query with NO criteria and got no records
returned. There are a number of records in the table being
queried. The really strange thing here is that there are
several other queries in this section of code that work
perfectly!!!!!

Thanks!
Click to expand...

So post the code already! :-)
 
Dirk,

Thanks for the reply. That worked! I also had a problem
with the order of when I set the value of the variable I
used in the query criteria. I had been setting the
variable value before I initiated the recordset, but not
before I declared the sql string. Also, your point on
moving last cleared up the problem with the record count.
No I was not aware of the issue with the recordcount, but
I will keep that in mind from now on!

Thanks for the help!

No longer lost and confused!

Thanks again!

Kevin
 
Kevin said:
Dirk,

Thanks for the reply. That worked! I also had a problem
with the order of when I set the value of the variable I
used in the query criteria. I had been setting the
variable value before I initiated the recordset, but not
before I declared the sql string. Also, your point on
moving last cleared up the problem with the record count.
No I was not aware of the issue with the recordcount, but
I will keep that in mind from now on!

Thanks for the help!

No longer lost and confused!
Click to expand...

So this means you're all set now? Great! If not, post back.
 
Back
Top