Solved

Access date problem

Posted on 2013-12-09
7
334 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

829 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