I'm Stumped

[sdimaggio]

I have a query running off of a form.

In the criteria section of my query I have the following code:

[forms]![frm_PlantProfiles].[employees]

I am trying to do a query of employees "greater than" 2000.

When I place 2000 in the form field the query displays all the plants with 2000 employees. However, when I type " >2000 " in the form's text box, I get a no data in the query and a message that says it's too complex.

When I type " >2000 " directly in the criteria section of the query it works. Why then does'nt it work when it grabs the criteria off of the form.

What am I doing wrong?

Any help would be greatly appreciated.

 

[HarleyQuinn]

When you type it in the textbox your query is looking for the text ">2000" which obviously doesn't appear in the table.

Try putting the > directly into the WHERE clause in the code rather than as the search text.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[PHV]

In the criteria section of your query:
>[Forms]![frm_PlantProfiles]![employees]


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sdimaggio]

This works but my user may only want a certain number like 2000.

If I place ">" before the where clause it will always be greater than and not an exact number.

Any other thoughts.

thanks steve

 

[PHV]

Any other thoughts
How is the query launched ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sdimaggio]

there is a button on the form that opens a report which is based off of the query.

 

[PHV]

Take a look at the 4th argument of the DoCmd.OpenReport method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sdimaggio]

Here is my printbutton code. I don't quite understand what you are asking.

Private Sub Command47_Click()
On Error GoTo Err_Command47_Click

Dim stDocName As String

stDocName = "rpt_PlantProfiles"
DoCmd.Close acReport, stDocName

stDocName = "rpt_PlantProfiles"
DoCmd.OpenReport stDocName, acPreview

Exit_Command47_Click:
Exit Sub

Err_Command47_Click:
MsgBox Err.Description
Resume Exit_Command47_Click

End Sub

 

[PHV]

With the cursor inside the OpenReport word in your code press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sdimaggio]

I'm sorry! I did what you said. The help feature came up but I did not see anything of value to solve the problem.

 

[PHV]

Sub OpenForm(FormName, [View As AcFormView = acNormal], [FilterName], [highlight][WhereCondition][/highlight], [DataMode As AcFormOpenDataMode = acFormPropertySettings], [WindowMode As AcWindowMode = acWindowNormal], [OpenArgs])


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sdimaggio]

still stumped?

 

[ProgramError]

What PHV is saying, is put your condition clause ie >2000 after two additon commas in the line in your subroutine.

DoCmd.OpenReport stDocName, acPreview

so it looks like

DoCmd.OpenReport stDocName, acPreview,,">2000"

note the two commas just before the ">2000"

Ian M {UK}

Program Error
Programmers do it one finger at a time!

 

[dRahme]

Another perspective:

Use querydefs with a textbox on your from from which to obtain the 'where' criteria like so:

Private Sub cmdPrint_Click() 'The Button Click for Report
On Error GoTo theErrorhandler

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim Strsql As String
Dim StsqWhere As String
Dim varItem As Variant

'Text Box on Your Form '
StsqWhere = Me.txtCriteria


'Sql string defining the Rest of your query'

Strsql = "SELECT tblTest.test From tblTest where " & _
"tblTest.test " & StsqWhere & " ; "

Set dbs = CurrentDb

''Delete Your Old Report Query""
dbs.QueryDefs.Delete "RptQuery"

''Create New Report Query''
Set qdf = dbs.CreateQueryDef("RptQuery", Strsql)

Strsql = vbNullString

''Open your Report ''

DoCmd.OpenReport "Report1", acViewPreview

qdf.Close

ExitProcedure:
Exit Sub

theErrorhandler:

''If Query does not exist ''

If (Err.Number = 3265) Then

MsgBox "the query does not exist. We will need to Create it first", vbOKOnly

Resume Next

Else

MsgBox Err.Number & Err.Description

Resume ExitProcedure
End If

End Sub

This will build a query on the fly using any valid criteria input into your text box. If you want items > 1000 for example, enter into the text box > 1000 and the query will be built using that condition.

Works rather well.

Hope that helps.