Solved

ado query

Posted on 2002-05-21
10
169 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
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 69

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
 
LVL 69

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now