Solved

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

Posted on 2002-05-23
10
1,077 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…

763 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

6 Experts available now in Live!

Get 1:1 Help Now