Solved

Access date problem

Posted on 2013-12-09
7
348 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 66

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 34

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 50

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

729 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