query to look between two numbers not working

[Ruzz2k]

Hi I have the following query which refers to a textbox on a form which has a number in it, number could be negative positive or 0 and the query needs to select which row the value falls between i.e less than reference but >= reference2. for a value of 5.2 I get the following.
If I put values into the Expr2 expression rather than link to textbox then It works. I don't know what to do next appreciate any help

see attached picture of query and SQL below.

SELECT [External Calibrations RD].ID, [External Calibrations RD].[Ext Calibration Date], [External Calibrations RD].Ref, [External Calibrations RD].[True], [External Calibrations RD].Reference, IIf(([Forms]![frmRD Entry]![Ref 1 Average]<[Reference]) And ([Forms]![frmRD Entry]![Ref 1 Average]>=[Reference2]),True,False) AS Expr2, DLookUp("[True]","qryRef1Range","[ID]=" & [ID]-1) AS True2, DLookUp("[Reference]","qryRef1Range","[ID]=" & [ID]-1) AS Reference2
FROM [External Calibrations RD]
WHERE ((([External Calibrations RD].Ref)=[Forms]![frmRD Entry]![Ref 1]));

 

[Brianwarnock]

Is reference2 text instead of numeric?

Just a hunch as it is left justified.

Brian

 

[Ruzz2k]

Reference2 is calculated and so is True2
? Any ideas?

 

[Brianwarnock]

Why is it left justified, numerics are right justified when printed. I would look at the calculation and check that you have not inadvertently created a string.

Brian

 

[Ruzz2k]

Reference2 and True2 are simply reference and true from the previous row e.g. Dlookup("[Reference]","qryRef1Range","[ID]="&[ID]-1)

If I remove the speech marks from [Reference] it does not work and won't pull the previous row value. Problem seems to me to be the value it is getting off the form but this is just a general number and I have also tried typing the number into this box on the form with same results. So a little lost
Thankd

 

[Brianwarnock]

Had not spotted that, the DLookup is returning the value as a string, wrap it in CDbl

CDbl(DLookup...))

Brian

 

[Ruzz2k]

Ok I done that for reference2 and true2 and get error for first line which is expected. But now no Expr2 are at -1 for a value of 4.2. I think the problem is with expr2
Expr2: IIf([Forms]![frmRD Entry]![Ref 1 Average]<[Reference] And [Forms]![frmRD Entry]![Ref 1 Average]>=[Reference2],True,False)
I think it might be returning the number as text any ideas?
Thanks

 

[Ruzz2k]

Ok it now works it is as you said by using Cdbl it works I just had two ranges that it matched due to data entry error
Thanks so much for your help