Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

(dd/mm/yyyy) ? or is it (mm/dd/yyyy)

Posted on 2002-05-23
10
Medium Priority
?
1,114 Views
Last Modified: 2008-01-09
SORRY FOR USING ANOTHER LOGON,IT WILL ONLY BE USED FOR THIS QUESTION, IT IS THAT I HAVE ASKED SOOOOO MANY QUESTIONS I HAVE RUN OUT OF POINTS.

HERE GOES...........

I am trying to make a SQL connection........
on my VB form i have a DTpicker1, in my database i have two fields validfrom and validto

what i want is to show all in the database where DTpicker is in-between the two dates.

in laymans terms:

WHERE dbase.validfrom (lessthan) DTPicker1 (AND) dbase.validto(greaterthan) DTpicker1.

i have done it great so far......

query = "SELECT * From farebase where (((farebase.valid_from) <=#" & DTPicker1.Value & "#) and ((farebase.valid_to) >=#" & DTPicker1.Value & "#)) order By farebase.gross_ad;"
----------------------------------------------
the only problem is........

if DTpicker1 is for example 01/03/2002, the database thinks i am asking for the 3rd May, as oposed to 1st March.... or vise versa,

I have changed the DTpicker properties to custom (dd/MM/yyyy)

so i am sure this is not the problem...???

I know i should use:

Format(farebase.valid_to,"dd/mm/yyyy")

but i have having problems putting it into the SQL query.
i had a stab here:

query = "select * from farebase where" & Format(farebase.valid_from, "dd/mm/yyyy") & "<=" & Format(DTPicker1.Value, "dd/mm/yyyy") & "and" & Format(farebase.valid_to, "dd/mm/yyyy") & ">=" & Format(DTPicker1.Value, "dd/mm/yyyy") & " order By farebase.gross_ad;"


any ideas???

PLEASE HELP ME
0
Comment
Question by:bolox2
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 7029224
You still need the # (and you had some spaces missing):

query = "select * from farebase where #" & Format(farebase.valid_from, "dd/mm/yyyy") & "# <= #" & Format(DTPicker1.Value, "dd/mm/yyyy") & "# and #" & Format(farebase.valid_to, "dd/mm/yyyy") & "# >= #" & Format(DTPicker1.Value, "dd/mm/yyyy") & "# order By farebase.gross_ad;"

CHeers
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 7029232
bolox: using access I would always recommend using a date which cannot be ambiguous so:

query = "select * from farebase where #" & Format(farebase.valid_from, "yyyy-mm-dd") & "# <= #" & Format(DTPicker1.Value,
"yyyy-mm-dd") & "# and #" & Format(farebase.valid_to, "yyyy-mm-dd") & "# >= #" & Format(DTPicker1.Value,
"yyyy-mm-dd") & "# order By farebase.gross_ad;"

Access has a tendency in queries to interpret any date as mm/dd regardless of the locale of the machine and the regional settings. Using a date format such as "yyyy-mm-dd" or even "dd mmm yyyy" which result in content that is unambiguous solves this problem.
0
 

Author Comment

by:bolox2
ID: 7029252
thankyou guys,

i have tried both options, it comes up with an error (same as what i got with my try):

runtime error 424 object required?

any ideas?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7029255
Sorry. This is because of the format(farebase.valid_from , ...) things. As the valid_from and valid_to fields are actually date fields, you don't need to format them:

query = "select * from farebase where farebase.valid_from <= #" & Format(DTPicker1.Value, "dd/mm/yyyy") & "# and farebase.valid_to >= #" & Format(DTPicker1.Value, "dd/mm/yyyy") & "# order By farebase.gross_ad;"

CHeers
0
 

Author Comment

by:bolox2
ID: 7029259
SORTED IT OUT..........

query = "select * from farebase where farebase.valid_from <= #" & Format(DTPicker1.Value, "yyyy/mm/dd") & "# and farebase.valid_to >= #" & Format(DTPicker1.Value, "yyyy/mm/dd") & "# order By farebase.gross_ad;"


I STILL DO NOT UNDERSTAND HOW TO SPLIT THE POINTS, I WANT ANGELL AND TIM TO GET 50 POINTS EACH FOR YOUR ANSWERS,  AS THEY BOTH MET IN THE MIDDLE FOR ME TO SORT IT OUT
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7029262
splitting points can be done by asking this in the Community Support area, using a 0-point question.
CHeers
0
 
LVL 2

Expert Comment

by:priya_pbk
ID: 7029274
Try this:

query="select * from farebase where valid_from between #" & format(dtpicker1.value,"dd-mmm-yy") & "# and #" & format(dtpicker2.value,"dd-mmm-yy") order by gross_ad"

Basically what i wanted to say is try using "dd-mmm-yy" in the format instead of "dd-mm-yy" in your sql query.

I hope this can fix your problem!

-priya


0
 
LVL 5

Expert Comment

by:Netminder
ID: 7029922
bolox2,

I have changed the points in this question to 50 so that you may accept the comment of TimCottee as an answer. Now you can ask another question in this topic area for angelIII worth 50 points, and when angelIII comments, you can accept that as an answer as well.

Please note that you will be receiving email from Admin regarding your duplicate account, as this is a violation of the Membership Agreement. There are plenty of legitimate ways to get more points if you need them, and as such, the matter is to be taken very seriously.

Netminder
CS Moderator
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7029931
Thank you, bolox2 for your request in Community Support today, I have processed this point split for you with 50 points awarded here, the other 50 await TimCottee comments here:

Points for TimCottee -> http://www.experts-exchange.com/jsp/qShow.jsp?qid=20303964

Moondancer - EE Moderator
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7029938
Oh oh, when I began this point split, no comment from Netminder, after completed see previous comments.  If this results in a duplicate question, for TimCottee, let me know.
Thanks,
Moondancer  -  EE Moderator
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

926 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