Please show most current SQL statement including the specific dates used.
PLEASE, PLEASE,PLEASE,PLEASE,PLEASE use the dates I have given to you - BETWEEN '2017-04-01' AND '2017-04-30'. This way EXACTLY please, not what you think, just these dates EXACTLY.
DO NOT change ANYTHING else! There are not to be ANY / in this format and the - is absolutely necessary!
I have tested this on my system using a dBase file and have no problems. What version of LibreOffice are you using? Can you post a copy of your dBase file (no personal or confidential info please) to help speed the process?
And please use comments to respond. That is the purpose of the comments.
Attachments can only be done in a question or answer not in a comment. On your question or answer, edit, select paper clip on toolbar, select your file the OK to post.
I can’t post the odb file as it is too big at 2.1mb, even after compressing it.
The version of LO I am using is 5.1.6.2.
TI was asked by another person on here if the ReadingDate field is set to DATE in the table , but it isn’t and I fear that could well be the reason of the problem.
If I changed it to DATE, all the dates would be removed, so is there and easy way of restoring over 5000 dates
That is certainly your problem. I may have another way to do this but it will take a bit of time to test it. Post when I get results.
After some searching and testing, I find no easy way to convert this text field into a date field. It being a dBase file presents limited functionality in LO. The only possible solution I have at this time would involve conversion to a different database format.
Strangely enough, testing on my system with this statement (TEXTDATE is a text field):
SELECT TEXTDATE FROM CONVERTDATE WHERE TEXTDATE BETWEEN '01/01/2016' AND '01/03/2016' listed the one record for 01/02/2016. I would expect similar results on your end. Another thought, make a copy of your dBase file and using that, delete a large amount of the data and then post that for the test. Up to you.
I have made a copy of the dbase file and it only has the required table, query and form in it now. Also I have ,AFAIK , uploaded it as per your earlier information about doing it by edit and selecting the paper clip.
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