2007 to 2003 function problem

[Eugene Marzins]

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?

 

[Michel Walsh]

You should use mm/dd/yyyy as date format when using the # delimiter.

Actually, since you use the query as rowsource, you are probably better
with:

mycriteria = mycriteria & " AND ([Depot In Date] BETWEEN
FORMS!yourFormNameHere!StartDate AND FORMS!yourFormNameHere!EndDate) "


The syntax FORMS!formName!ControlName is automatically resolved, without
having, for you, to hard code the logic for the proper delimiter ( ', ", or
# ), *if* you use DoCmd, or a recordSource, or a rowSource property of an
Access form/control. It won't work if you have to use CurrentDb or an object
created from CurrentDb.



Vanderghast, Access MVP