[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1116
  • Last Modified:

(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
0
bolox2
Asked:
bolox2
  • 3
  • 2
  • 2
  • +3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
TimCotteeCommented:
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
 
bolox2Author Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
bolox2Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
splitting points can be done by asking this in the Community Support area, using a 0-point question.
CHeers
0
 
priya_pbkCommented:
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
 
NetminderCommented:
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
 
MoondancerCommented:
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
 
MoondancerCommented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now