• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

ado query

query = "SELECT farebase.*, farebase.valid_from, farebase.valid_to From farebase WHERE (farebase.valid_from) >='" & "01/01/1999" & "' and (farebase.valid_to) <= '" & "01/01/2006" & "' order by farebase.gross_ad desc;"

in a nutshell what i want is for (in the end) :

the database to filter using :

(farebase.valid_from) is greater or equal to
maskedbox1.text
-----------
AND
-----------
(farebase.valid_to) is smaller or equal to
maskedbox2.text


PLEASE HELP ME, i have tried loads of variations using ( and ) around parts, and the closest i got is using the trim$ method, but it did not filter, help

B
0
bolox
Asked:
bolox
Advertise Here
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
query = "SELECT farebase.*, farebase.valid_from, farebase.valid_to From farebase WHERE (farebase.valid_from)
>='" & maskedbox1.formatedtext & "' and (farebase.valid_to) <= '" & maskedbox2.formatedtext & "' order by farebase.gross_ad
desc;"
0
 
boloxAuthor Commented:
data type mismatch in criteria expression error now??

i changed to formaTTedtex of both boxes, i suppose that was just a typo.

Any other ideas????




0
 
pjbaratelliCommented:
Try this
  query="SELECT * FROM farebase WHERE valid_from >='" & DateValue(maskedbox1.text) & "' AND valid_to <='" & DateValue(maskedbox2.text) & "'"

If this is an access database you need to replace the single quotes (') with number signs (#) for the database to recognize the values as dates.  

The other issue to watch for is the time.  5/21/2002 12:000:00 means that anything after (and including) 12 noon is returned, but everthing before 12 noon is not even though it has the day date.  I would manipulate the date and time first.

strDateFrom = Format$(maskedbox1.text,"mm/dd/yyyy") & " 00:00:00"
strDateTo = Format$(maskedbox2.text,"mm/dd/yyyy") & " 23:59:00"

Manipulating the date like this means anything greater than equal to this value is choosen and includes the entire day for the from date. For the to date it will include any date from the end of the day back. The sql statement would look like this:

query = "Select * FROM farebase WHERE valid_From>='" & strDateFrom & "' AND valid_to>='" & strDateTo & "'"

Note: I think that you will have to manipulate the time for your own specifications, though.

Hope this helps
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Sign Up
 
Éric MoreauSenior .Net ConsultantCommented:
is this in access? in this case

query = "SELECT farebase.*, farebase.valid_from, farebase.valid_to From farebase WHERE (farebase.valid_from) >= #" & maskedbox1.formatedtext & "# and (farebase.valid_to) <= #" & maskedbox2.formatedtext & "# order by farebase.gross_ad desc;"
0
 
IxeusCommented:
If you're using MSAccess database it's (Jet3.51 or 4.0)
 
#Date#
'Date' SQLServer or something

so it should be

query = "SELECT farebase.*, farebase.valid_from, farebase.valid_to From farebase WHERE (farebase.valid_from)

>=#" & maskedbox1.formatedtext & "# and (farebase.valid_to) <= #" & maskedbox2.formatedtext & "# order
by farebase.gross_ad
desc;"

Just a thought


If I read it wrong, sorry.

-Ix
0
 
IxeusCommented:
aiiiiiii my ISP died as I was sending and you beat me by 4 min ... oh well =p
0
 
boloxAuthor Commented:
GOOD JOB, MORE POINTS COMING UP IF YOU CAN FIX THIS BIT TO GO WITH IT

MY CODE..
-----------------------------------------
Dim myconn
Set myconn2 = New ADODB.Connection
myconn2.Open "Provider=Microsoft.JET.OLEDB.4.0;Persist Security Info=False;Data Source=c:\farebase\farebase.mdb"
'Set myado2 = myconn2.Execute("SELECT farebase.* From farebase where farebase.from_1 = '" & Text1.Text & "' and to_1 = '" & Text2.Text & "'order by farebase.from_1 desc;")



query = "SELECT farebase.*, farebase.valid_from, farebase.valid_to From farebase WHERE (farebase.valid_from) >=#" & DateValue(MaskEdBox1.Text) & "# and (farebase.valid_to) <= #" & DateValue(MaskEdBox2.Text) & "# order By farebase.gross_ad desc;"
Set myado2 = myconn2.Execute(query)
num = 0 + 0
 While Not myado2.EOF
    num = num + 1
    MSFlexGrid1.Cols = 11
    MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
   
MSFlexGrid1.TextMatrix(num, 0) = IIf(IsNull(UCase(myado2!airline)), "", (UCase(myado2!airline)))
MSFlexGrid1.TextMatrix(num, 1) = IIf(IsNull(UCase(myado2!Class)), "", (UCase(myado2!Class)))
MSFlexGrid1.TextMatrix(num, 2) = IIf(IsNull(UCase(myado2!from_1)), "", (UCase(myado2!from_1)))
MSFlexGrid1.TextMatrix(num, 3) = IIf(IsNull(UCase(myado2!routing_air1)), "", (UCase(myado2!routing_air1)))
MSFlexGrid1.TextMatrix(num, 4) = IIf(IsNull(UCase(myado2!to_1)), "", (UCase(myado2!to_1)))
MSFlexGrid1.TextMatrix(num, 5) = IIf(IsNull(UCase(myado2!journeytype)), "", (UCase(myado2!journeytype)))
MSFlexGrid1.TextMatrix(num, 6) = IIf(IsNull(UCase(myado2!valid_from)), "", (UCase(myado2!valid_from)))
MSFlexGrid1.TextMatrix(num, 7) = IIf(IsNull(UCase(myado2!valid_to)), "", (UCase(myado2!valid_to)))
MSFlexGrid1.TextMatrix(num, 8) = UCase(myado2!min_num & myado2!min_type) & "/" & UCase(myado2!max_num & myado2!max_type)
MSFlexGrid1.TextMatrix(num, 9) = UCase("£" & myado2!gross_ad)
MSFlexGrid1.TextMatrix(num, 10) = myado2!fareid
    myado2.MoveNext
  Wend
End If
----------------------

ON RUNNING, IT SAYS ...........

MSFlexGrid1.TextMatrix(num, 6) = IIf(IsNull(UCase(myado2!valid_from)), "", (UCase(myado2!valid_from)))

"cannot be found in the collection correesponding to the requested nanme or ordinal"
IT WORKS FINE IF I REMOVE THE QUERY AND JUST DIAPLY ALL????

ANY IDEAS
0
 
pjbaratelliCommented:
I think what is happening with this is you are call the same field twice.  First you Select farebase.*, which gives you everything, and then you select farebase.valid_from and farebase.valid_to.  The return should give you all the fields, plus the two duplicated fields.  Just use a wildcard (*)

   SELECT * FROM feebase......
0
 
pjbaratelliCommented:
I meant farebase...
0
 
boloxAuthor Commented:
YOU DO DESERVE THESE POINTS,

I had no idea why this was happening,

Ho hum, Bolox strikes again

THANX AGAIN
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Access It Instantly
  • 3
  • 3
  • 2
  • +1
Advertise Here
Tackle projects and never again get stuck behind a technical roadblock.
Join Now