Syntax Error on IF_THEN_ELSE statement 1

[tbiceps]

I have listed the code behind an IF Then Else statement that I'm attempting to use in a query.

It doesn't work because of a syntax error. Does anyone see the error?

If [Forms]![fReportQuerybyWMSApp]![Combo9] ="All"

Then "Inbound" Or "Outbound" Or "Inventory Management" Or "Returns" Or "Miscellaneous"

Else [Forms]![fReportQuerybyWMSApp]![Combo9]

End If

 

[DENewkirk]

If you have typed it exactly as presented, the first problem is that the "Then" has to be on the same line as the "If". Second, the "Then" has no verbs. Third, the "Then" line has "Or", which is only relevant in the "If" condition, unless you intend to use it as a bit-wise OR operator, which would create a string of strange characters indeed.

I suggest you follow your post up with a plain-text narrative of what you hope do, and I'm sure one of us will have some concrete advice on how to fix it.

 

[tbiceps]

My objective is as follows:
I'm working with a query. In this query, I have two fields that I'm querying for specific data. The query is behind a drop-down menu. There are multiple selections for each drop-down menu. For example, one drop-down menu has the following selections,WAREHOUSE_PROCESS_AREA(Inbound, Outbound, Inventory Management,etc.), the other dropdown menu has OPERATIONS_AREA(Pick, Pack, Ship, Receive, etc.). WAREHOUSE_PROCESS_AREA and OPERATIONS_AREA are the field names. I want to make a selection from the dropdown menu in the WAREHOUSE_PROCESS_AREA, and a selection from the drop-down menu in the OPERATIONS_AREA. The goal of querying for a one to one relationship works. However, I would like to make a selection of "All" from the Operation_Area field. So that my result will return ALL OPERATIONS_AREA(Pick, Pack, Ship, Receive, etc.)that match the chosen WAREHOUSE_PROCESS_AREA field choice. Therefore, if I made a choice of WAREHOUSE_PROCESS_AREA(ALL), and Operation_Area (Pick), then my query should return, (Inbound,Pick), (Outbound, Pick), (Inventory, Pick), (Management, Pick), etc. I'm trying to use the IFTHENELSE statement on the query. Any suggestions?

 

[DENewkirk]

Interestingly, Boolean algebra tells us that when combining one condition that is always TRUE with another condition that varies, you only need test the second condition. What I mean is, that if you have chosen ALL for the warehouse area, you can word your query to select for the current value from the operations area, rather than trying to code the query for every logical combination.

If you write an AfterChange or AfterUpdate event handler for the OPERATIONS_AREA control, you can have it create a query statement for your Form.RecordSource or other use:

If me.WAREHOUSE_AREA = "All" Then
' This line selects for all records with the
' chosen Operations Area value:
strQuery = "SELECT * from MyTable WHERE OperationsArea = '" & _
me.OPERATIONS_AREA & "'"

Else
' This line selects for all records with both
' the selected Warehouse Area and Operations
' Area values:
strQuery = "SELECT * from MyTable WHERE WarehouseArea = '" & _
me.WAREHOUSE_AREA & "' AND OperationsArea = '" & _
me.OPERATIONS_AREA & "'"
End If

Form.RecordSource = strQuery

In the example, MyTable is the name of your table, and I have differentiated the form control names as all upper-case (me.WAREHOUSE_AREA), and the table field names as mixed (WarehouseArea).

As soon as you set the record source equal to the SQL statement (strQuery) the form will be requeried.

NOTE that I have assumed that every record will have a valid WarehouseArea value, and have not put protecting code into the 'All' case.

I hope this will help.

Don Newkirk
don.e.newkirk@odot.state.or.us

 

[Golom]

I infer that you are trying to build a query dynamically in code. Is that correct?

If so, you may need something like

Dim SQL As String
SQL = "Select * From myTable WHERE "

If [Forms]![fReportQuerybyWMSApp]![Combo9] ="All" Then

   SQL = SQL & "WAREHOUSE_PROCESS_AREA IN ('Inbound', 'Outbound', 'Inventory Management', 'Returns', 'Miscellaneous')"

Else

   SQL = SQL & "WAREHOUSE_PROCESS_AREA = '" [Forms]![fReportQuerybyWMSApp]![Combo9] & "'"

End If