Convert SQL Server Select to Select Statement for Access

[cr84net2]

I first posted this in SQL Programing (Not sure how to remove it from there)
I need to convert this SQL Select statement in to a select statement that will work with MS Access 2000. Any help would be appreciated.

strSQL = "SELECT * FROM eventCalendar WHERE " & _
"convert(datetime,convert(varchar,DatePart(yyyy, dteEventStart)) + '-' + " & _
"convert(varchar,DatePart(mm, dteEventStart)) + '-' + " & _
"convert(varchar,DatePart(dd,dteEventStart))) <= " & _ 
"'" & intYear & "-" & intMonth & "-" & y & "'" & " AND " & _ 
"convert(datetime,convert(varchar,DatePart(yyyy, dteEventEnd)) + '-' + " & _
"convert(varchar,DatePart(mm, dteEventEnd)) + '-' + " & _
"convert(varchar,DatePart(dd,dteEventEnd))) >= " & _
"'" & intYear & "-" & intMonth & "-" & y & "'" & _
"ORDER BY dteEventStart, dteEventEnd"

Thanks,

 

[johnwm]

Look on your computer for Access SQL help - it's default home is:
"C:\Program Files\Common Files\Microsoft Shared\OFFICE11\1033\JETSQL40.CHM"
Then look up the 'Between' keyword

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[cr84net2]

Thanks johnwm. I am familiar with using Between, however in this case I believe the Select Statement is using a function named DatePart (My first reaction was to place () after this until I realized it is a function). I am unfamiliar with Sql Server and can see where some changes need to be made, + to & etc. In researching this I found that the equivalent of Sql Server Convert(varchar,x) is cstr(x) but this and the other changes caused errors. I am not sure how to proceed from here. Any help would be appreciated.

 

[DotNetGnat]

ok here is the thing...

DatePart(yyyy,'11/18/2005') -> returns 2005
DatePart(mm,'11/18/2005') -> returns 11
DatePart(dd,'11/18/2005') -> returns 18

easy hah...

-DNG

 

[cr84net2]

Thanks, so I can abandon the convert(varchar, all together. I appreciate the help.

 

[DotNetGnat]

no...here is the thing...

convert(datetime,[red]convert(varchar,DatePart(yyyy, dteEventStart)[/red])

the part in red gives you a varchar....

and the outer convert converts that varchar back to datetime format...

-DNG

 

[cr84net2]

O.K., the light just went on, I have been trying to convert to much of this statement over so it would be useable with access. But much of it is fine, just some syntax changes are necessary. I am sorry, I am completely unfamiliar with SQL Server and its syntax. That is why you sugggested using between.

I just need to redo this so it finds events between dteEventStart and dteEventEnd. Thanks for simplifying this for me.

 

[DotNetGnat]

no problem..glad to be of help...post back if you get any more errors...we will take a look...

-DNG

 

[Sheco]

When you see code where a date field is converted to a string and then the string is converted back into a date....

...check and see if the purpose of all this is to get rid of the hours:mins:secs part of the date.