How to get values based on 2 date between in access db in vb

Question

0.00/5 (No votes)

See more:

Hi I have access database ,which contains date as one column,based on that date value need to get values.
In my access db
Column name PDate and its datatype is Date/Time and General Date format "5/8/2015 3:58:39 PM"

below is my data in Access db

SQL

BMID    UserId    PDate                    PName
50      1        5/8/2015 3:58:39 PM     Aravind
51      1        6/8/2015 3:59:47 PM     Aravind
52      1        8/8/2015 4:01:09 PM     Aravind
53      1       10/8/2015 4:02:29 PM     Aravind
54      1       12/8/2015 4:03:19 PM     Aravind
55      1       14/8/2015 4:04:39 PM     Aravind
56      1       16/8/2015 4:19:39 PM     Aravind

From above if i try to use select query in access db like

SQL

Select BMID,UserId,Format([PDate],'dd-MM-yyyy') as PDate1,PName From BodyMeasurements where UserId=1 And BodyMeasurements.[PDate]  between #19/07/2015 12:00:00 AM# and #18/08/2015 11:59:59 PM#

i can get result all 7 rows,if end date is "14/08/2015 11:59:59 PM" i can get top 6 rows,but if end date is "12/08/2015 11:59:59 PM" i can get all 7 rows as result.

pls reply me how to correct values if date is 12 and below

Regards
Aravind

Posted 17-Aug-15 15:33pm

Aravindba

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-08-17T17:48:00

This sounds like the Access is interpreting your date as dd/mm in the first case and mm/dd in the second.

Try using format yyyy-mm-dd which should always be interpreted correctly. In other words

SQL

Select BMID,UserId,Format([PDate],'dd-MM-yyyy') as PDate1,PName 
From BodyMeasurements 
where UserId=1 
And BodyMeasurements.[PDate]  between #3015-07-19 12:00:00 AM# and #2015-08-18 11:59:59 PM#