WHERE Statement with 2 criteria

[DKasler]

I am attempting to have the following SQL Query find a records using the field PatID, then of those only give me the records where the contents of the field Disabled is NOT EQUAL to the letter Y.

The statement below doesnt seem to be working correctly.

sSQL = "select vaccine,manufacturer,dov,lotnum,exp,docname,type,Dose,insurance,Disabled from vfcrec Where PatID like '" & Text2.Text & "' and Disabled <> 'Y' order by vaccine"

Any help would be appreciated. Thanks in Advance.

EDIT: I just attepted to subsititue the 'Y' with chr$(89) and what happens now is even though the operator is <> (which i believe means NOT EQUAL) The Output is showing me records that DO EQUAL Y....

Im confused...

[dglienna]

try IS NOT. i think that is right...
maybe NOT =

[DKasler]

Nah, that didnt work.

According to the MSDN Lib:

<> Not equal to

Any other suggestions?

[dglienna]

Put in a breakpoint, and look at sSQL to make sure that it is what you want it to be. There may be something else wrong with it. Put in a
debug.print sSQL

I got this back:

select vaccine,manufacturer,dov,lotnum,exp,docname,Type,Dose,insurance,Disabled from vfcrec Where PatID like 'Text2' And Disabled <> 'Y' order by vaccine

I don't think the variable is right, and you may need to be using """ instead of '

[DKasler]

sSQL is just a string value...

Here is the full set of code:

Dim sConnect As String
    Dim sSQL As String
    Dim dfwConn As ADODB.Connection
 
    ' set strings
    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Password='';User ID=Admin;Data Source=Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & frmLocation.Text1.Text & "';Persist Security Info=False;Mode=Share Deny None;Extended Properties='';Jet OLEDB:System database='';Jet OLEDB:Registry Path='';Jet OLEDB:Database Password='';Jet OLEDB:Engine Type=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password='';Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"
    sSQL = "select vaccine,manufacturer,dov,lotnum,exp,docname,type,Dose,insurance,Disabled from vfcrec Where PatID like '" & Text2.Text & "' and Disabled <>  '" & Chr$(89) & "' order by vaccine"
    
    ' open connection
    Set dfwConn = New Connection
    dfwConn.Open sConnect
 
    ' create a recordset using the provided collection
    Set datPrimaryRS = New Recordset
    datPrimaryRS.CursorLocation = adUseClient
    datPrimaryRS.Open sSQL, dfwConn, adOpenForwardOnly, adLockReadOnly
 
    Set MSHFlexGrid2.DataSource = datPrimaryRS

[dglienna]

isn't Type a reserved word? I edited the post above

[DKasler]

Ive used 'type' as a field name in this expression for months and its worked fine...

just now im trying to add the

and Disabled <>  '" & Chr$(89) & "'

and thats whats not working. Until I added that the statement did exactly what it was suposed to.

[dglienna]

I got this from Access 2000

SELECT Call.ID, Call.Field1
FROM Call
WHERE (((Call.Field1)<>"Y"));

I don't have SQL Server.

[DKasler]

I don't have SQL Server.

I dont either. Im pulling the data from an Access DB

[nkad]

Ive used 'type' as a field name in this expression for months and its worked fine...

just now im trying to add the

and Disabled <>  '" & Chr$(89) & "'

and thats whats not working. Until I added that the statement did exactly what it was suposed to.

Uh, watch out for reserved words..... type and disabled are most likely reserved words. Try putting brackets [] around the field name.

[nkad]

I dont either. Im pulling the data from an Access DB

If you dont have the money to fork over for SQL Server, MySQL can be an option for you. Sure beats not having to use Access any day.

[dglienna]

Try designing a new query

[DKasler]

Ok..

and Disabled = Chr$(89) order by vaccine"

DOES EXACTLY WHAT IT SHOULD... Finds records where the Field Disabled IS EQUAL to Chr$(89)

If I change the = to <> the query stops finding records and MOST of the records in the table <> Chr$(89) (all but 1 record are actually blank in the Disabled field)

I dont believe this has anything to do with Reserved words because if it did the query wouldnt work with an = either...

[pnish]

I assume Disabled is a text field. What possible values can this field hold? Y and N? Is it possible that there are NULL values? That can cause problems. Try something like:

Where [color=red]([/color][PatID] like '" & Text2.Text & "'[color=red])[/color] and [color=red]([/color][color=red]Not[/color] [Disabled] [color=red]=[/color] 'Y'[color=red])[/color] [color=black]order by vaccine"[/color]

If that doesn't work, you may need to test for [Disabled] Is Null also.

[DKasler]

I got it more or less worked out. I ran an update query changing any field not EQUAL to Y to N

Your right... it was probably NULL working its way in there.

Thanks Much

[dglienna]

Glad it's working. I hope I helped some. I am trying to learn also.

[szlamany]

Just wanted to make sure you were aware that LIKE requires a wildcard in the lookup text. Not sure what you are typing in the TEXT BOX - but to use LIKE you must also have a wildcard character.

We use MS SQL - it would be:

WHERE PATID LIKE '%SMITH%'

Also LIKE is for character data - not numeric data types.