Filter for date syntax error

[jera]

Hey guys!

So I have this form where I want to filter data by a date. At this point, I don't really care if entries at exactly this date, after or before the date are displayed. I just want it to work.

Dim filter As String
If Text27.Value <> "" Then
   filter = "[Baustellenstart] = #" & CDate(Text27.Value) & "#"
End If
Me.filter = filter
Me.FilterOn = True

The code however works when I filter by other fields which are strings or numbers, it's just about the "filter =" line.
The entity "Baustellenstart" is of the type date/time and has the format short date which should correlate with the date entered (either as 1.1.2011 or 01.01.2011 which both make 01.01.2011 when converted to date by the CDate() function)

So I always get a run-time error 3075: syntax error in date in query expression but have absolutely no clue why.

I'd be desperately grateful for suggestions!
Thanks in advance!
-jera-

 

[llkhoutx]

Use the debugger to determine the value of Text27.Value. It may not be a date.

 

[boblarson]

If your date is formatted with the periods, you need to convert to what Access wants (thank Microsoft for that one):

filter = "[Baustellenstart] = " & Format(Text27.Value), "\#mm\/dd\/yyyy\#")

 

[jera]

Thanks for your replies!

@llkhoutx: Text27.Value is indeed not a date, that's why I have to convert it. After conversion it has the beautiful german format dd.mm.yyyy.

@boblarson: As I see it the format including periods is what Access wants. I configured the field [Baustellenstart] as date with exactly this format with periods. It works fine with periods when I open the table window and search for an entry in the [Baustellenstart] field. I can simply type in "1.1.1998" and it is converted to #01.01.1998# and that's what I do in my query.

However, I'm not in the office until in a few hours but will try the solution as soon as I get there and let u know what happened thanks!

 

[jera]

Oh you're one wicked boblarson! And if you could tell me why the hell this one worked you'd be my hero too!

What I don't really get is why Access is obviously not able to work with the format I set for the field [Baustellenstart] type date ..

But anyway, thank you so much for the help, u made my day ..

 

[boblarson]

Oh you're one wicked boblarson! And if you could tell me why the hell this one worked you'd be my hero too!

What I don't really get is why Access is obviously not able to work with the format I set for the field [Baustellenstart] type date ..

But anyway, thank you so much for the help, u made my day ..

I think if you read this it will clear up some things for you:
http://allenbrowne.com/ser-36.html

 

[jera]

Yeah ok, so I read from that that Microsoft is not to be understood

Thanks for the help!