Error 3075 - Date

[SebH]

Hi,

I read a lot of topics about this via Google but I have not find the solution of my problem.
So, I am writing a SQL request with VBA and dates are the problem. Below a working code :

Qry_Where = " WHERE " & _
                "(Table1.[" & Champ_Date & "] > Table2.[" & Champ_Date & "] " & _
                "AND Table1.[" & Champ_Date & "] <= #19/11/2015# " & _
                "AND Table2.[" & Champ_Date & "] >= #31/10/2015#) "

Howether, when I want to go with variable for the Dates, it s not working any more, with the Error 3075 :

Qry_Where = " WHERE " & _
                "(Table1.[" & Champ_Date & "] > Table2.[" & Champ_Date & "] " & _
                "AND Table1.[" & Champ_Date & "] <= #" & CDate(Date_End) & "# " & _
                "AND Table2.[" & Champ_Date & "] >= #" & CDate(Date_Beg) & "#) "

The 2 variable Date_End and Date_Beg are Dates.

I have also tried Format(Date_End, "dd mm yyyy") and Cdate(Date_End).

Does someone know what I have done wrong ?
Thanks for your assistance !

 

[JHB]

Date in a criteria has to be in US format mm/dd/yyyy.
Try the below.

Qry_Where = " WHERE " & _
"(Table1.[" & Champ_Date & "] > Table2.[" & Champ_Date & "] " & _
"AND Table1.[" & Champ_Date & "] <= #" & Format(Date_End,"mm/dd/yyyy") & "# " & _
"AND Table2.[" & Champ_Date & "] >= #" & Format(Date_Beg, "mm/dd/yyyy") & "#) "

 

[SebH]

Hi,

Thanks for your reply.

I have tried and it does not work too.

I obtain the error :

Syntax error in date in query expression '(Table1.[NAV Date] > Table2.[NAV Date] AND Table1.[NAV Date] <= #11.19.2015# AND Table2.[NAV Date] >= #12.31.2014#'.

 

[sneuberg]

@JHB I'm wondering why the format function is producing a date with dots in it. It that perhaps a regional setting? But if dates in Geneva have dots why wouldn't they be that way in the database?

 

[sneuberg]

http://allenbrowne.com/ser-36.html seems to suggest that backslashes would help. I suggest trying.

Qry_Where = " WHERE " & _ 
"(Table1.[" & Champ_Date & "] > Table2.[" & Champ_Date & "] " & _ 
"AND Table1.[" & Champ_Date & "] <= #" & Format(Date_End,"mm\/dd\/yyyy") & "# " & _ 
"AND Table2.[" & Champ_Date & "] >= #" & Format(Date_Beg, "mm\/dd\/yyyy") & "#) "

 

[SebH]

Back slash have worked !!

Thanks you so much !! I would have never find this one !

[SOLVED]