Date format issue in where clause

[Bainwash]

Dear all,

I have a WHERE clause I'd like to parametrise but I'm getting some overflow problems when I want to do this.

Original where clause (which was working ok was) :
strWhereClause = " where (((" & strTablename & ".bg_detection_date) > #8/JAN/2005#)) and (((" & strTablename & ".bg_detection_date) < #8/JUL/2005#))"

Now I wanted to parametrise the where clause so that a period of 6 months is taken, initially I used this :

strWhereClause =" where (((" & strTablename & ".bg_detection_date) > #" & DateAdd("m", -6, Date) & "#)) and (((" & strTablename & ."bg_detection_date) < #" & DateAdd("m", -6, Date)

I experienced that when I executed this it ran well but the month and date were inverted so that I had much less results than I should have got.

Therefore I started changing the syntax adding some format function, unfortunately this results in overflow errors. Eg. : this one gives an overflow :

" where (((" & strTablename & ".bg_detection_date) > #" & Format(DateAdd("m", -6, Date), d / mmm / yyyy) & "#)) and (((" & strTablename & ".bg_detection_date) < #" & Format(Date, d / mmm / yyyy) & "#))"


Can anybody advise me on how to change the syntax of this where clause in order to avoid the overflow error and get the correct results ?

Thanks !

Jan

 

[Jon K]

strWhereClause = " where (((" & strTablename & ".bg_detection_date) > #" & Format(DateAdd("m", -6, Date), "m/d/yyyy") & "#)) and (((" & strTablename & ".bg_detection_date) < #" & Format(Date, "m/d/yyyy") & "#))"
.

 

[Bainwash]

Jon K,

Thanks !
A missing double quote indeed makes the difference.

Regards,

Jan