Syntax error with Dates where clause

Thank you!

Staying with Access SQL, for starters I see only two #'s and since there are two dates in your query you'll need four.

Private Sub cdmRunReport_Click()
Dim strWhere As String
 
strWhere = "[CreatedDate] Between #" & Format(Me.txtDateFrom, "dd/mm/yyyy") & "# And #" & Format(Me.txtDateTo, "dd/mm/yyyy") & "#"")"

DoCmd.OpenReport "rptScanningReport", acViewPreview, , strWhere
End Sub

Select allOpen in new window

Also be advised using BETWEEN with dates only means you won't pull any rows from the txtDateTo where there is a time component other than midnight.

BTW,

Access processes dates in the format mm/dd/yyyy, so I think, sticking with Access, you would need.  And I agree with Jim regarding use of between.  If [CreatedDate] contains a time component, then you should use >= and < 

strWhere = "([CreatedDate] >= #" & Format(Me.txtDateFrom, "mm/dd/yyyy") & "#) And " _
                 & "([CreatedDate] < #" & Format(DateAdd("d", 1, Me.txtDateTo), "mm/dd/yyyy") & "#)"

HTH
Dale

No points please.  Just a little more clarification.  Dates are stored internally as double precision numbers with the integer part being the number of days since the 0 date which in the case of Access is 12/30/1899 (SQL Server and Excel use a different origin date but otherwise the concept is the same).  The decimal is the fraction of time past midnight.  Given that, it is always best to simply leave dates as dates and not format them at all since using the Format() function turns them into strings and that forces Access, etc. to treat them as strings rather than as dates.

So, in a querydef you would say:
Where dateA Between DateB and DateC.  As long as DateB and DateC are both defined as datetime data types (even when they are form field references), no conversion is necessary or desired.

However, where you run into an issue is when you build SQL strings in code.  Because the string is implicitly a string, the dates are always strings and not datetime data tyoes.  When you use a string date in a query, it MUST be in mdy or ymd order.  mm/dd/yyyy is the "standard that SQL Server uses but it will also recognize yyyy/mm/dd since it is unambiguous (and therefore my preference in a multi-country environment).  Using dd/mm/yyyy format in this environment leads to ambiguity and errors.  

So - when your date must be a string and you are using it in a query, then use either yyyy/mm/dd format or mm/dd/yyyy.

mm/dd/yyyy is the "standard that SQL Server uses but it will also recognize yyyy/mm/dd since it is unambiguous

Just a correction, Pat. This is not the standard for SQL Server. That depends on how it is configured. You can get the SQL Server configuration by running the following command:

exec sp_configure 'default language'

Select allOpen in new window

If you want to use a format that will always work in any SQL Server instance then use 'YYYYMMDD'. This will present the date as string and SQL Server engine will perform an implicit conversion to date.

I can't really disagree with you Vitor since I'm in the US and our standard date part order is mdy, but I think that sp probably affects how dates are displayed.  It doesn't alter the internal workings of SQL Server.  If it did, no one who lived in countries that used date order other than mdy would ever have a problem.  I also mentioned that I recommend using yyyy-mm-dd format when working with string dates.  At least you can properly sort and compare string dates in the format yyyy-mm-dd whereas you can't when you use the other string formats.

I don't disagree with the decision to award points to Vitor.

However, the OP does not state whether he/she is using a pass-through query or is using linked tables and simply querying those tables for his/her report.  If the user is using linked tables, rather than a pass-through query, for the report then the syntax suggested by Vitor will not work, and the use of # to offset the dates would be necessary.

I disagree.  Dale's answer is more on point.  It uses the correct Access syntax (this is a where argument in an OpenReport method after all, and he got the date field order correct.  I'm going to guess that if the RecordSource of the report is a pass-through query, Access will apply the where clause locally rather than attempt to modify the pas-through query prior to sending it so Access syntax would be correct.