I have come from MS Office Access for over 15 years, and want to use dBase now in linux.
I have tried various solutions for other people on other boards and youtube:
Between #date1# and #date 2#
‘Between date1 and date 2’
Between +date1 + and + date2+
“between” ‘date1 “and” date2’
I either get no results, or every date, depending on which one of the above I use.
In Access it is Between #date1# and #date2#, but using that in dBase gives no results.
Help please.
I stumble over terse SQL syntax errors a lot. So many poor and lacking error messages. This is especially frustrating to me because I have written a good language parser with careful error messages. It can work better. But the terse SQL error messages are what we have at the moment. SO, I think the best way to proceed is to break down your SQL into smaller parts, so you can divide and conquer. Take the SQL parts and poke them into the Menu, Tools, SQL… window, until you get it to work.
@Awy65, In the table design, is your ReadingDate field of type Date?
No, what happened was that I had almost 200 tables to copy across from Access to dBase and queries, and I just let or assumed dBase would set the fields to the correct data type, but it didn’t, and now it looks like I am having to change over 5000 dates to the Date type., unless there is an easier way of doing it.
@Avy65, Ok, good to know what was wrong. There might be a way to convert the date string into a date type w/ a query and a function to convert to a date.
You don’t specify but I’ll guess your talking about a query or some other SQL statement.
Just tested with dBase sample file:
SELECT "dbase_f5".DATE
FROM "dbase_f5"
WHERE DATE between '1950-01-01' And '1970-01-01'
works without a problem. The # you mention is specific to the JET engine.
Edit 5/3/2017:
By using the term dBase I was off on a wild goose chase. I was doing all my tests based upon you being connected directly to a dBase file. It was a waste of many hours since I could have easily answered this with the correct information. I have a converted file for you already.
The attached is your table with an added field at the end named “NewReadingDate”. This is now an actual date field. I also included “Query2” which is a simple query to use the select between clause.
Updated table: Result.odb
Now after all that, If this answers your question please click on the 
(upper left area of my answer).
Edit:
I didn’t include the “How” because it seems you do not have a grasp on SQL (please learn as this is essential in using ANY database). But here is what I did.
I did a ‘quick & dirty’ if you will, by creating a Temp table with your table selecting the record "ID and a re-format of the date in the text field:
SELECT "ID1", SUBSTRING("ReadingDate",7,4) || '-' || SUBSTRING("ReadingDate",4,2) || '-' || SUBSTRING("ReadingDate",1,2) MYDATE from "TestTable"
Next I add the new “Date” field to the existing table.
Finally, I updated the new field from the one generated in the temp table:
UPDATE "TestTable" SET "NewReadingDate" = (SELECT MYDATE FROM "TempTable" WHERE "TestTable"."ID1" = "TempTable"."ID1")
As for the checkmark:
Also, the ^ is an Upvote indicating approval of a question or answer & the down caret is dis-approval. The check mark signifies the correct answer was given.
Edit - UK format sample - ModifiedResult.odb
Copybase.odbYes it is a query with over 5000 records, and I want to select between 2 date ranges each month.
Thanks, but when I used this:
SELECT “ReadingDate”.DATE
FROM “tblWXdataLeeds”
WHERE DATE between ‘01-04-2017’ And '30-04-2017
I get a syntax error with nothing to say where it is exactly. I added it underneath the existing SQL of the query
The ReadingDate is a heading with a list of dates in the column, and the tblWXdataLeeds is just that, a table
For the moment, there are too many things against me to learn SQL now and apart from LO dbase crashing on me every time I have made a change to one of the forms or queries, I thank you for the help you have given so far, but SQL is a world I am not familiar with, so I’ll go back to Access for the time being.
I have put this into a SQL command in LO
SELECT “tblWXdataleeds”.“ReadingDate” From “tblWXdataLeeds” WHERE “ReadingDate” between ‘2017-04-01’ And ‘2017-04-30’
The status is:
2: Column not found: tblWXdataleeds.ReadingDate in statement [SELECT “tblWXdataleeds”.“ReadingDate” FROM “tblWXdataLeeds” WHERE “ReadingDate” BETWEEN ‘2017-04-01’ AND ‘2017-04-30’]
But if I use this:
SELECT “ReadingDate” From “tblWXdataLeeds” WHERE “ReadingDate” between ‘2017-04-01’ And ‘2017-04-30’
The status is
1: Command successfully executed.
So now I have to figure out where to put that line into the existing SQL for the query.
Ok, I have just tried this:
SELECT “WXdataleeds”.“ReadingDate” From “tblWXdataLeeds” between ‘2017-04-01’ And ‘2017-04-30’, and still getting the syntax error message.
The dates in the table are of the UK format ie: 01/04/2017
Apologies for the formatting of my replies, and hope this makes it more readable.
I have copied the existing SQL from the said query to make it more readable for me:
SELECT DISTINCT
“ID1” AS “ID1”,
“ReadingDate” AS “ReadingDate”,
“TempMaxC” AS “TempMaxC”,
“TempWindChillC” AS “TempWindChillC”,
“TempMinC” AS “TempMinC”,
“RainFall” AS “RainFall”,
“Pressure” AS “Pressure”,
“RH” AS “RH”,
“WindGust” AS “WindGust”,
“WindSpeed” AS “WindSpeed”,
“Direct” AS “Direct”,
“CloudAmnt” AS “CloudAmnt”,
“CloudType” AS “CloudType”,
“Conditions” AS “Conditions”
Then I added the WHERE clause
WHERE “ReadingDate” between ‘01/04/2017’ And ‘30/04/2017’
FROM
“tblWXdataLeeds”
That returned this status error:
1: Unexpected token WHERE, requires FROM in statement
Do I need to use another FROM, or have I put the WHERE clause in the wrong place.
This on it’s own doesn’t give what I want, as it for now just gives all the dates in the ReadingDate column, over 5000 of them
SELECT “tblWXdataLeeds”.“ReadingDate” FROM “tblWXdataLeeds” WHERE “ReadingDate” BETWEEN ‘01/04/2017’ AND ‘30/04/2017’
Ok, I have now added the WHERE after FROM:
FROM “tblWXdataLeeds” WHERE “ReadingDate” BETWEEN ‘01/04/2017’ AND ‘30/04/2017’
and that gives me every record, which I don’t want
The reason for the AS is because that is how dbase built the query when it was created with the wizard, and I just copied and pasted it.
Thanks
Using the internal date of YYMMDD gives no results at all, as the date format I use in the UK is DDMMYYY, which gives every record.
Yes I did specifically use the internal date, or USA date as we call it but no records were found. All the cells under Reading Date have the date as DDMMYYY.
This is the SQL in the said query which currently gives all and every record of dates:
SELECT “ReadingDate”,“ID1”, “ReadingDate” , “TempMaxC”, “TempWindChillC” , “TempMinC” , “RainFall”, “Pressure” , “RH”, “WindGust” , “WindSpeed” , “Direct” , “CloudAmnt” , “CloudType”, “Conditions” FROM “tblWXdataLeeds” WHERE “ReadingDate” BETWEEN ‘01/04/2017’ AND ‘30/04/2017’
If I invert the dates, then no records are found.
I copied and pasted what you asked me to use into the line of SQL and exactly how you wrote it. This gives NO results whatsover. To me it is something else which isn’t correct somewhere, unless of course it can’t be done anyway.
Typically dates are stored in YYYY-MM-DD format. Your dates are in the wrong format. See sample in my answer.
Also I don’t understand your SQL. Select statement should be:
SELECT “YOUR-TABLE-NAME”.“YOUR-FIELD-NAME” FROM “YOUR-TABLE” WHERE… and so on.
Internal date format is what is needed. Your statement is still incorrect.
SELECT "tblWXdataleeds"."ReadingDate" From "tblWXdataLeeds" WHERE "ReadingDate" between '2017-04-01' And '2017-04-30'
For some reason you left our the WHERE & field to compare with and didn’t put in the correct table name on the selected field.
Just re-read your comment - “I added it underneath the existing SQL of the query”. There is nothing to add. This is the entire statement. Except for the actual table & field names, it is identical to the code given in my answer. BTW, when a field or table name is all caps & no special characters, surrounding quotes are not needed as in the field DATE used in the answer.
Unfortunately, the statement you posted is still not the one I have posted for you just above. You are still missing the WHERE "ReadingDate" portion which is what is generating the error. Sorry to hear about the crashing. More information would be needed to help on that problem.
I am very sorry but I had a mis-spelling in my statement provided - tblWXdataleeds should have been tblWXdataLeeds. As you can see it is not necessary for your statement but is needed if other conflicting tables are also used. The SQL can be put in the Query section. Just select Create Query in SQL View, place the statement there & save it with a name.
Correct syntax for SELECT statement is SELECT...FROM...WHERE.... Yours is reversed. Also, you’ve done a lot of extra typing. If the result wanted is the same as the actual field name, you do not need to specify AS. "ReadingDate" will give the same exact result as "ReadingDate" AS "ReadingDate". If you wanted the output column title different, then AS is useful such as "ReadingDate" AS "Date". Here the output column name would be Date.
You keep falling back to the date format problem. No matter what the display date is, the INTERNAL date is YYYY-MM-DD. This is a standard. Use BETWEEN '2017-04-01' AND '2017-04-30'.
“to learn SQL now”? Truth is, access is based on SQL, just a different flavor. You see it when you go into edit a query and select SQL mode. I’m not going to fault you if you stick with the well known. But from my experience both Access and LO crash and misbehave at times, and about equally I would say. The one thing LO has over Access is our ability to report and get bugs fixed. There are still crashing bugs in my Access that have been there for years that MS hasn’t bothered to fix.
Did you specifically use BETWEEN '2017-04-01' AND '2017-04-30'? I mean exactly those dates?
@Avvy65, You might want to refresh your memory with something like this. This should be easy reading.
Syntax for most basic SQL select statement: SELECT [list of things to select] FROM [where to get it, tables, queries, sub-queries, and JOIN’s] WHERE [what to get and not to get, e.g. “City” = ‘New York’] ORDER BY [list of things to sort by].