Link to home
Start Free TrialLog in
Avatar of bolox2
bolox2Flag for United Kingdom of Great Britain and Northern Ireland

asked on

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

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
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TimCottee
TimCottee
Flag of United Kingdom of Great Britain and Northern Ireland image
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.
Avatar of bolox2
bolox2
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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?
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
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
Avatar of bolox2
bolox2
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
splitting points can be done by asking this in the Community Support area, using a 0-point question.
CHeers
Avatar of priya_pbk
priya_pbk
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


Avatar of Netminder
Netminder
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
Avatar of Moondancer
Moondancer
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 -> https://www.experts-exchange.com/jsp/qShow.jsp?qid=20303964

Moondancer - EE Moderator
Avatar of Moondancer
Moondancer
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