Like Operator problem

[anil]

Hi all
i am facing this Like Operator problem in VBA.Please help in the
mistake

str="INSERT INTO tblnewone ( LocationName, SiteID, SiteAddress,
SiteCode, BDate )" & _
" SELECT tblLocation.LocationName, tblSite.SiteID,
tblSite.SiteAddress, tblSite.SiteCode, " & _
" Int(DMax('Adate','tblLog','((SiteCode) Like '***-***-S-***') And
((SampleTakenForID) = 1)'))+7 AS SDate" & _
" FROM tblLocation INNER JOIN tblSite ON tblLocation.LocationID =
tblSite.LocationID" & _
" WHERE (((tblSite.SiteCode) Like '***-***-S-***') AND
((tblSite.SiteType)='S'));
Docmd.runsql str con1

I am getting syntax error in
Int(DMax('Adate','tblLog','((SiteCode) Like '***-***-S-***') And
((SampleTakenForID) = 1)'))+7

I have tried using ....like'%S%' ......, but no success.
I have tried using ....like'*' & 'S' & '*' ......, but no success.

can some one please help me in the error.
It must be Like operator error,as this query runs fine as access query

Thanks
anil

 

[Damian S]

Hi anil,

The problem is with the single quotes - you are using them to both delimit
the entire expression, and also the '***-***' etc inside the expression...
try this:

" SELECT tblLocation.LocationName, tblSite.SiteID, tblSite.SiteAddress,
tblSite.SiteCode, " & _
Int(DMax("Adate","tblLog","((SiteCode) Like '***-***-S-***') And
((SampleTakenForID) = 1)"))+7 & " AS SDate"

Damian.

 

[anil]

Hi Damien
Thanks for your help.
Actually " ..." was giving error when used inside string in VBA
as str = " Select .... Int(Dmax("....")"
So I tried using 2 times ' as ' ' ***-***-S-*** ' ' and it worked,but
I did not know why.
but It solved my problem.
Anil