Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Convert SQL Server Select to Select Statement for Access

Status
Not open for further replies.

cr84net2

Programmer
Feb 8, 2005
93
US
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.


Code:
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,
 
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
 
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.
 
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

 
Thanks, so I can abandon the convert(varchar, all together. I appreciate the help.
 
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
 
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.
 
no problem..glad to be of help...post back if you get any more errors...we will take a look...

-DNG
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top