By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,321 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Problem with date formatting in SQL command

P: n/a
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.

Oct 15 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a


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

Oct 15 '07 #2

P: n/a
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.
Oct 15 '07 #3

P: n/a

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!
Oct 15 '07 #4

P: n/a
On 16 oct, 01:24, Chuck Grimsby <c.grim...@worldnet.att.net.invalid>
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 ...


Oct 16 '07 #5

P: n/a
On Oct 16, 5:31 am, nartla <bruno.bar...@altran.comwrote:
On 16 oct, 01:24, Chuck Grimsby <c.grim...@worldnet.att.net.invalid>
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.


Oct 16 '07 #6

P: n/a
On 16 oct, 12:41, lyle <lyle.fairfi...@gmail.comwrote:
On Oct 16, 5:31 am, nartla <bruno.bar...@altran.comwrote:


On 16 oct, 01:24, Chuck Grimsby <c.grim...@worldnet.att.net.invalid>
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.

Oct 16 '07 #7

P: n/a
On 16 oct, 13:36, nartla <bruno.bar...@altran.comwrote:
On 16 oct, 12:41, lyle <lyle.fairfi...@gmail.comwrote:


On Oct 16, 5:31 am, nartla <bruno.bar...@altran.comwrote:
On 16 oct, 01:24, Chuck Grimsby <c.grim...@worldnet.att.net.invalid>
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 !


Oct 16 '07 #8

P: n/a
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
Oct 16 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.