WhereCondition

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

Guest

I have read some post that are related to the wherecondition but still can
get my problem solved. So here is my problem. I have a report that returns
all computers that have a given operating system. The user can select the OS
from a list box I can not get this to work. I keep getting a pop up asking
for the parameter. Here is my code that I have tried

temp = OSListBox.Value
DoCmd.OpenReport "OS", acViewPreview, , WhereCondition:=temp

and I tried this


DoCmd.OpenReport "OS", acViewPreview, , WhereCondition:=OSListBox.Value


I still get the second popup is there something wrong with my SQL that
generates the report. I have no idea where to go from here.
 
newguy said:
I have read some post that are related to the wherecondition but still can
get my problem solved. So here is my problem. I have a report that returns
all computers that have a given operating system. The user can select the OS
from a list box I can not get this to work. I keep getting a pop up asking
for the parameter. Here is my code that I have tried

temp = OSListBox.Value
DoCmd.OpenReport "OS", acViewPreview, , WhereCondition:=temp

I still get the second popup is there something wrong with my SQL that
generates the report. I have no idea where to go from here.
Click to expand...


You have to have a complete conditional expression as would
appear in an SQL statement:

temp = "theosfield = """ & OSListBox.Value & """"

which will result in this kind of thing:

theosfield = "WinXP"

To see what result you want to achieve, create a query with
a sample os value as the criteria. Then switch to SQL view
and look at the WHERE clause.
 
Base your report on a query and put the following expression in the criteria
of the OS field:
Forms!NameOfFormContainingOSListBox!OsListbox

Use the following code to open your report:
DoCmd.OpenReport "OS", acViewPreview
 
I think I got the string formatted correctly but I still get the second
prompt for the os value. Could it be a problem with the stored produce? Here
is what it looks like

select os, computerMake from computer where os like @os

here is the formatted string on the vba side

temp "os LIKE" & " ' " & OStext.value & " ' "

What do you think is going on I can't seem to get any were on this
 
newguy said:
I think I got the string formatted correctly but I still get the second
prompt for the os value. Could it be a problem with the stored produce? Here
is what it looks like

select os, computerMake from computer where os like @os

here is the formatted string on the vba side

temp "os LIKE" & " ' " & OStext.value & " ' "
Click to expand...


Ah, uh, sorry. Since I don't work with SQL Server, I don't
think I can help unravel this.

All I can say here is that I don't see why you're using Like
without a wildcard and that there are extra space characters
around the apostrophes. Seems like it sould be:
temp = "os = " & "'" & OStext.value & "'"
But I have no idea how that can end up as a useful parameter
in @OS.
 
I thought I would post my solution just incase someone else had the same
problem.

I removed the where condition from my stored producer. I just selected all
the fields I needed and used a filter. Here is the code for the filter.

strFilter = "[com_OS]" & "=" & "'" & OStext.Value & "'"
If strFilter <> "" Then
DoCmd.OpenReport "FinalReport", acViewPreview
With Reports![FinalReport]
.Filter = strFilter
.FilterOn = True
.OrderBy = strOrderBy
.OrderByOn = True
End With
End If
 
Back
Top