Runtime error - Due to Where Clause Filtering? 1

[0212]

Good Afternoon! I am getting a runtime error (Syntax/missing operator) when I execute the following statement:

DoCmd.OpenForm " FRM_3_8_2007_Budget_Form ", acNormal, acEdit, StrWhere

It compiles fine. I have a dialog box in which users can enter one of several filtering fields. If the user leaves a field blank, it should return all records. The way that I am doing this is as follows:

If Not IsNull(Me.Combff) Then
StrWhere = StrWhere & " And [OfficeID] = " & _
"Me.Combff"
Else
StrWhere = StrWhere & " And [OfficeID] = " & _
"*"
End If

The IDs are autonumbers. Any ideas? Thanks in advance for any help that you can provide!

 

[TheAceMan1]

How are ya 0212 . . .

[blue]The IDs are autonumbers.[/blue]

If this is true then [blue]your trying to compare numeric to string![/blue] . . . No Can Do!

As a first shot at this (info is insufficient) perhaps:

[blue]If trim(Me.Combo_Off & "") <> "" Then
    StrWhere = StrWhere & " And [OfficeID] = " & Me.Combo_Off"
Else
    StrWhere = StrWhere & " And [OfficeID] Is Not NUll"
End If[/blue]

It would bw better if you show the code starting at the [blue]Dialog[/blue] and including the [blue]full build of strWhere.[/blue]

Also if you add [blue]Debug.Print[/blue] just after strWhere is fully built and add a breakpoint on the next line, in the [blue]Immediate Window[/blue] you can parse the final results for errors.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[0212]

Thank you, theaceman1! I think my code is really funky! I will try your suggestion for returning all records for a null value id. However, when I put values in the dialog box, I get the standard "Enter Parameter Value" after my form is opened. It is asking for the values that I just entered into the dialog! I have included all code for the ok_click on my dialog box. Thanks for you great help!




Private Sub OK_Click()
Me.Visible = False
Dim StrWhere As String
Dim strOrder As String
StrWhere = "1=1 "

'Check PE Combo Box for Null, IF Not Null, place value in PEID Field in form'
'If it is Null, return all records'
If Not IsNull(Me!Combo_PE) Then
StrWhere = StrWhere & " And [PEID] = " & _
"Me!Combo_PE"
Else
StrWhere = StrWhere & " And [PEID] = " & _
"*"
End If
'Same as above for AC Combo Box'
If Not IsNull(Me!Combo_AC) Then
StrWhere = StrWhere & " And [ACID] = " & _
"Me!Combo_AC"
Else
StrWhere = StrWhere & " And [ACID] = " & _
"*"
End If
'Same as above for Office Combo Box'
If Not IsNull(Me!Combff) Then
StrWhere = StrWhere & " And [REOfficeID] = " & _
"Me!Combff"
Else
StrWhere = StrWhere & " And [REOfficeID] = " & _
"*"
End If
'Same as above for UW Combo Box'
If Not IsNull(Me!Combo_UW) Then
StrWhere = StrWhere & " And [UWID] = " & _
"Me!Combo_UW"
Else
StrWhere = StrWhere & " And [UWID] = " & _
"*"
End If
'Same as above for UWTM Combo Box'
If Not IsNull(Me!Combo_UWTM) Then
StrWhere = StrWhere & " And [UWTMID] = " & _
"Me!Combo_UWTM"
Else
StrWhere = StrWhere & " And [UWTMID] = " & _
"*"
End If
'Test for Budget Report Type 1. Budget dollar range or'
'Budget remaining dollar range'
Select Case Me!Budget_Rpt_Type

Case 1
'Find all Total Budget Field <= value in Dialog Box'
StrWhere = StrWhere & " AND [BudgTot] >=" & _
"Me!TB_Budg_Rang"
strOrder = "[BudgTot]"
Case 2
'Find all Budget Remaining Field <= value in Dialog Box'
StrWhere = StrWhere & " AND [BudgRem] <=" & _
"Me!TB_Budg_Rem"
strOrder = "[BudgRem]"
End Select
DoCmd.OpenForm " FRM_3_8_2007_Budget_Form ", acNormal, acEdit, StrWhere
'Sort by Budget Total or Budget Remaining'
With Forms!FRM_3_8_2007_Budget_Form
.OrderBy = strOrder
.OrderByOn = True
'If option 1 (Budget Total), Text box in FRM_3_8_2007_Budget_Form = TB_Budg_Rang in this
'Make Less than labels etc invisible. Do opposite for Budget Remaining
If Me!Budget_Rpt_Type = 1 Then
TRptType = "Total Budget Report"
Greaterthan.Visible = True
TDollars = Me!TB_Budg_Rang
.LessThan.Visible = False
Else
.LessThan.Visible = True
TRptType = "Budget Remaining Report"
TDollars = Me!TB_Budg_Rem
.GreatThan.Visible = False
End If

End With
End Sub

 

[TheAceMan1]

[blue]when I put values in the dialog box, I get the standard "Enter Parameter Value" after my form is opened. It is asking for the values that I just entered into the dialog![/blue]

Sounds like you have a parameter query as the [blue]recordsource[/blue] of the form! If your gonna filter with code the recordsource should be a Select Query. You'll have to establish this before you continue.

What happens if you open the form independently from the form window?

A side from that the code you provided has other errors. I've condensed the top combobox section to provide proper syntax. In the code I've replaced strWhere with Cri (stand for Criteria).

