?
Solved

Access date problem

Posted on 2013-12-09
7
Medium Priority
?
367 Views
Last Modified: 2013-12-12
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?
0
Comment
Question by:HKFuey
7 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 400 total points
ID: 39705865
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
 

Author Comment

by:HKFuey
ID: 39705902
The format did not seem to work, the cdate looks like this: -
Dates
It shows 03/10/2013 (October) as 10th March
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 400 total points
ID: 39705986
>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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Assisted Solution

by:Norie
Norie earned 400 total points
ID: 39706236
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
 
LVL 15

Assisted Solution

by:JimFive
JimFive earned 400 total points
ID: 39707140
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
 
LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 400 total points
ID: 39707927
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
 

Author Closing Comment

by:HKFuey
ID: 39713469
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question