Help with WHERE clause 2

[Moss100]

Hello I have managed to confuse myself more than normal.

I have an Accounts Form which populates on open from a DOA recordet that I create from an external table.

I have all of that working fine. At the moment I am soing a SELECT * and this works fine.

What I actually want to do is exclude certain records.

For example there is a TYPE field in the table and I want to select everything except records with a TYPE of "Deposit Paid"

My current working code is:

Dim strSQL As String
Dim rs As DAO.Recordset

  strSQL = "SELECT * FROM " & Me.txt_Gem_Account_No.Value
  strSQL = strSQL & " ORDER BY date desc"
      
  Set rs = db.OpenRecordset(strSQL)

I have tried all sort of efforts such as:

Dim strSQL As String
Dim rs As DAO.Recordset
    
  strSQL = "SELECT * FROM " & Me.txt_Gem_Account_No.Value &, _
            "WHERE [type] <> 'rent paid"
  strSQL = strSQL & " ORDER BY date desc"
      
  Set rs = db.OpenRecordset(strSQL)

I could do with some help. Many thanks Mark

 

[Andrzejek]

> everything except records with a TYPE of "[blue]Deposit Paid[/blue]"
> "WHERE [type] <> '[blue]rent paid[/blue]"
[tt]
strSQL = "SELECT * FROM " & Me.txt_Gem_Account_No.Value & _
" WHERE UCase([type]) <> UCase('[blue]rent paid[/blue]')"
strSQL = strSQL & " ORDER BY date desc"
[/tt]
or
[tt]
strSQL = "SELECT * FROM " & Me.txt_Gem_Account_No.Value & _
" WHERE UCase([type]) [blue]NOT IN[/blue] UCase('[blue]Deposit Paid[/blue]')"
strSQL = strSQL & " ORDER BY date desc"
[/tt]



Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Moss100]

This is working thank you.....

strSQL = "SELECT * FROM " & Me.txt_Gem_Account_No.Value & _
" WHERE UCase([type]) <> UCase('rent paid')"
strSQL = strSQL & " ORDER BY date desc"

If I wanted to exclude other items can you demonstate how to do that..

ie. If I wanted to exclude 'rent paid' 'deposit paid' and 'fee paid'

Many thanks Mark

 

[SkipVought]

WHERE UCase([type]) NOT IN ('RENT PAID','SOME OTHER TYPE')"

...etc.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Moss100]

That's great - thank you all

 

[Andrzejek]

One would hope you would have a small look-up table, something like:
[pre]
Money_Paid
MID MoneyDesc
1 rent paid
2 deposit paid
3 fee paid
...
[/pre]
and in any other table(s) you would keep just the PK (as a FK) from that table, and NOT the description of it. So your SQL would be something like:
[tt]
WHERE MoneyID NOT IN (1, 2, 3) [/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.