Problem with date formatting in SQL command

Hello,


I work in an application in VBA/Access, which connects to an SQL
Server database via ODBC.

I am trying to run a SQL command to the SQL Server, but I have some
data problems :

My command is (in VBA) :

Dim dateTest as String, strSQL as String

dateTest = CStr(Year(Now)) & CStr(Month(Now)) & CStr(Day(Now))

strSQL = "SELECT ID_PRX, PRX_LIB FROM PRX_TABLE WHERE ID_PROJECT =
1000 AND ID_CYCLE <>2 AND PRX_END_DATE >= '" & dateTest & "'*

when I run the command without the PRX_END_DATE test, everything is
OK. But if I run it the way it is written (with simple quotes
surrounding the dateTest value), I get a 3464 message from Access
("Data type mismatch in criteria expression")


If I run this command directly to the SQL Server by using the tools
from Entreprise Manager, there is no problem at all, even with the
PRX_END_DATE test (I just replace dateTest with its value).

So my question is : how should I format the SQL command in VBA to
allow the testing of PRX_END_DATE being >= to the current date,
without getting the 3464 error message ?

Thank you.

Re: Problem with date formatting in SQL command

Note that PRX_END_DATE is a DATETIME field in the PRX_TABLE (in SQL
Server)

Re: Problem with date formatting in SQL command

nartla wrote:
> Hello,
>
>
> I work in an application in VBA/Access, which connects to an SQL
> Server database via ODBC.
>
> I am trying to run a SQL command to the SQL Server, but I have some
> data problems :
>
> My command is (in VBA) :
>
> Dim dateTest as String, strSQL as String
>
> dateTest = CStr(Year(Now)) & CStr(Month(Now)) & CStr(Day(Now))
>
> strSQL = "SELECT ID_PRX, PRX_LIB FROM PRX_TABLE WHERE ID_PROJECT =
> 1000 AND ID_CYCLE <>2 AND PRX_END_DATE >= '" & dateTest & "'*
>
> when I run the command without the PRX_END_DATE test, everything is
> OK. But if I run it the way it is written (with simple quotes
> surrounding the dateTest value), I get a 3464 message from Access
> ("Data type mismatch in criteria expression")
>
>
> If I run this command directly to the SQL Server by using the tools
> from Entreprise Manager, there is no problem at all, even with the
> PRX_END_DATE test (I just replace dateTest with its value).
>
> So my question is : how should I format the SQL command in VBA to
> allow the testing of PRX_END_DATE being >= to the current date,
> without getting the 3464 error message ?
>
> Thank you.
>
Is PRX_END_DATE a character string in the database? You are passing a
character string in the query. If it's a number you might want to do a
CLng(dateTest) and remove the quotes.

Re: Problem with date formatting in SQL command

Format the dateTest variable to YYYY-MM-DD format before enclosing it
in the single quotes:

strSQL = "SELECT ID_PRX, PRX_LIB FROM PRX_TABLE " & _
"WHERE ID_PROJECT = 1000 AND ID_CYCLE <>2 AND " & _
"PRX_END_DATE >= '" & Format(dateTest, "YYYY-MM-DD") & "'*

If there's also a time value in dateTest, use "YYYY-MM-DD HH:NN:SS".



On Mon, 15 Oct 2007 16:18:35 -0000, nartla
wrote:

>Hello,
>
>
>I work in an application in VBA/Access, which connects to an SQL
>Server database via ODBC.
>
>I am trying to run a SQL command to the SQL Server, but I have some
>data problems :
>
>My command is (in VBA) :
>
>Dim dateTest as String, strSQL as String
>
>dateTest = CStr(Year(Now)) & CStr(Month(Now)) & CStr(Day(Now))
>
>strSQL = "SELECT ID_PRX, PRX_LIB FROM PRX_TABLE WHERE ID_PROJECT =
>1000 AND ID_CYCLE <>2 AND PRX_END_DATE >= '" & dateTest & "'*
>
>when I run the command without the PRX_END_DATE test, everything is
>OK. But if I run it the way it is written (with simple quotes
>surrounding the dateTest value), I get a 3464 message from Access
>("Data type mismatch in criteria expression")
>
>
>If I run this command directly to the SQL Server by using the tools
>from Entreprise Manager, there is no problem at all, even with the
>PRX_END_DATE test (I just replace dateTest with its value).
>
>So my question is : how should I format the SQL command in VBA to
>allow the testing of PRX_END_DATE being >= to the current date,
>without getting the 3464 error message ?
>
>Thank you.

Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!

Re: Problem with date formatting in SQL command