[blue][purple]Private Sub OK_Click()
   Dim Cri As String, strOrder As String, Build As String
   Dim cbxName As String, idx As Integer, ID As String
   
   Me.Visible = False
   
   Do
      idx = idx + 1
      ID = Choose(idx, "PRID", "ACID", "REOfficeID", "UWID", "UWTMID")
      cbxName = "Combo_" & Choose(idx, "PE", "AC", "OFF", "UW", "UWTM")
   
      If Trim(Me(cbxName) & "") <> "" Then
         Build = "([" & ID & "] = " & Me(cbxName) & ")"
         
         If Cri <> "" Then
            Cri = Cri & " AND " & Build
         Else
            Cri = Build
         End If
      End If
   Loop Until idx = 5[/purple]
            
   [green]'Test for Budget Report Type 1. Budget dollar range or'
   'Budget remaining dollar range'[/green]
   Select Case Me!Budget_Rpt_Type
      Case 1
          [green]'Find all Total Budget Field <= value in Dialog Box'[/green]
          Cri = Cri & " AND [BudgTot] >=" & Me!TB_Budg_Rang
          strOrder = "[BudgTot]"
      Case 2
          [green]'Find all Budget Remaining Field <= value in Dialog Box'[/green]
          Cri = Cri & " AND [BudgRem] <=" & Me!TB_Budg_Rem
         strOrder = "[BudgRem]"
   End Select
   
   DoCmd.OpenForm " FRM_3_8_2007_Budget_Form ", acNormal, acEdit, Cri
   
   [green]'Sort by Budget Total or Budget Remaining'[/green]
   With Forms!FRM_3_8_2007_Budget_Form
      .OrderBy = strOrder
      .OrderByOn = True
      [green]'If option 1 (Budget Total), Text box in [/green]FRM_3_8_2007_Budget_Form = TB_Budg_Rang in this
      [green]'Make Less than labels etc invisible.  Do opposite for Budget Remaining[/green]
      If Me!Budget_Rpt_Type = 1 Then
         TRptType = "Total Budget Report"
         Greaterthan.Visible = True
         TDollars = Me!TB_Budg_Rang
         .LessThan.Visible = False
      Else
         .LessThan.Visible = True
         TRptType = "Budget Remaining Report"
         TDollars = Me!TB_Budg_Rem
         .GreatThan.Visible = False
      End If
   End With

End Sub[/blue]

BTW: If you've never read it: To get great answers and know whats expected of you in the forums be sure to have a look at FAQ219-2884 . . . particularly item 17 . . .

See Ya! . . . . . .

 

[0212]

Thank you, again, theaceman1!!. I worked on your initial response and I got it to work eventually. I then substituted your revised code (tremendous insight!) - very neat!! However, I could not get the last part to work (see below):

With Forms!FRM_3_8_2007_Budget_Form
.OrderBy = strOrder
.OrderByOn = True
'If option 1 (Budget Total), Text box in FRM_3_8_2007_Budget_Form = TB_Budg_Rang in this
'Make Less than labels etc invisible. Do opposite for Budget Remaining
If Me!Budget_Rpt_Type = 1 Then
TRptType = "Total Budget Report"
.Greaterthan.Visible = True
TDollars = Me!TB_Budg_Rang
.LessThan.Visible = False
Else
.LessThan.Visible = True
TRptType = "Budget Remaining Report"
TDollars = Me!TB_Budg_Rem
.GreatThan.Visible = False
End If
End With

I am trying to transfer a values from the dialog box to a text box in the form. Even though TRptType and TDollars have a value in the Dialog Box Code, there is no value in the form textboxes. The only way I was able to get it to work is to put the same code (but reversed) in the on load event for the form. Is this standard or have I done something wrong? Thanks again for your excellent help!

 

[randy700]

Else
StrWhere = StrWhere & " And [OfficeID] = " & _
"*"


Isn't this simply stating you want ALL OfficeID's?
If so... the AND portion is unnecessary. Your WHERE clause is complete as is.


Randy

 

[0212]

Thanks, Randy700, for your help! However, I was not able to make the "*" work to return all records (autonumber). I was able to make the rest of the code work above. Thank you all!

 

[randy700]

Seems to me that " And [OfficeID] = " & "*"
SHOULDN'T work to return all records. You most likely don't have any records that meet that criteria (where the OfficeID is an asterisk. If you don't include the OfficeID = portion in the WHERE clause, all office id's will be returned.



Randy

 

[0212]

Thanks, randy700! Just to clarify, I only need the following to return either a specific officeid or all if null:

StrWhere = StrWhere &

 

[PHV]

Anyway, you call the OpenForm with wrong args:
DoCmd.OpenForm "FRM_3_8_2007_Budget_Form", acNormal, , strWhere, acFormEdit

Furthermore if TRptType and TDollars are textboxes in FRM_3_8_2007_Budget_Form:
...
If Me!Budget_Rpt_Type = 1 Then
[!].[/!]TRptType = "Total Budget Report"
.Greaterthan.Visible = True
[!].[/!]TDollars = Me!TB_Budg_Rang
.LessThan.Visible = False
Else
...

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

 

[0212]

Thank you, PHV! I really appreciate you attention to my "missed" detail.

 

[TheAceMan1]

Howdy randy700 . . .

If you look at the post origination you'll find that OfficeID is [blue]numeric![/blue]

See Ya! . . . . . .