quotes on B/E of access converting to sql server

[teneesh]

I've created a button that I want to produce a report in Access. The code goes as follows:
Dim sqltext As String
sqltext = "SELECT interviewdate,candgroup,lastname,firstname,middlen ame," & _
"program,s.roomno,dbo.idtoname(eid) AS interviewer,title,intdatetime " & _
"FROM InvwCandidate c, InvwSchd s, InvwInterviewer i , InvwInterviewerSchd v" & _
"where c.candrecno=s.candrecno" & _
"and v.recno = s.invwschrecno" & _
"and v.invwerID=i.invwerID" & _
"and c.acyear=" & Me.ComboAcYear & _
Switch(Me.ComboProgram = "M", " and program='M'", Me.ComboProgram = "P", " and program='P'") & _
" and InterviewDate between '" & Me.ComboFromDate & "' and '" & _
Me.ComboToDate & "' ORDER BY InterviewDate,intdatetime,lastname,firstname"
Debug.Print sqltext
************************************************** ***************
When I do a crtl G to look at the code, it returns:
SELECT interviewdate,candgroup,lastname,firstname,middlen ame,program,s.roomno,dbo.idtoname(eid)
AS interviewer,title,intdatetime FROM InvwCandidate c, InvwSchd s, InvwInterviewer i , InvwInterviewerSchd vwhere c.candrecno=s.candrecnoand v.recno = s.invwschrecno
and v.invwerID=i.invwerIDand c.acyear=2008
and program='M'
and InterviewDate between '11/13/2007' and '11/13/2007'
ORDER BY InterviewDate,intdatetime,lastname,firstname
************************************************** ************************
notice it combines the letter 'v' as the nickname for the table InvwerInterviewerSchd and the word 'where' and then it does it again with the word 'and' and the word right before 'and', s.candrecno.

I think its a quote issue, but it looks correct to me. Please advise me on how to get rid of the two words combining.
Thanks,
t

[Stwange]

I've created a button that I want to produce a report in Access. The code goes as follows:
Dim sqltext As String
sqltext = "SELECT interviewdate,candgroup,lastname,firstname,middlen ame," & _
"program,s.roomno,dbo.idtoname(eid) AS interviewer,title,intdatetime " & _
"FROM InvwCandidate c, InvwSchd s, InvwInterviewer i , InvwInterviewerSchd v" & _
"where c.candrecno=s.candrecno" & _
"and v.recno = s.invwschrecno" & _
"and v.invwerID=i.invwerID" & _
"and c.acyear=" & Me.ComboAcYear & _
Switch(Me.ComboProgram = "M", " and program='M'", Me.ComboProgram = "P", " and program='P'") & _
" and InterviewDate between '" & Me.ComboFromDate & "' and '" & _
Me.ComboToDate & "' ORDER BY InterviewDate,intdatetime,lastname,firstname"
Debug.Print sqltext
************************************************** ***************
When I do a crtl G to look at the code, it returns:
SELECT interviewdate,candgroup,lastname,firstname,middlen ame,program,s.roomno,dbo.idtoname(eid)
AS interviewer,title,intdatetime FROM InvwCandidate c, InvwSchd s, InvwInterviewer i , InvwInterviewerSchd vwhere c.candrecno=s.candrecnoand v.recno = s.invwschrecno
and v.invwerID=i.invwerIDand c.acyear=2008
and program='M'
and InterviewDate between '11/13/2007' and '11/13/2007'
ORDER BY InterviewDate,intdatetime,lastname,firstname
************************************************** ************************
notice it combines the letter 'v' as the nickname for the table InvwerInterviewerSchd and the word 'where' and then it does it again with the word 'and' and the word right before 'and', s.candrecno.

I think its a quote issue, but it looks correct to me. Please advise me on how to get rid of the two words combining.
Thanks,
t

The problem here is that the &_ only concatenates the strings on the two lines
as though there was no gap between the last character of the first string and the first character of the second string. All you need to do is add a space to either the end of the first string or the beginning of the second string.
Eg.

" and v.recno = s.invwschrecno" & _
" and v.invwerID=i.invwerID" & _
 

hope this helps.