Translating a query from SQL Server to the Backend of Access

[teneesh]

I'm trying to get the sqltext query below in a format that VB will accept, it is coded on a button in access. When I type it in, with all quotes around it along with the " & _ things, I still get errors. I'm wondering if i'm missing something because of the internal parentheses. Please help. here is the query. I've taken out all of the quotes and other connotations that didn't help.
thanks in advance.

Dim sqltext As String
 
sqltext = select * from " & _
(Select type,shortname as Ward, abbrev,email,schedule.rotation, schedule.acyear, dbo.idtoname(schedule.eid) name, schedule.eid , min(startdate) start, " & _
 
sum(DateDiff(d, startdate, enddate) + 1) As nodays " &_
 
from schedule, serviceward, employee " & _
 
where schedule.srvcode = serviceward.srvcode
 
and schedule.eid = employee.eid
 
and hosp='U' and type=1 and schedprt=1
 
and acyear = " & Me.CbxAcyear" and rotation between " & Me.cbxMonth" -1 and " & Me.CbxMonth" and stfgroup=1
 
 
 
Group by type, shortname, schedule.eid, email, schedule.rotation, schedule.acyear, abbrev
 
) A
 
where nodays >= 10
 
and datepart(ww,start) = datepart(ww,getdate())+1

[Delerna]

What, exactly, are the error messages that you are getting. It will help narrow down the error if we know what error is occuring.

One immediate thing I can see is in line 3




there is a missing quote
it should be

   sqltext = "select * from " & _
 
 

[NeoPa]

Teneesh, A couple of important points.

• Always use [ CODE ] tags when posting code. This is not optional and you're a full member now so you should know this (The rules are not hidden).
• When posting code for review, always make sure that you copy/paste the actual code tested. This has not been tested as posted as it couldn't possibly run (or even compile). Trimming the code is a good thing, but if it means we're looking at code that's never been checked to see if it does what you describe, then that extra effort is more than wasted.
• Always compile code before posting it.
• If there are compile errors then report the error fully and say which line (in the post - not in the module) that the error occurs on.

If you follow these simple instructions you'll find that your questions are answered more often and more correctly (and there won't be so many requests for extra information).