Access date problem

I have this code as the row source to a listbox: -

Dim Fd
Fd = Format(Me!TxtFromDate, "YYYY/MM/DD")
Sql = "SELECT dbo_View_DispatchNotesStatus5All.*  FROM dbo_View_DispatchNotesStatus5All "
Sql = Sql & "WHERE tbl_DriverAssigned.DriverCode Is Null AND dbo_View_DispatchNotesStatus5All.PlannedDeliverDate >= #" & Fd & "# "
LstDN.RowSource = Sql

Open in new window


The date criteria does not seem to work (I am in UK). If I print the Sql and put it into a query it works fine. Can anyone help?
HKFueyAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Give either of these a try:

Dim Fd
Fd = Format(Me!TxtFromDate, "YYYY/MM/DD")
Sql = "SELECT dbo_View_DispatchNotesStatus5All.*  FROM dbo_View_DispatchNotesStatus5All "
Sql = Sql & "WHERE tbl_DriverAssigned.DriverCode Is Null AND Format(dbo_View_DispatchNotesStatus5All.PlannedDeliverDate, "YYYY/MM/DD") >= #" & Fd & "# "
LstDN.RowSource = Sql 

Open in new window


Or:

Dim Fd
Fd = Format(Me!TxtFromDate, "YYYY/MM/DD")
Sql = "SELECT dbo_View_DispatchNotesStatus5All.*  FROM dbo_View_DispatchNotesStatus5All "
Sql = Sql & "WHERE tbl_DriverAssigned.DriverCode Is Null AND CDate(dbo_View_DispatchNotesStatus5All.PlannedDeliverDate) >= #" & CDate(Me!TxtFromDate) & "# "
LstDN.RowSource = Sql 

Open in new window

0
 
HKFueyAuthor Commented:
The format did not seem to work, the cdate looks like this: -
Dates
It shows 03/10/2013 (October) as 10th March
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>Sql = "SELECT dbo_View_DispatchNotesStatus5All.*
Prolly would be best to lose the asterisk and to explicitly spell out all columns in your SQL statement.

(1)  Takes less memory, as is the table has 50 columns and you want to display a small subset, say 3-5, then it's less data to move around.
(2)  If your table column order changes, the columns will be in a different order in the resulting recordset then the ordering of columns in your listbox.
(3)  Avoiding SELECT * is a best practice anyways.

>The format did not seem to work
Define for us 'did not work'.  Wrong date?  Do you want mm/dd/yyyy instead of dd/mm/yyyy?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
NorieConnect With a Mentor VBA ExpertCommented:
Does this work?
Dim Fd 

    Fd = Format(Me!TxtFromDate, "YYYY-MM-DD")

    Sql = " SELECT dbo_View_DispatchNotesStatus5All.*  "
    Sql = Sql & " FROM  dbo_View_DispatchNotesStatus5All "
    Sql = Sql & " WHERE tbl_DriverAssigned.DriverCode Is Null "
    Sql = Sql & " AND dbo_View_DispatchNotesStatus5All.PlannedDeliverDate >= '" & Fd & "'"

    LstDN.RowSource = Sql 

Open in new window

0
 
JimFiveConnect With a Mentor Commented:
If imnorie's solution doesn't work then look at the format of DN's from date control on the form.  It looks to me that you think 3/10/2013 is October 3, 2013, but the computer thinks 3/10/2013 is March 3, 2013.  Try using the datepicker to select the date, as well and see what happens.  It's possible that the Format function is assuming the wrong date format of the text string.
0
 
Gustav BrockConnect With a Mentor CIOCommented:
Too much guessing here.
Run this is and tell us what you get:
Dim Fd As String
Debug.Print "TxtFromDate", Me!TxtFromDate

Fd = Format(Me!TxtFromDate, "yyyy\/mm\/dd")
Debug.Print "fd", fd

Sql = "SELECT dbo_View_DispatchNotesStatus5All.*  FROM dbo_View_DispatchNotesStatus5All "
Sql = Sql & "WHERE tbl_DriverAssigned.DriverCode Is Null AND dbo_View_DispatchNotesStatus5All.PlannedDeliverDate >= #" & Fd & "# "
Debug.Print "Sql", Sql

LstDN.RowSource = Sql

Open in new window

/gustav
0
 
HKFueyAuthor Commented:
Have given all equal points and apologize for wasting your time. (The row source was being changed by another bit of code)
Can't believe I missed it DUH!

Thanks all for trying to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.