Query too complicated?!

[swift]

Hi again,

OK, so here is my next problem. I've got a query which I'm trying to use to pull information which matches only my criteria. However, I just keep getting this message (only shown partially):

The expression is typed incorrectly, or is too complex to be evaluated.

What I'm trying to do is only return results where the field DFChainage is inbetween the field SectionRunsFrom and SectionRunsTo. See the SQL below.

SELECT [Network Asset Defects].[Section Ref], [Network Asset Defects].DPCode, [Network Asset Defects].DFChainage, [Network Asset Defects].[Incid No], SectionDetailsQuery.SchemeID, SectionDetailsQuery.SectionRunsFrom, SectionDetailsQuery.SectionRunsTo
FROM [Network Asset Defects] INNER JOIN SectionDetailsQuery ON [Network Asset Defects].[Section Ref] = SectionDetailsQuery.[Section_Ref_RMMS]
WHERE ((([Network Asset Defects].DFChainage) Between [SectionRunsFrom] And [SectionRunsTo]) AND ((SectionDetailsQuery.SchemeID)=[Forms]![VM_RMMS_Form]![SchemeID]));

This seems like it should work to me, can anybody tell me what I'm doing wrong?

Cheers in advance!!

Swifty

 

[jzwp22]

What is the data type of the DFChainage field? A number? What is the data type of the SchemeID field?

I assume that you are supplying the values for [SectionRunsFrom] And [SectionRunsTo] at run time, but you have schemeID being supplied by a form. If you remove the schemeID critieria, will the query return the correct results?

Based on this: [Forms]![VM_RMMS_Form]![SchemeID]
the schemeID control is on a main form, is that correct? Do you have the correct control name?