Problem with date formatting in SQL command

[nartla]

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.

[nartla]

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

[Salad]

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.

[Chuck Grimsby]

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 <br**********@altran.com>
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!

[nartla]

On 16 oct, 01:24, Chuck Grimsby <[e-mail]>
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 ...

[lyle]

On Oct 16, 5:31 am, nartla <[e-mail]:

On 16 oct, 01:24, Chuck Grimsby <[e-mail]>
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.

[nartla]

On 16 oct, 12:41, lyle <[e-mail]:

On Oct 16, 5:31 am, nartla <[e-mail]:

On 16 oct, 01:24, Chuck Grimsby <[e-mail]>
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 & " ASDATETIME)"

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.

[nartla]

On 16 oct, 13:36, nartla <[e-mail]:

On 16 oct, 12:41, lyle <[e-mail]:

On Oct 16, 5:31 am, nartla <[e-mail]:

On 16 oct, 01:24, Chuck Grimsby <[e-mail]>
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 & " ASDATETIME)"

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écédents -

- Afficher le texte des messages précédents -

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 >= #" & 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 !

[lyle fairfield]

nartla <br**********@altran.comwrote in
news:11**********************@t8g2000prg.googlegro ups.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