Date search error

[saranmc]

I just cant find the error in an SQL Statement that I have.

I am using Acess 2010. Im trying to search in a table from a form using date as the search Parameter.

"SELECT * INTO [new] FROM [projectsearch]" & _
        " WHERE [projectsearch].[Delivery Date] BETWEEN #" & Me.datefrom & "# and #" & Me.dateto & "#;"

I have two text boxes with a date picker in the form.

But when I run this SQL I get an error stating Syntax error in the date.

Does anybody see where the error lies.

Thanks in advance.

[MikeTheBike]

Hi

you could try this

"SELECT * INTO [new] FROM [projectsearch]" & _
         " WHERE [projectsearch].[Delivery Date] BETWEEN #" & Format(Me.datefrom,"mm/dd/yyyy") & "# and #" & Format(Me.dateto,"mm/dd/yyyy") & "#;"

The dates should be formatted as strings in sql statements, and in American or universal date format.
HTH

MTB

[saranmc]

thanks you for your answer!
what happens when I try it is that, my date Format is being changed.
For example if I enter 01.03.2016, it becomes 03.01.2016 in the SQL. this is wrong. It is searching for the wrong values.

[PhilOfWalton]

Try changing the date format to "mmm-dd-yyyy"
That should always show month 3 as Mar

Phil

[MikeTheBike]

Well I have 2 comments
1) I incorrectly assumed that datefrom etc. are actually dates
2) I have never used periods(.) as separator in a date, I have always a slash (/) from well before computer where in the public consciousness, but it would seem people has started using a period and I have no idea why.
This practice also plays havoc in Excel if you want to use it in a calculation/function.

So, using a slash may solve (part) of the problem??

MTB