Query not working in Access 2007

[dmkennard2]

Hi,
I have a query that did work in Access 2003 but i cannot get to work in 2007. The form where the query is used has several checkboxes that filter a subreport when they are clicked.

Like IIf([Forms]![frmAllData]![chkBlocked]=True,-1,"*")

This is the code i have in the query for each checkbox. When i take the code out the query runs ok.

I dont understand why this would work in 2003 and not in 2007!!

Any help would be much appreciated.

Thanks
Dazz

 

[dhookom]

What is the data type of the field you are filtering?

Duane
Hook'D on Access
MS Access MVP

 

[dmkennard2]

I have 4 which are text and one which is a comparison of one field against another to see if it is greater than the other.
So 4 x Text and 1 x Number

 

[dhookom]

You shouldn't compare text to [red]-1[/red] and I wouldn't use [red]Like "*"[/red] with a numeric field.

Duane
Hook'D on Access
MS Access MVP

 

[dmkennard2]

So why did this work in 2003 and not in 2007 and are there any solutions on how i can make it work in 2007?

 

[dhookom]

You could provide your exact field and control names as well as the data types. We may be able to provide a solution that doesn't require Access to resolve your data type mismatches.

Duane
Hook'D on Access
MS Access MVP

 

[dmkennard2]

Hi,
Thanks for this.
SQL code is:

SELECT tblMainData.Handling_Unit, tblMainData.Material, tblMainData.SUT, tblMainData.Blocked, tblMainData.Type, tblMainData.WCS_Qty, tblMainData.SAP_Qty, tblMainData.WCS_Location, tblAisle.Aisle, tblSide.Side, tblLocation.Location, tblLevel.Level, tblBack_Front.[Back/Front], tblStorage_Bin.Bin, [Blocked]="S" AS Block, [Type]="DHP" AS PartPallets, [WCS_Location]="No location" AS NoLoc, tblMainData.Date
FROM tblStorage_Bin RIGHT JOIN (tblSide RIGHT JOIN (tblLocation RIGHT JOIN (tblLevel RIGHT JOIN (tblBack_Front RIGHT JOIN (tblAisle RIGHT JOIN tblMainData ON tblAisle.Aisle = tblMainData.Aisle) ON tblBack_Front.[Back/Front] = tblMainData.[Front/Back]) ON tblLevel.Level = tblMainData.Level) ON tblLocation.Location = tblMainData.Location) ON tblSide.Side = tblMainData.Side) ON tblStorage_Bin.Bin = tblMainData.Bin
WHERE (((tblMainData.Handling_Unit) Like fCboSearch([Forms]![frmAllData]![txtSSCC])) AND ((tblMainData.Material) Like fCboSearch([Forms]![frmAllData]![cmbMaterial])) AND ((tblMainData.SUT) Like fCboSearch([Forms]![frmAllData]![cmbSUT])) AND ((tblAisle.Aisle) Like fCboSearch([Forms]![frmAllData]![cmbAisle])) AND ((tblSide.Side) Like fCboSearch([Forms]![frmAllData]![cmbSide])) AND ((tblLocation.Location) Like fCboSearch([Forms]![frmAllData]![cmbLocation])) AND ((tblLevel.Level) Like fCboSearch([Forms]![frmAllData]![cmbLevel])) AND ((tblBack_Front.[Back/Front]) Like fCboSearch([Forms]![frmAllData]![cmbFrontBack])) AND (([Blocked]="S") Like IIf([Forms]![frmAllData]![chkBlocked]=True,-1,"*")) AND (([Type]="DHP") Like IIf([Forms]![frmAllData]![chkPartPallets]=True,-1,"*")) AND (([WCS_Location]="No location") Like IIf([Forms]![frmAllData]![chkNoLoc]=True,-1,"*")) AND (([WCS_Qty]<>[SAP_Qty]) Like IIf([Forms]![frmAllData]![chkDifferences]=True,-1,"*")) AND (([Material]<>"") Like IIf([Forms]![frmAllData]![chkCode]=True,-1,"*")))
ORDER BY tblAisle.Aisle, tblLocation.Location, tblLevel.Level;

MainData Table	details

Handling_Unit = Text
WCS_Location = Text
Material = Text
SUT = Text
Blocked = Text
Type = Text
Bin = Text
WCS_Qty = Number
SAP_Qty = Number
Date = Date/Time
Aisle = Text
Side = Text
Location = Text
Level = Text
Front/Back = Text

All other tables are Text

 

[dhookom]

I'm not sure I understand your logic however, in the following, if Blocked <> "S", the expression to the left of the Like returns 0. If the check box on the form is not checked, the IIf() returns the string "*". Your criteria compares a numeric value with a string, which I try to avoid.

Try replacing this type of expression:

   (([Blocked]="S") Like IIf([Forms]![frmAllData]![chkBlocked]=True,-1,"*")) AND

with this type of expression:

   (([Blocked]="S") <= IIf([Forms]![frmAllData]![chkBlocked]=True,-1,0)) AND

Duane
Hook'D on Access
MS Access MVP

 

[dmkennard2]

Hi,
Thanks for your help with this.
Unfortunately it did not work, but what it has highlighted is if i take this out of the statement the query runs and returns data. Now i am even more confused!!

 

[dhookom]

I am confused by your logic/solution. Is this the record source for a form or report?

Duane
Hook'D on Access
MS Access MVP

 

[dmkennard2]

It's a record source for a subform.

 

[dhookom]

Normally, I would use link master/child and/or build a Record Source dynamically to display records in a subform.

Your logic seems a bit convoluted (not necessarily bad) when you create a yes/no value and then filter on the yes/no value in the criteria.

Duane
Hook'D on Access
MS Access MVP