Solved

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

Posted on 2002-05-23
10
1,095 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

738 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