Solved

Access date problem

Posted on 2013-12-09
7
305 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 100 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 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 33

Assisted Solution

by:Norie
Norie earned 100 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 100 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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

707 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now