sql - MS Access query: between two numbers -

this isn't simple sounds. have scenario enter number text field form, want press button search number close (or exact) number. if leave field blank want return results.

i (very) new sql , access i'm having bit of trouble. know how find exact value:

like "*" & [forms]![navigation form]![navigationsubform].[form]![txtfuelconsumption] & "*" 

(my textbox called txtfuelconsumption)

an example of want returned:

say type in number "10" textbox , run query. want show me data entries fuel consumption rate of 7 13.

i tried using between function, not wrap head around it.

thankyou!

edit: thought of approach take. create standalone combo box values "small, medium, large" , use query assign specific values 'small, medium , large'. although, again, not know how this. i've put here suggestion in case people can't figure out first problem.

edit:

    select [car table].car_vin, [car table].car_class, [car table].car_bodytype, [car table].car_colour, [car table].car_make, [car table].car_model, [car table].car_enginetype, [car table].car_transmissiontype, [car table].car_gpsavailability, [car table].car_bootspace, [car table].car_fuelconsumptrate, [car table].car_seatnumber, [car table].car_greenstarrating, [car table].car_ancapsafetyrating, [car table].car_drivetype [car table]         ((([car table].car_vin) "*" & [forms]![navigation form]!     [navigationsubform].[form]![txtvin] & "*") , (([car table].car_class) "*"     & [forms]![navigation form]![navigationsubform].[form]![cmbclass] & "*") ,     (([car table].car_drivetype) "*" & [forms]![navigation form]!     [navigationsubform].[form]![cmbdrivetype] & "*") , (([car table].car_bodytype)     "*" & [forms]![navigation form]![navigationsubform].[form]![cmbbodytype] &     "*") , (([car table].car_colour) "*" & [forms]![navigation form]     ![navigationsubform].[form]![cmbbodytype] & "*") , (([car table].car_make)     "*" & [forms]![navigation form]![navigationsubform].[form]![txtmake] & "*")     , (([car table].car_model) "*" & [forms]![navigation form]     ![navigationsubform].[form]![txtmodel] & "*") , (([car table].car_enginetype)     "*" & [forms]![navigation form]![navigationsubform].[form]![cmbenginetype]     & "*") , (([car table].car_transmissiontype) "*" & [forms]![navigation     form]![navigationsubform].[form]![cmbtransmissiontype] & "*") , (([car     table].car_gpsavailability) "*" & [forms]![navigation form]     ![navigationsubform].[form]![chgpsavailability] & "*") , (([car      table].car_seatnumber) "*" & [forms]![navigation form]     ![navigationsubform].[form]![txtseatnumber] & "*") , (([car      table].car_greenstarrating) "*" & [forms]![navigation form]     ![navigationsubform].[form]![cmbgreenstarrating] & "*") , (([car      table].car_ancapsafetyrating) "*" & [forms]![navigation form]     ![navigationsubform].[form]![cmbancapsafetyrating] & "*") ,      ((abs([car_fuelconsumptrate]-nz([forms]![navigation form]![navigationsubform]     .[form]![txtfuelconsumption],[car_fuelconsumptrate]))<=nz([2],9999)) ,  ((abs([car_bootspace]-nz([forms]![navigation form]![navigationsubform].[form] ![txtbootspace], [car_bootspace]))<=nz([100],9999))); 

syntax error edit:

 , ((abs([car_fuelconsumptrate]-nz([forms]![navigation form]![navigationsubform].[form]![txtfuelconsumption],[car_fuelconsumptrate]))<=nz([2],9999))  

first, current expression not find single value, find 100.

for range, use method this:

select     *,     abs([consumptionrate]-[forms]![navigation form]![navigationsubform].[form]![txtfuelconsumption]) consumptionrange     [car table]     abs([consumptionrate]-nz([forms]![navigation form]![navigationsubform].[form]![txtfuelconsumption],[consumptionrate])) <= nz([inputdeviation], 9999) order     abs([consumptionrate]-[forms]![navigation form]![navigationsubform].[form]![txtfuelconsumption]) asc