I have just changed the ReadingDate to TextDate, but left the Field Type as TEXT[VARCHAR] in my copy of the dbase file. It didn’t make any difference though as running the query gave back every record when:
SELECT “TextDate” FROM “Table1” WHERE “Table1”.“TextDate” BETWEEN ‘01/04/2017’ AND ‘30/04/2017’
@Avvy65 I have just downloaded your sample. Is this the table you have been working with? The one with the date problem? I am confused because you spoke in your question of dBase. This is not dBase. Also the table name is different.
It should have the same data in both Table1 and TestTable, but you can rename either one if you wish. I just created a new file and added what was required as the records are still the same. Ok if it is not dBase, is it just called Base then.
Ok apologies for calling it dBase but I have seen references to dBase elsewhere, and I thought they were the same thing, but no they are not.
Can I ask how you managed to get all the NewReadingDates in the said column.
Also I can’t see the said tick on the upper right of your answer.
Thanks
I am still getting syntax error message when I press F5 for a date range of say BETWEEN 03/26/15 AND 04/26/15. I press enter after I have put in the date range and get a syntax error, and also I am asked to correct the date range to MMDDYY when 01/04/2017 etc when the query returns the results.
I am confused now.
I see the system date is as you said it was: YYYMMDD, but the format in the query 2 is MMDDYY.
Please, you must learn when stating something we can’t see what you are looking at! What exactly are you viewing where you press F5?
OK. You need to understand the diference betwen internal & display dates. Internally on a database the format will be YYYY-MM-DD no matter where you are. This is for a variety of reasons. The display usually defaults to your locale unless otherwise specified. In your case 26/03/15 & my case 03/26/15 but internally they are both 2015-03-26. Now if you have a display problem with my new date, edit the table, go down to the new field & change the format.
Changed my file here to your date format and have no problem with
BETWEEN #01/04/2017# AND #26/04/2017# in the query criteria.
Just tried using that date format; BETWEEN #01/04/2017# AND #30/04/2017# in the Query_TestTable and it would not let me save it so I could run the query. Nothing was displayed except a warning message:
‘The value entered is not a valid date. Please enter a date in a valid format, for example, MM/DD/YY’
BUT using the exact same format in the Query2 pruduced a syntax error message when I pressed F5, but I could save and run it .Very strange indeed.
I hope I have given enough information
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.