between...and operation help........ sql newbie

[andrest]

Hi I'm trying to query a database using a BETWEEN operation to choose between to dates.

adoAllJobs.CommandType = adCmdText
adoAllJobs.RecordSource = "SELECT Jobs.JobNumber,Jobs.Date, Clients.BussinessName, Jobs.ProjectName, Jobs.Salesman, Jobs.SubTotal, Jobs.GST, Jobs.Total, Jobs.Balance FROM Jobs, Clients WHERE Jobs.ClientID = Clients.ClientID AND Jobs.Date BETWEEN #8/1/2002# AND #8/31/2002#"
adoAllJobs.Refresh

But it does not work when the dates are replaced by variables. Something like this......

adoAllJobs.CommandType = adCmdText
adoAllJobs.RecordSource = "SELECT Jobs.JobNumber, Jobs.Date, Clients.BussinessName, Jobs.ProjectName, Jobs.Salesman, Jobs.SubTotal, Jobs.GST, Jobs.Total, Jobs.Balance FROM Jobs, Clients WHERE Jobs.ClientID = Clients.ClientID AND Jobs.Date BETWEEN #FisrtDayOfMonth# AND #LastDayOfMonth#"
adoAllJobs.Refresh

can someone give me a hand.

andrest

 

[CajunCenturion]

I think you might want to make a slight change in your syntax. Something like the following

" ...BETWEEN #" & FisrtDayOfMonth& "# AND #" & LastDayOfMonth & "#"

Your original syntax put the name of variable in the SQL statement, and not the value of the variable. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[andrest]

Ok... i finally got it going.... thanks guys....