Solved

ado query

Posted on 2002-05-21
10
174 Views
Last Modified: 2010-05-02
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
Comment
Question by:bolox
[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
  • 3
  • 2
  • +1
10 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7023950
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
 
LVL 1

Author Comment

by:bolox
ID: 7023970
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
 
LVL 1

Accepted Solution

by:
pjbaratelli earned 150 total points
ID: 7024021
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7024035
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
 
LVL 2

Expert Comment

by:Ixeus
ID: 7024050
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
 
LVL 2

Expert Comment

by:Ixeus
ID: 7024052
aiiiiiii my ISP died as I was sending and you beat me by 4 min ... oh well =p
0
 
LVL 1

Author Comment

by:bolox
ID: 7024076
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
 
LVL 1

Expert Comment

by:pjbaratelli
ID: 7024150
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
 
LVL 1

Expert Comment

by:pjbaratelli
ID: 7024155
I meant farebase...
0
 
LVL 1

Author Comment

by:bolox
ID: 7024170
YOU DO DESERVE THESE POINTS,

I had no idea why this was happening,

Ho hum, Bolox strikes again

THANX AGAIN
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

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 …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
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…
Suggested Courses
Course of the Month3 days, 21 hours left to enroll

630 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