2007 to 2003 function problem

[Eugenios]

Hello everyone. I've been working on a database in MSAccess2007 and had to convert it to 2003. I have a line of code which returns a requested date range from a query to a listbox:

Dim MySQL As String
MySQL = "SELECT * FROM qryContLife  WHERE 1=1"

If IsDate(Me![StartDate]) And IsDate(Me![EndDate]) Then
   If CDate(Me![StartDate]) < CDate(Me![EndDate]) Then
      mycriteria = mycriteria _
        & " AND [Depot In Date] Between #" _
        & Format(Me![StartDate], "yyyy/mm/dd") _
        & "# And #" _
        & Format(Me![EndDate], "yyyy/mm/dd") & "# "
   End If
End If

MyRecordSource = MySQL & mycriteria
Me![lstContList].RowSource = MyRecordSource

After converting, it stopped working. Returns nothing. Any idea how to solve this?

 

[JoeAtWork]

Try formatting the date as mm/dd/yyyy

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[Eugenios]

Didn't help, returns 2 weird values out of like a 100 possible. The thing is that even if I run the 2003 version in 2007 access it works fine, but if in 2003 it doesn't work.

 

[RoyVidar]

Strange, could you do a

[tt]Debug.Print MyRecordSource[/tt]

in both cases, and post the results?

A couple of tips:[ul]
[li]When using unbound controls for date criteria, set the format of the control to some valid date format. This will force whatever is inputted to be a valid date, or give some kind of errormessage[/li]
[li]When assigning dates to a string, which is later sent to Jet for evaluating, use either ISO 8601 "yyyy-mm-dd" or US format NOTE with escape characters "mm\/dd\/yyyy". As far as I know, these are the only safe ways[/li][/ul]

For dates, you might be interested in Allen Brownes article here

http://allenbrowne.com/ser-36.html

Also - are the number of columns returned by this SQL equal to the number of columns specified in the listbox Column Count property - and does the Column Widths property contain the same amount of column width specifications?

A very simple test of whether some SQL returns rows or not, is to use:

[tt]? currentproject.connection.execute(<TheSql>).GetString[/tt]

in the immediate pane (Ctrl+G)

Roy-Vidar

 

[Eugenios]

yyyy/mm/dd

SELECT * FROM qryContLife WHERE 1=1 AND [Depot In Date] Between #2008/05/01# And #2008/07/30#

mm/dd/yyyy

SELECT * FROM qryContLife WHERE 1=1 AND [Depot In Date] Between #05/01/2008# And #07/30/2008#

Both textboxes are formatted to short date. Currently in both cases in MSAccess2007 in returns the requested date range. Unfortunately I do not have the 2003 version of the program right now so i cannot show the debug print there, but it didn't work yesterday.