hi,

i got an error while running a sql query in VB. The query is:

rs1.Open "Select * from arcusfil_sql where convert(datetime,user_def_fld_3) > '" & _
Format(DateAdd("d", 30, Date), "dd/mm/yy") & "'", Conn

when compile i receive this error message:

Undefined function "convert" in expression.

I'm using SQL 2000 and user_def_fld_3 is a char(8) by defination.

Thanks.
John

Try using:

rs1.Open "Select * from arcusfil_sql where convert(datetime,user_def_fld_3) > " & _
" Format(DateAdd(d, 30, Date), 'dd/mm/yy') ", Conn

Taking away the double quote will cause the 'd' to be not defined. Thus the error "variable not defined" appear.

As you can see, I'm using VB to run this SQL statement. Any other idea?

Thanks,

John

That's not supposed to cause an error because the syntax for dateadd is
DATEADD(datepart, number, date)
where the abbreviation for datepart can be day,dd or d (see Books Online). To make sure, just type DATEADD(d, 30, getdate()) in Query Analyser and see that it passes, so I think that the problem is somewhere else.
Format function doens't exists in SQL2000. Try using CONVERT or CAST, like, for instance:

CONVERT(VARCHAR, DATEADD(d, 30, your_date), 103)

Bottom line, the SQL statement to pass to SQL Server (BTW you can store it in a VB string var) would be:

Dim strSQL as String
strSQL = "Select * from arcusfil_sql where convert(datetime,user_def_fld_3) > CONVERT(DATETIME, DATEADD(d, 30, " & _
your_vb_date_var & "), 103)"

rs1.Open strSQL, Conn

ok, i think my problem lies back to the initial problem. That is" undefined function "convert" in expresion.

I can run the sql statement in query analyzer but using VB this problem hit out. I tested a lot of sql functions in vb and they can work nicely but this convert function is otherwise. Is there any other alternative for me?

User_def_fld_3 is char type and have the date data. i need to select records that is almost expired(30 days) and already expired.

Thanks for yur help.

John