Missing dates in query form

[Domino2]

If I run an adhoc query, putting in a date, I get all my records. However I am missing records in a query by form. I have a listbox that gets dates via a query to a table (Updated On) field. The listbox is called DULISTMy query is using a where extension: ' Date Updated

If [Forms]![TabbedForm]![DULIST] <> "" Then MySql = MySql & " AND PROGRESS.[Updated On] = #" & Format([Forms]![TabbedForm]![DULIST], "dd/mm/yyyy") & "#"
End If

However If I pick dates:12/10/2006 or 06/04/2011 or 03/10/2011 I get no records from query.If I amend the date 12/10/2006 to 31/10/2006 my record comes out, the same if I modify 06/04/2011 to 26/04/2011 I get my record out. Using Access 2010

 

[PHV]

Format([Forms]![TabbedForm]![DULIST], "[!]yyyy-mm-dd[/!]")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

You can help yourself simply by:
[tt]
If [Forms]![TabbedForm]![DULIST] <> "" Then
MySql = MySql & " AND PROGRESS.[Updated On] = #" & Format([Forms]![TabbedForm]![DULIST], "dd/mm/yyyy") & "#"
End If
[blue]
Debug.Print MySql[/blue]
[/tt]
and see what your DB gets

Have fun.

---- Andy

 

[PHV]

Andy, the problem was clearly an ambiguous date format ...

 

[Andrzejek]

Yes, I know, but most of the time you can acctually see your problem when you can see what your database gets, the SQL that you send to your DB. That's all.

Have fun.

---- Andy

 

[Domino2]

Thanks PHV/Andy. Sorry for delay, system went down here. PHV you are correct, I switched the day/month/year order around as my system is set that way, it produced dates in the query but with missing ones which threw me off the trail, had nothing come out would have looked again. Andy, I did try debug.print MySql, it looked okay as I was expecting day/month/year, so again thrown in the wrong direction. Next time I won't change what I am given, that taught me. Thanks again

 

[Domino2]

Wish this was Access 2003. I am filling a listbox which gets used as a recordsource for a report. The listbox being filled from a query is filling in but dates are out of order.

In the query I have this syntax:

MySql = "SELECT PARTNERS.Partner, ACTIVITIES.Type, ACTIVITIES.Status, ACTIVITIES.[Date Raised], PROGRESS.[Updated On], PROGRESS.Update"
MySql = MySql & " FROM (PARTNERS INNER JOIN ACTIVITIES ON PARTNERS.ID1 = ACTIVITIES.ID1) INNER JOIN PROGRESS ON ACTIVITIES.ID3 = PROGRESS.ID3"
MySql = MySql & " WHERE 1 = 1 "

' Date Updated
If [Forms]![TabbedForm]![DULIST] <> "" Then
MySql = MySql & " AND PROGRESS.[Updated On] = #" & Format([Forms]![TabbedForm]![DULIST], "yyyy/mm/dd") & "#"
End If

MySql = MySql & " ORDER BY PROGRESS.[Updated On];"

However the list fills way out of date order.

 

[MazeWorX]

In the control on the form apply the order by

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[Domino2]

I went to the properties thinking you knew of a new property in 2010 but found nothing. The list is filled by an external to the control SQL statement. Can you explain you reply a bit more, thanks

 

[Domino2]

I have called it a day, gone away from SQL to a straight query, all okay. Does anyone know of a website where Access 2010 bugs are being listed. I am having so many problems, things like making oblects equally spaced is dead, setting tab orders on objects placed on tab controls totally mess up front to back positions of items like rectangles etc. It may just be a duff issue of my program, reinstalled twice. Just good to know if others having similar?