Error When Using REPLACE In SQL

Hello.  I am Connecting to an Access db via ODBC.  I am using PHP to display my results, but I can't get the SQL REPLACE function to work.  If I were using MySQL, I would put the following into my select statement:
  "  REPLACE(permits.Date,'00:00:00','') as 'Date'  "

When I try to put that into the sql that hits the access db, I get this error:
"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect., SQL state 37000 in SQLExecDirect "

What am I doing wrong?  Thanks a lot.

*Side Question*
What type of SQL am I using when I'm connected to an Access db through ODBC?
thedeal56Asked:
Who is Participating?
 
ChumadConnect With a Mentor Commented:
It seems that REPLACE cannot be used through an ODBC connection (Jet SQL). See the first response to the question on this page: http://www.dbforums.com/archive/index.php/t-1059565.html
0
 
ChumadCommented:
Can you supply the sql statement you are using? Have you tried to execute that same sql statement directly within ms access?
0
 
thedeal56Author Commented:
I have not tried to run it in access, I'll do that right now.  Here is my entire statement:
$sql="SELECT REPLACE(Date,'00:00:00','') as Date ,[Customer Name],[Customer Address],[Permit Number]  FROM Permits
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
ChumadCommented:
I don't know for sure about MS Access, but it's possible that "date" is a keyword - in which case, you may need to put brackets around it:

SELECT REPLACE([Date],'00:00:00','') as [Date] ,[Customer Name],[Customer Address],[Permit Number]  FROM Permits
0
 
thedeal56Author Commented:
I put the brackets in, and now I get this message:
Undefined function 'REPLACE' in expression., SQL state 37000 in SQLExecDirect
0
 
thedeal56Author Commented:
Ah, I see.  In my case, I might be able to get around it by chopping off the last 8 characters in the date field.  Thanks!
0
 
thedeal56Author Commented:
Thankyou
0
 
thedeal56Author Commented:
I just thought I would post the statement that worked for me:

$sql="SELECT LEFT([Date],10) as Date1 ,[Customer Name],[Customer Address],[Permit Number]  FROM Permits

0
All Courses

From novice to tech pro — start learning today.