On 16 oct, 01:24, Chuck Grimsby
wrote:
> Format the dateTest variable to YYYY-MM-DD format before enclosing it
> in the single quotes:
>
> strSQL = "SELECT ID_PRX, PRX_LIB FROM PRX_TABLE " & _
> "WHERE ID_PROJECT = 1000 AND ID_CYCLE <>2 AND " & _
> "PRX_END_DATE >= '" & Format(dateTest, "YYYY-MM-DD") & "'*
>
> If there's also a time value in dateTest, use "YYYY-MM-DD HH:NN:SS".
>

Hello,

Thank you for your answer.

If I just modify the command the way you suggested it and run the app,
I got a message about 'overflow', obviously because dateTest =
"20071016"

So I tried to make the following changes :

dateTest = CStr(Year(Now)) & "-" & CStr(Month(Now)) & "-" &
CStr(Day(Now)) ' dateTest now is "2007-10-16"
strSQL = "SELECT ID_PRX, PRX_LIB FROM PRX_TABLE " & _
"WHERE ID_PROJECT = 1000 AND ID_CYCLE <>2 AND " & _
"PRX_END_DATE >= '" & Format(dateTest, "YYYY-MM-DD") & "'

but i'm still having the same old 3464 error message.

It seems that Access still does not understand that the value after
"PRX_END_DATE >= '" is supposed to be a date, but I don't find a way
to make it work ...
I even tried with "PRX_END_DATE >= CAST(" & dateTest & " AS
DATETIME)" which is supposed to be the right syntax for SQL Server,
but still not working ...

Re: Problem with date formatting in SQL command

On Oct 16, 5:31 am, nartla wrote:
> On 16 oct, 01:24, Chuck Grimsby
> wrote:
>
> > Format the dateTest variable to YYYY-MM-DD format before enclosing it
> > in the single quotes:
>
> > strSQL = "SELECT ID_PRX, PRX_LIB FROM PRX_TABLE " & _
> > "WHERE ID_PROJECT = 1000 AND ID_CYCLE <>2 AND " & _
> > "PRX_END_DATE >= '" & Format(dateTest, "YYYY-MM-DD") & "'*
>
> > If there's also a time value in dateTest, use "YYYY-MM-DD HH:NN:SS".
>
> Hello,
>
> Thank you for your answer.
>
> If I just modify the command the way you suggested it and run the app,
> I got a message about 'overflow', obviously because dateTest =
> "20071016"
>
> So I tried to make the following changes :
>
> dateTest = CStr(Year(Now)) & "-" & CStr(Month(Now)) & "-" &
> CStr(Day(Now)) ' dateTest now is "2007-10-16"
> strSQL = "SELECT ID_PRX, PRX_LIB FROM PRX_TABLE " & _
> "WHERE ID_PROJECT = 1000 AND ID_CYCLE <>2 AND " & _
> "PRX_END_DATE >= '" & Format(dateTest, "YYYY-MM-DD") & "'
>
> but i'm still having the same old 3464 error message.
>
> It seems that Access still does not understand that the value after
> "PRX_END_DATE >= '" is supposed to be a date, but I don't find a way
> to make it work ...
> I even tried with "PRX_END_DATE >= CAST(" & dateTest & " AS
> DATETIME)" which is supposed to be the right syntax for SQL Server,
> but still not working ...

Surely
"PRX_END_DATE >= CAST(" & dateTest & " AS
> DATETIME)"
would generate a syntax error, or ask SQL to Cast the integer 20071016
as a date, and something like

CAST ('2007-10-16' AS DateTime)

or

CAST ('" & dateTest & "' AS DateTime)
single quote, double quote & dateTest & double quote, single quote
would be needed?

I use CAST ('2007-10-16' AS DateTime) extensively with no problems.
Then again I don't use ODBC, so this may not be applicable.

Re: Problem with date formatting in SQL command

