Syntax error on date field (1 Viewer)

[shery1995]

Hi All

I am trying the following line of code:

strCriteria = "[MatterDate] Between " & Format(Me.txtDateFrom, "\#dd\/mm\/yyyy\#) & " And " & Format(Me.txtDateTo, "\#dd\/mm\/yyyy\#")

But getting Syntax error message. Will much appreciate if someone can help to to sort it out.

Many thanks

 

[Minty]

You are missing one closing "

strCriteria = "[MatterDate] Between " & Format(Me.txtDateFrom, "\#dd\/mm\/yyyy\#") & " And " & Format(Me.txtDateTo, "\#dd\/mm\/yyyy\#")

 

[theDBguy]

Hi. That code will probably result in unexpected outcome anyway. Suggest using the format "yyyy-mm-dd" to avoid potential problems/issues.

 

[Gasman]

Also Debug.Print the variable to see the error ?

 

[shery1995]

Hi. That code will probably result in unexpected outcome anyway. Suggest using the format "yyyy-mm-dd" to avoid potential problems/issues.

Thank you for your reply. Format requirement is dd/mm/yyyy.

 

[shery1995]

You are missing one closing "

strCriteria = "[MatterDate] Between " & Format(Me.txtDateFrom, "\#dd\/mm\/yyyy\#") & " And " & Format(Me.txtDateTo, "\#dd\/mm\/yyyy\#")

Thank you for your reply. Can you please point where exactly closing is missing?

 

[MajP]

To save typing and get it correct look at the CSQL function

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[theDBguy]

Thank you for your reply. Format requirement is dd/mm/yyyy.

Unfortunately, data format is not the same as data value. Please see the following article for more info.

Microsoft Access tips: International Dates in Access

How to ensure dates are interpreted correctly in a Microsoft Access database, even when the user's regional settings are different from the US format.

allenbrowne.com

 

[Dreamweaver]

I use the below you need to format for a US date

int([EventDate]) BETWEEN " & Format(Me![txt_From], "\#" & "mm/dd/yyyy" & "\#") & " AND " & Format(Me![txt_To], "\#" & "mm/dd/yyyy" & "\#")

 

[shery1995]

Unfortunately, format is not the same as data value. Please see the following article for more info.

Microsoft Access tips: International Dates in Access

How to ensure dates are interpreted correctly in a Microsoft Access database, even when the user's regional settings are different from the US format.

allenbrowne.com

Many thanks

 

[theDBguy]

Many thanks

You're welcome. I've run into the same issue before. Cheers!

 

[shery1995]

I use the below you need to format for a US date

int([EventDate]) BETWEEN " & Format(Me![txt_From], "\#" & "mm/dd/yyyy" & "\#") & " AND " & Format(Me![txt_To], "\#" & "mm/dd/yyyy" & "\#")

Many hanks

 

[Gasman]

I can never quite remember the correct syntax and use this that I found on the web.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

 

[Pat Hartman]

Using FORMAT() to format a date turns it into a string. Fine for display purposes or when you need to pass a date to SQL Server and it KNOWS it's getting a date. If you format a date into a string, then 01/02/20 is going to be less than 02/01/20 and that would be incorrect if the string were dd/mm/yy.

Strings are evaluated character by character, left to right. So, 2 is > 10000000 if the fields are strings.