Re: criteria issue

ID_1 is a field in the table, or an ALIAS to a computed expression? If it is
an alias to a computed expression, you have to use the computed expression,
not the alias. That is required because it generally happens that the WHERE
clause is evaluated BEFORE the SELECT clause itself, so the aliases do not
exists, yet, when the WHERE clause is evaluated, and keep or reject the
rows.



Vanderghast, Access MVP


"bigwillno2 via AccessMonster.com" <u32748@uwe> wrote in message
news:717be9c51ff7a@xxxxxx
Hey Michel thanks for being patient......

your last post look very promising, only thing that i am getting an error,
it's asking me for the [ID_1] selected......

this is my code, can you see what's wrong with it....

strDelim = "" 'Delimiter appropriate to field type. .
strDoc = "rptProduction"
'Loop through the ItemsSelected in the list box.
If Me.sched.ItemsSelected.Count = 0 Then
Beep
MsgBox "no item selected ", 48
Exit Sub
End If

With Me.sched
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column.

strModelNum = strModelNum & strDelim & .Column(3, varItem)
&
strDelim & ", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[OrderNo] IN (" & Left$(strWhere, lngLen) & ")"
End If
lngLen = Len(strModelNum) - 1
If lngLen > 0 Then
strModelNum = "[ID_1] IN (" & Left$(strModelNum, lngLen) & ")"
End If

'Report will not filter if open, so close it.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & "
AND
" & strModelNum
', OpenArgs:=strDescrip
Resp = MsgBox("Print??????", vbYesNo)
If Resp = vbYes Then
DoCmd.PrintOut
Else
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere & "
AND
" & strModelNum
', OpenArgs:=strDescrip

Michel Walsh wrote:
Right now, strWhere should be, when printed

Debug.Print strWhere

something look like:

OrderNo IN( "aaa", "b", "cde")

So, build similar string for other fields, such as string strScheduleDate,
to get, once printed, something like

ScheduleDate IN( #01-01-2001#, #02-02-2002#)

and then,

DoCmd.OpenReport "rptProduction", acViewPreview, _
WhereCondition:=strWhere & " AND " & strScheduleDate,
OpenArgs:=strDescrip

to get two criteria based on a list.

Hoping it may help,
Vanderghast, Access MVP

Hey Michel,
sorry that i didnt explained myself better.
[quoted text clipped - 55 lines]
OpenArgs:=strDescrip
Resp = MsgBox("Print??????", vbYesNo)

--
Message posted via http://www.accessmonster.com



.

Relevant Pages

  • Re: Parameter Query on an expression
    ... It is because you CANNOT USE ALIAS in the WHERE clause, but have to repeat the expression the alias stands for. ... but two levels of alias: LotsAcc depends on CountOfqtrce which is itself an alias for a computed expression. ... Furthermore, since the computed expression involves aggregate, some of the criteria have to be moved in the HAVING clause. ... I would expect it to determine what the "Profit"> value ...
    (microsoft.public.access.queries)
  • Re: Calculated Fields not Working
    ... exp1 is re-used in exp2. ... That is somehow logical since the SELECT clause is technically executed AFTER the WHERE clause did get evaluated. ... Unfortunately too, you cannot use alias in the ORDER BY clause, EXCEPT for a UNION query. ... With MS SQL Server, you cannot use an alias in a computed expression since alias are seen as column name given to fields in the result, BUT you can use them in the ORDER BY clause. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Criteria prevents Query from running.
    ... is probably a computed expression, which is not allowed in the WHERE ... clause use the expression, not the alias: ... edit the SQL statement and change in the WHERE clause for the ... expression on which is used as alias. ...
    (microsoft.public.access.queries)
  • Re: Parameter Query on an expression
    ... That should work unless cost or is itself another computed expression. ... In the SQL view you should have a WHERE clause which repeat the computed expression and which DOES NOT use the alias. ...
    (microsoft.public.access.queries)
  • Re: Aliases and the WHERE clause
    ... The one hope we could hold is that future releases of engines will build on ... To me, SQL also feels just like a set of principles, or if you wish, mental ... I was thinking about the ORDER BY clause. ... alias in the WHERE clause. ...
    (microsoft.public.access.queries)
Loading