DatePart in sql not working

[EddyLLC]

I want to return all records that have the RenewalDte falling in the 6th month from the month the sql is run. I've figured it as a query in Access 2007 but can't seem to get it to work as an sql statement. Hopelessly lost, any help is appreciated. The statement the won't work is below.

strSql = = CurrentDb
strSql = "SELECT Owners.Owner, Owners.FOorSA, Owners.NameFirst, Owners.NameLast, " _
& "Owners.OState, Renewal.SalesLastYr, Renewal.YrToDate, " _
& "Renewal.LastYrToDte, Renewal.RenewalDte, Renewal.NewRate, " _
& "Renewal.Rate, Renewal.LTM, Renewal.STARRate, Owners.Notes " _
& "FROM Owners INNER JOIN Renewal ON Owners.Owner = Renewal.Comp " _
& "WHERE FOorSA = '" & strOwner & "'" AND ((Year(RenewalDte)*12+DatePart("m",RenewalDte))=Year(Date())*12+DatePart("m",Date())+6))

 

[Darrylles]

Eddy,

Have you pasted the code 'as-is'?

I don't understand:

1) strSql = = CurrentDb
2) & "WHERE FOorSA = '" & strOwner & "'" AND (with no _ )

(the _ tells the compiler that the next line is part of this line)

ATB

Darrylles



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[EddyLLC]

Thanks for the reply. Sorry about that. I've been at it too long. Here is the actual code up till I try to open the record set. I haven't finished it because I can't get that far.

Dim strSql As String
Dim dbsDB As DAO.Database
Dim rs As DAO.Recordset
Dim strOwner As String

strOwner = "SA"
Set dbsDB = CurrentDb
strSql = "SELECT Owners.Owner, Owners.FOorSA, Owners.NameFirst, Owners.NameLast, " _
& "Owners.OState, Renewal.SalesLastYr, Renewal.YrToDate, " _
& "Renewal.LastYrToDte, Renewal.RenewalDte, Renewal.NewRate, " _
& "Renewal.Rate, Renewal.LTM, Renewal.STARRate, Owners.Notes " _
& "FROM Owners INNER JOIN Renewal ON Owners.Owner = Renewal.Comp " _
& "WHERE FOorSA = '" & strOwner & "'" AND ((Year(RenewalDte)*12+DatePart("m",RenewalDte))=Year(Date())*12+DatePart("m",Date())+6))"

Set rs = dbsDB.OpenRecordset(strSql, dbOpenDynaset)

 

[Darrylles]

Eddy,

OK - you got your recordset defined in rs.

What isn't 'working'?

ATB

Darrylles

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[EddyLLC]

I receive a Compile error: Expected: end of statement error and the last ")" is highlighted. If I remove it I get the same error but now the last quotation mark is highlighted.

 

[Darrylles]

Eddy,

First replace the double-quotes related to the DATEPART function with single quotes.

Remember that in VBA - double quotes enclose a string, when a function like DATEPART uses double-quotes - you must replace those with single-quotes or else VBA get's confused.

So:

DatePart("m",Date()) = DatePart('m',Date())

Try this first - let me know...

ATB

Darrylles



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Andrzejek]

Is the field RenewalDte declared as Date in your DB?

And if today's date is 10/27/2011, you want all records that have RenewalDte of 4/27/2012 ?

Have fun.

---- Andy

 

[PHV]

Replace this:
& "WHERE FOorSA = '" & strOwner & "'" AND ((Year(RenewalDte)*12+DatePart("m",RenewalDte))=Year(Date())*12+DatePart("m",Date())+6))"
with this:

& "WHERE FOorSA='" & strOwner & "' AND Year(RenewalDte)*12+Month(RenewalDte)=Year(Date())*12+Month(Date())+6"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[EddyLLC]

Darrylles- I made your suggested changes, replacing the quotes with singles around the m in DatePart('m', Date()+5) I received another compile error: Expected Expression with the single quote preceding the m highlighted.

Andrzejek - Yes the RenewalDte is declared as a Date in the db with a format of Medium Date.

PHV - I tried your change and it did create a record set but oddly it only contains 1 record when in fact it should contain 8.

 

[EddyLLC]

You were correct PHV. Your suggestion worked. I had not idea I had to do a rs.MoveLast before the rs.RecordCount would give me a true record count in the record set.

Sorry for being slow and thanks again to all for the help.

Eddy

 

[PHV]

had not idea I had to do a rs.MoveLast
So, next time you'll post all the relevant code ...

 

[EddyLLC]

So, next time you'll post all the relevant code..."

I'm not sure what this means. I posted all my code. I checked out the record count in the immediate window after opening the record set with the suggestion PHV gave me. I was searching other sites when I saw someone had used the MoveLast. I decided to try it before stopping the code and doing another record count. That's when I realized the record set was complete.

If I wasn't clear in my explanation, I apologize and as always thanks for all the help.