Solved

ado query

Posted on 2002-05-21

Visual Basic Classic

1 solution

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

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

A Textbox ActiveX Control That Limits Input to Numbers

Article by: Martin

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 …

Intercepting Office Ribbon Control Events with VBA using "Repurposing" Commands

Article by: Jamie

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.

Using Automation to Control Excel from Access

Video by: TechMommy

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…

Creating and Using an Access Class Module

Video by: TechMommy

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…