Send an "In ('Criteria')" parameter to a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I currently have the following query:

SELECT dbo_myvDeltaT.jobnum, dbo_mytShortages.class
FROM dbo_myvDeltaT INNER JOIN dbo_mytShortages ON dbo_myvDeltaT.jobnum =
dbo_mytShortages.jobnum
WHERE (((dbo_mytShortages.class) In ("SMP","MFC")));

I would like to use a function to set the criteria for the IN() clause such
that the bottom line would read:
WHERE (((dbo_mytShortages.class) In (SetClass())));
or it could read
WHERE (((dbo_mytShortages.class) SetClass()));

The SetClass() function would be fed a string such as "'SMP', MFC'" as it's
return value.
It would also have to understand the '*' character for all records.

I have used message boxes to verify that I am getting what I am looking for
out of the function, but it will not work in the query. I can't use this one
only in code as a second query has to run off of this one to then be joined
to a table in a third query to limit the records that will be used in a
report.

My boss wants to not have reports show up if there are no records in it's
sub report while there is data for the main report and the criteria for the
sub report is set in code on report open since you can't send criteria to a
sub report any other way.

I hope what I have written makes sense as I really need to find an answer
for this.

Thanks in advance!
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

1. You can't have a wildcard character "*" in an IN () predicate.
2. You can't use a function to insert a comma-delimited paramter into
an IN () predicate 'cuz the function returns one string of:

"'SMP','MFC'"

which is interpreted as one string instead of the 2 strings you want.
The query won't find a record w/ a column value of "'SMP','MFC'".

You can use the InStr() trick to get something like the IN () predicate.
E.g.:

PARAMETERS SearchString TEXT(255);
SELECT...
FROM...
WHERE InStr(',' & SearchString & ',' , ',' & column_name & ',')>0

This will return true if the value of the column_name is in the
SearchString. The True will select the record. The SearchString should
be a comma-delimited string as returned by your function. You can
substitute you function name in place of the SearchString parameter name
& get rid of the PARAMETERS clause.

For the * wildcard you might be able to do this:

WHERE IIf(SearchString="*", True, InStr(',' & SearchString & ',' , ','
& column_name & ',')>0)

The IIf() function will examine the SearchString parameter. If it
equals the wildcard then a True is returned, which causes the query to
return all records (futher filtered by any other WHERE criteria);
otherwise, the InStr() trick is used.

==== Report ====

I'm not sure you can cancel a report if a sub report doesn't have any
data. There is a property of a sub report called HasData. Perhaps, in
a section's OnPrint event you could watch for that and cancel the report
that way. The following assumes the sub report is in the report's
Detail section.:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

If Me!subreportName.Report.HasData = False Then
DoCmd.Close acReport, Me.Name
End If

End Sub

But, this wouldn't work when there was some data for some sub reports
and none for others. E.g.:

1 Main report info
Sub report data

2 Main report info
<no sub report data>

3 Main report info
Sub report data

4 Main report info
<no sub report data>

The report would print part 1 and then hit part 2 and close.

The other thing you may want to do is run the query that fills the sub
report(s) and just count the records. If the count = 0 then stop the
report. E.g.:

Private Sub Report_Open(Cancel As Integer)

' The name of the query that fills the sub report
Const QRY = "SubReportQuery"

Dim db as DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDB
Set rs = db.OpenRecordset("SELECT Count(*) FROM " & QRY)
If Not rs.EOF Then
' There's a count - is it zero?
If rs(0) = 0 Then Cancel = True
Else
' Eof means no records
Cancel = True
End If

set rs = nothing
set db = nothing

End Sub

The rs(0) = 0 checks if there are any records in the sub report's query.
Cancel = True will stop the report.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBREbsXYechKqOuFEgEQJ71ACgpEYDkmgR2UJL35T0kHEiStAu5v0AoMYz
5HK9SEvYzK7xGYYkRt355gtv
=lM0B
-----END PGP SIGNATURE-----
 
Must try this again.

I typed out a reply and microsoft ate it. I hate when you hit post and it
makes you log in again and then clears your response.

I got it to work, just had to produce the query in SQL, not the design grid
as I had no idea that the SQL you gave would look the way it ended up looking
in the design grid. I had to take out all of the ',' and & markings to get
it to work, but hey, I learned something even if I don't really understand
the logic behind it.

As for the report part of your post, it wasn't something I was considering
as this query is run on the data used for the sub report and will be joined
to the data for the base report such that base reports will only be printed
for jobnums that will have records in the sub reports determined buy what
class of parts you want to see on the reports. In essance, I will set a
global variable when the user clicks on preview that will then be used to
limit output, but since the sub report is the limiting factor, I couldn't do
it the normal way.

Thanks so much, I beat my head for a few days trying to figure this one out.
 
Back
Top