When I copied the statement BETWEEN #01/04/2017# AND #30/04/2017# from the Query_TestTable into the sql window from Tools/SQL it returned: 1: Unexpected token: BETWEEN in statement [BETWEEN]
Try the new sample attached in my answer.
Query_TestTable is telling me that the date format must be a valid date even when it is:
‘BETWEEN #04/01/17# AND #04/28/17#’
This last comment - I don’t know if this about the NEW sample or OLD. Also that format is not for use in SQL view, only in Critereon in edit design view.
OMG, ignore that previous about valid date because it is damn well working ok now
Finally. Please don’t forget to mark the answer as correct. Also, go over the documentation I pointed you to earlier in the comments. You need to understand the terminology especially when asking questions. And finally, learn SQL. Very important. Lots of good info on the net.
Thank you and thank you again, as it is working correctly after I fiddled around a bit. And yes the sql is different from just putting a statement in the Criteria of the query, as statements don’t always work but SQL does.
Where is the tick you are asking to mark, is it the big Green up arrow.
Really glad to hear it now works for you. See the end of my answer for check mark location.
After I thought it was fixed and working, after I set a filter in the qryWXdataLeeds of
BETWEEN {d ‘2017-04-01’ } AND {d ‘2017-04-30’ }
I now get an ID1 conflict. or more precisley Error inserting the new record.Violation of unique constraint SYS_PK_260 : duplicate values for columns “ID1” in statement [INSERT INTO “tblWXdataLeeds” all data entered into the form.
This seems like I can’t have filtered data in a query, unless there is more to it.
After viewing my copy of the file I see no problem with adding a record. As this is a different issue, please ask this as a new question.
This is the structure of the forum. It also provides others an easier way to possibly find a solution to their problem.
Update: @Awy65, I’m trying to understand your using a slash style date. In the table design, is your ReadingDate field of type Date or is it something else? For example here the field named created is of type Date: 
But it also quite possible to put a date string in a text field. It is easier to work with in a date field, but if it’s in a text field that matters here.
LO can connect to a number of different data bases, and each one has somewhat different SQL. On top of that date formats can depend on where you live or work.
Rather than solve your problem for you, which I find counterproductive in the long run, I’d rather give you the tools to solve it yourself. So if you’re connecting to the default embedded database which is HSQLDB1.8 the manual is here. There is a whole big section on dates and times in it. I also maintain a list of hopefully helpful resource links here.
I started w/ HSQLDB but now generally connect to MariaDB instead. I’ve found that this MySQL twin offers more features, and LO still works fine with it. LO is switching to Firebird as the default embedded database engine, so you might want to start look at that as well. (HSQLDB will remain as well.)
I worked with Access for many years before coming to LO, so welcome. I understand and appreciate how frustrating it can be to start over. This Q/A site has been quite helpful to me, and in particular Ratslinger and several others have been very helpful.
LO won’t do everything that Access does, but it does do quite a bit, and it seems like development is slowly ongoing on it.