Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

ado query

Posted on 2002-05-21

Visual Basic Classic

1 solution

Medium Priority

175 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

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

Join The Community

10 Comments

LVL 70

Expert Comment

by:Éric Moreau

ID: 70239502002-05-21

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;"

LVL 1

Author Comment

by:bolox

ID: 70239702002-05-21

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????

LVL 1

Accepted Solution

by:pjbaratelli

pjbaratelli earned 600 total points

ID: 70240212002-05-21

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

LVL 70

Expert Comment

by:Éric Moreau

ID: 70240352002-05-21

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;"

LVL 2

Expert Comment

by:Ixeus

ID: 70240502002-05-21

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

LVL 2

Expert Comment

by:Ixeus

ID: 70240522002-05-21

aiiiiiii my ISP died as I was sending and you beat me by 4 min ... oh well =p

LVL 1

Author Comment

by:bolox

ID: 70240762002-05-21

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

LVL 1

Expert Comment

by:pjbaratelli

ID: 70241502002-05-21

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......

LVL 1

Expert Comment

by:pjbaratelli

ID: 70241552002-05-21

I meant farebase...

LVL 1

Author Comment

by:bolox

ID: 70241702002-05-21

YOU DO DESERVE THESE POINTS,

I had no idea why this was happening,

Ho hum, Bolox strikes again

THANX AGAIN

Featured Post

Question has a verified solution.

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

Sending and receiving XML or HTML data, communicating with web services using VB6

Article by: kbirecki

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…

Visual Basic Classic

Using the VB6 Debugger

Article by: Martin

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture. There is also a companion Debug Toolbar that looks like the followin…

Visual Basic Classic

Using a Criteria Form with an Access Report

Video by: TechMommy

Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

Basic Error Handling code for VBA and Microsoft Office

Video by: crystal

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…