VB ODBC Connect String too SQL Server 2000 1

[xtreme69]

I'm very new too SQL server, having recently moved up from Access. The majority of my transition has gone smoothly, but I am having one issue that I am not quite sure how too handle.

Set rst = dbs.OpenRecordset("SELECT *, instr([status],'Deleted') as s_del, instr([status],'Cancelled') as s_can FROM Workorders WHERE EntryDate > #" & DateAdd("m", -6, Date) & "# AND InstallDate = null OR InstallDate > #" & DateAdd("d", -30, Date) & "#&quot

This connection string is causing me problems, and I'm assuming it has too do with the SQL server backend (since it worked fine before the change). I think those functions used in the statement are geared toward the Jet DB engine and that's why I'm having problems.

Anyway, I'm looking for the translation of this statement into something that will work with SQL Server. Eventually I would like too move some queries like this one over too stored procedures, but I am still shaky on the data interchange between the VB code and the procedure...so for now I'm hoping too be able too stick with this. Any help or thoughts are appreciated.

 

[xtreme69]

Btw, pardon the misnomer in the subject, I'm a little frazzled today with this stuff

 

[mwolf00]

The date delimiter in SQL Server is ' instead on #


Set rst = dbs.OpenRecordset("SELECT *, instr([status],'Deleted') as s_del, instr([status],'Cancelled') as s_can FROM Workorders WHERE EntryDate > '" & DateAdd("m", -6, Date) & "' AND InstallDate = null OR InstallDate > '" & DateAdd("d", -30, Date) & "'&quot

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[mwolf00]

Another option is to use the getDate() function of SQL Server...

Set rst = dbs.OpenRecordset("SELECT *, instr([status],'Deleted') as s_del, instr([status],'Cancelled') as s_can FROM Workorders WHERE EntryDate > dateAdd(m,-6,getDate()) AND InstallDate = null OR InstallDate > getDate()-30&quot

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[xtreme69]

Thanks guys, I'll be sure too try an isolated call with date restrictions using what you've told me (I've already made sure that at least "SELECT * FROM Workorders" works fine on it's own).

Problem is both the date restrictions and the Instr() part of the statement seem too be causing this error. I'll make the necessary changes too the date calls, but any thoughts on the InStr() problem?

 

[xtreme69]

Btw, changing the "#" over too "'" worked great. And thanks also for the tip on the getDate() function...I'll get myself converted yet

 

[mwolf00]

overlooked that, it should be

CHARINDEX ([status] ,'Deleted')

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[xtreme69]

Damn you're good! Thanks a ton for the help, really appreciate it.