On 16 oct, 12:41, lyle wrote:
> On Oct 16, 5:31 am, nartla wrote:
>
>
>
>
>
> > On 16 oct, 01:24, Chuck Grimsby
> > wrote:
>
> > > Format the dateTest variable to YYYY-MM-DD format before enclosing it
> > > in the single quotes:
>
> > > strSQL = "SELECT ID_PRX, PRX_LIB FROM PRX_TABLE " & _
> > > "WHERE ID_PROJECT = 1000 AND ID_CYCLE <>2 AND " & _
> > > "PRX_END_DATE >= '" & Format(dateTest, "YYYY-MM-DD") & "'*
>
> > > If there's also a time value in dateTest, use "YYYY-MM-DD HH:NN:SS".
>
> > Hello,
>
> > Thank you for your answer.
>
> > If I just modify the command the way you suggested it and run the app,
> > I got a message about 'overflow', obviously because dateTest =
> > "20071016"
>
> > So I tried to make the following changes :
>
> > dateTest = CStr(Year(Now)) & "-" & CStr(Month(Now)) & "-" &
> > CStr(Day(Now)) ' dateTest now is "2007-10-16"
> > strSQL = "SELECT ID_PRX, PRX_LIB FROM PRX_TABLE " & _
> > "WHERE ID_PROJECT = 1000 AND ID_CYCLE <>2 AND " & _
> > "PRX_END_DATE >= '" & Format(dateTest, "YYYY-MM-DD") & "'
>
> > but i'm still having the same old 3464 error message.
>
> > It seems that Access still does not understand that the value after
> > "PRX_END_DATE >= '" is supposed to be a date, but I don't find a way
> > to make it work ...
> > I even tried with "PRX_END_DATE >= CAST(" & dateTest & " AS
> > DATETIME)" which is supposed to be the right syntax for SQL Server,
> > but still not working ...
>
> Surely
> "PRX_END_DATE >= CAST(" & dateTest & " AS> DATETIME)"
>
> would generate a syntax error, or ask SQL to Cast the integer 20071016
> as a date



You should remember that in my case, dateTest is defined as a String
variable.

Re: Problem with date formatting in SQL command

On 16 oct, 13:36, nartla wrote:
> On 16 oct, 12:41, lyle wrote:
>
>
>
>
>
> > On Oct 16, 5:31 am, nartla wrote:
>
> > > On 16 oct, 01:24, Chuck Grimsby
> > > wrote:
>
> > > > Format the dateTest variable to YYYY-MM-DD format before enclosing =
it
> > > > in the single quotes:
>
> > > > strSQL =3D "SELECT ID_PRX, PRX_LIB FROM PRX_TABLE " & _
> > > > "WHERE ID_PROJECT =3D 1000 AND ID_CYCLE <>2 AND " & _
> > > > "PRX_END_DATE >=3D '" & Format(dateTest, "YYYY-MM-DD") & "=
'*
>
> > > > If there's also a time value in dateTest, use "YYYY-MM-DD HH:NN:SS".
>
> > > Hello,
>
> > > Thank you for your answer.
>
> > > If I just modify the command the way you suggested it and run the app,
> > > I got a message about 'overflow', obviously because dateTest =3D
> > > "20071016"
>
> > > So I tried to make the following changes :
>
> > > dateTest =3D CStr(Year(Now)) & "-" & CStr(Month(Now)) & "-" &
> > > CStr(Day(Now)) ' dateTest now is "2007-10-16"
> > > strSQL =3D "SELECT ID_PRX, PRX_LIB FROM PRX_TABLE " & _
> > > "WHERE ID_PROJECT =3D 1000 AND ID_CYCLE <>2 AND " & _
> > > "PRX_END_DATE >=3D '" & Format(dateTest, "YYYY-MM-DD") & "'
>
> > > but i'm still having the same old 3464 error message.
>
> > > It seems that Access still does not understand that the value after
> > > "PRX_END_DATE >=3D '" is supposed to be a date, but I don't find a w=
ay
> > > to make it work ...
> > > I even tried with "PRX_END_DATE >=3D CAST(" & dateTest & " AS
> > > DATETIME)" which is supposed to be the right syntax for SQL Server,
> > > but still not working ...
>
> > Surely
> > "PRX_END_DATE >=3D CAST(" & dateTest & " AS> DATETIME)"
>
> > would generate a syntax error, or ask SQL to Cast the integer 20071016
> > as a date
>
> You should remember that in my case, dateTest is defined as a String
> variable.- Masquer le texte des messages pr=E9c=E9dents -
>
> - Afficher le texte des messages pr=E9c=E9dents -

Hello,

I finally made it work : since my SQL command is being launched from
an Access VBA application (to a SQL Server), the syntax I have to use
is the following :

SELECT... WHERE ... AND PRX_END_DATE >=3D #" & Format(Now(), "dd/mm/
yyyy") & "#"

It seems that using # before and after the date is a specificity of
Access syntax.

Thank you to all those who tried to help me !

Re: Problem with date formatting in SQL command

nartla wrote in
news:1192534609.453969.106310@t8g2000prg.googlegroups.com:

> You should remember that in my case, dateTest is defined as a String
> variable.

VBA would know that. I would know that. You would know that. T-SQL would
not know that.

--
lyle fairfield