Not Like statement

  • Thread starter Thread starter balrog via AccessMonster.com
  • Start date Start date
B

balrog via AccessMonster.com

Hi,

I've got a table with few fields... I need to write query which shows me only
records that are not equal to one value. The field is text field. When I
write Not Like "*ZPSFN*" to that query, nothing is shown. There are about 20
records in the table with different values than ZPSFN, also there are "Null
values". Can anyone give me advice, how to show records which contain
different values than ZPSFN or null values?

Thanks in advance.
 
balrog said:
Hi,

I've got a table with few fields... I need to write query which shows
me only records that are not equal to one value. The field is text
field. When I write Not Like "*ZPSFN*" to that query, nothing is
shown. There are about 20 records in the table with different values
than ZPSFN, also there are "Null values". Can anyone give me advice,
how to show records which contain different values than ZPSFN or null
values?

Thanks in advance.
Click to expand...

SELECT *
FROM TableName
WHERE FieldName Not Like "*ZPSFN*"
OR FieldName Is Null
 
Rick said:
Hi,
Click to expand...
[quoted text clipped - 7 lines]
Thanks in advance.
Click to expand...

SELECT *
FROM TableName
WHERE FieldName Not Like "*ZPSFN*"
OR FieldName Is Null
Click to expand...


OK, thanks. This works fine to me. But I've got another problem. I need to
dynamically change recordsource of subform on my form. And in one case, the
result have to be same as the query above. I wrote code, which after pushing
command button apply a filter (change recordsource). The code is:

Private Sub cmdFlt4_Click()

Dim sloupec As String
Dim tSloup As String
Dim sql As String
Dim dOd As Date
Dim dDo As Date
Dim tZak As String
Dim tCas As String
Dim tWhere As String

tSloup = "DokFr"

/// I've got two text fields for date filter - from date and to date

If IsNull(Form_FormPrehledHistorie.fltOd4) Then
dOd = #1/1/1900#
Else
dOd = Form_FormPrehledHistorie.fltOd4
End If

If IsNull(Form_FormPrehledHistorie.fltDo4) Then
dDo = #12/31/9999#
Else
dDo = Form_FormPrehledHistorie.fltDo4
End If

/// Here I choose from three variants - filter only ZPSFN or everything but
ZPSFN or different value choosed in the field fltZmetkyZak on the main form.
The fourth variant is no filter.

If Form_FormPrehledHistorie.fltZmetky = 1 Then
tZak = " AND (IDZ = 'ZPSFN')"
Else
If Form_FormPrehledHistorie.fltZmetky = 2 Then
tZak = " AND (IDZ Not Like '*ZPSFN*' OR IDZ Is Null)" ' Not Like
'*ZPSFN*' OR IDZ Is Null)"
Else
If Form_FormPrehledHistorie.fltZmetky = 3 Then
tZak = " AND (IDZ = '" & Form_FormPrehledHistorie.fltZmetkyZak & "')
"
Else
tZak = ""
End If
End If
End If

/// Here I am creating part of sql statement for date filter

tCas = "(" & tSloup & " BETWEEN " & Format$(dOd, "\#mm\/dd\/yyyy\#") & " AND
" & Format$(dDo, "\#mm\/dd\/yyyy\#") & ")"

/// This is the full sql statement

sql = "SELECT * FROM DotPrehledZmetky WHERE ( " & tCas & tZak & ")"

Form_SubFormPrehledHistorieZmetky.RecordSource = sql
Form_SubFormPrehledHistorieVsázky.Requery

End Sub

If I print the result of sql to debug, I get this:

SELECT * FROM DotPrehledZmetky WHERE ( (DokFr BETWEEN #01/01/1900# AND
#12/31/9999#) AND (IDZ Not Like '*ZPSFN*' OR IDZ Is Null))

Can you give me second (I think the final) advice? Thank you very much.
 
Back
Top