How to convert access query to sql server query string?

Question

how to converse access query to sql server query string by programing?

example for access query string

SELECT dbo_VNMST.VISITDATE, dbo_VNTREAT.TREATMENTCODE, dbo_VNMST.HN, dbo_VNMST.VN, dbo_VNTREAT_1.TREATMENTCODE, Count(dbo_VNMST.HN) AS CountOfHN, dbo_PATIENT_NAME.SUFFIX, Mid([firstname],2) AS FIRSTNAME1, Mid([lastname],2) AS LASTNAME1, (FIRSTNAME1+' '+LASTNAME1) AS FULLNAME
FROM (((dbo_VNMST INNER JOIN dbo_VNTREAT ON (dbo_VNMST.VISITDATE = dbo_VNTREAT.VISITDATE) AND (dbo_VNMST.VN = dbo_VNTREAT.VN)) INNER JOIN dbo_VNPRES ON (dbo_VNMST.VISITDATE = dbo_VNPRES.VISITDATE) AND (dbo_VNMST.VN = dbo_VNPRES.VN)) INNER JOIN dbo_VNTREAT AS dbo_VNTREAT_1 ON (dbo_VNMST.VISITDATE = dbo_VNTREAT_1.VISITDATE) AND (dbo_VNMST.VN = dbo_VNTREAT_1.VN)) INNER JOIN dbo_PATIENT_NAME ON dbo_VNMST.HN = dbo_PATIENT_NAME.HN
GROUP BY dbo_VNMST.VISITDATE, dbo_VNTREAT.TREATMENTCODE, dbo_VNMST.HN, dbo_VNMST.VN, dbo_VNTREAT_1.TREATMENTCODE, dbo_PATIENT_NAME.SUFFIX, Mid([firstname],2), Mid([lastname],2)
HAVING (((dbo_VNMST.VISITDATE) Between #9/1/1466# And #9/14/1466#) AND ((dbo_VNTREAT.TREATMENTCODE)="3964") AND ((dbo_VNTREAT_1.TREATMENTCODE)="92H") AND ((dbo_PATIENT_NAME.SUFFIX)=0));

Answer 1

You'll need to use the SUBSTRING function instead of the MID function, replace double quotes with a single quote and remove the hash (#) identifier for your BETWEEN statement.

Answer 2

In addition to what Kane posted, you'll also need to replace the underscore ("_") between dbo and the table name with a period ("."), e.g.:

SELECT dbo_VNMST.VISITDATE

will become

SELECT dbo.VNMST.VISITDATE