TO_CHAR and BETWEEN Operator in my SQL Code

[JrExceler]

Hello Everyone,

I'm using SQL to get data from Access to my Excel Sheet, but I want to use the invoice date to have another filter criteria,
Do you know how can it be done?

I'm trying to convert the Invoice Date with:
" TO_CHAR(`Invoice Date`, 'YYYYMMDD')," & _
and then use BETWEEN in my Query,
however, it's giving me error.

¿is there any other way to get it?

This is my original Code that is Working,
But I cannot make it work with BETWEEN operator.

Any idea?

SQL0 = " SELECT " & _
        " Mat," & _
        " `Vol`," & _
        " Mon," & _
        " `Invoice Date`," & _
        " `Group`," & _
        " Dest," & _
        " source," & _
        " Sales"
SQL7 = " FROM `C:\Sales\NSalesAc.accdb`.NSales" & _
  " WHERE `Group` = '" & MODL & "' " & _
  " AND Dest = '" & MKT & "' "
     SQL = SQL0 + SQL7

 

[Norie]

What criteria are trying to set and how have you tried using TO_CHAR and BETWEEN?

 

[JrExceler]

What criteria are trying to set and how have you tried using TO_CHAR and BETWEEN?

The first thing I tried was to change Date in the original format of: dd/mm/yyyy to the format YYYYMMDD

SQL0 = " SELECT " & _
        " Mat," & _
        " `Vol`," & _
        " Mon," & _
        " TO_CHAR (`Invoice Date`, 'YYYYMMDD'), " & _
        " `Group`," & _
        " Dest," & _
        " source," & _
        " Sales"
SQL7 = " FROM `C:\Sales\NSalesAc.accdb`.NSales" & _
  " WHERE `Group` = '" & MODL & "' " & _
 
  " AND Dest = '" & MKT & "' "

     SQL = SQL0 + SQL7

But it's giving syntax error,
Any idea how to solve this error? what am I doing wrong with the code?

 

[Norie]

If anything you should probably use a format like yyyy-mm-yy.

Have you tried creating the query you want in Access itself?

If you can do that successfully you can get the proper SQL syntax from the SQL view of the query.

PS Pretty sure there's no TO_CHAR function in Access SQL. Also, you might want to avoid using 'Group' for a field name, could get confused with SQL's GROUP BY clause.

 

[Norie]

Oops, that format should be yyyy-mm-dd.

 

[Joe4]

Posted here too: https://www.mrexcel.com/forum/microsoft-access/999114-to_char-between-operator-my-sql-code.html

Similar to Cross-Posting, please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.