Not like statement not working as expected 1

[Triacona]

Dear All,
Thank you for a great forum

I have a query - EnAllocationOverdue
I have a form - MainScreen

The query runs off the form.

I use the following criteria within the query.

WHERE (((UNI7LIVE_ENCASE.RECEPD)<=DateAdd("d",102,[UNI7LIVE_ENCASE].[RECEPD])) AND 

((UNI7LIVE_ENCASE.ENSTAT) Not Like "*" & [Forms]![MainScreen].[cmbEnStat] & "*") AND 

((UNI7LIVE_ENCASE.CLOSED) Is Null));

The problem is with the NOT LIKE.

The field in particular here is cmbEnStat if cmbEnStat is blank, no data is returned.

However if cmbEnStat has a value in it for example NOTICE it returns data with NOTICE excluded.

I have no idea why this is not working.

Any help will be greatly appreciated
Thank you




Thank you,

Kind regards

Triacona

 

[dhookom]

Try add a zero length string to the field to change it from null to a string:

WHERE (((UNI7LIVE_ENCASE.RECEPD)<=DateAdd("d",102,[UNI7LIVE_ENCASE].[RECEPD])) AND 
((UNI7LIVE_ENCASE.ENSTAT [b][COLOR=#A40000][highlight #FCE94F]& ""[/highlight][/color][/b]) Not Like "*" & [Forms]![MainScreen].[cmbEnStat] & "*") AND 
((UNI7LIVE_ENCASE.CLOSED) Is Null));

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Hi Duane,

Thank you very much for your reply

I tried what you suggested and the result is still no data and now if I insert a value into cmbEnStat there is no data as well, whereas if there was a value in cmbEnStat in the past there was.

Do you have any other thoughts that could help?

Thank you

Thank you,

Kind regards

Triacona

 

[dhookom]

Try add NZ():

WHERE (((UNI7LIVE_ENCASE.RECEPD)<=DateAdd("d",102,[UNI7LIVE_ENCASE].[RECEPD])) AND 
((UNI7LIVE_ENCASE.ENSTAT & "") Not Like "*" & Nz([Forms]![MainScreen].[cmbEnStat],"ZZZZZ") & "*") AND 
((UNI7LIVE_ENCASE.CLOSED) Is Null));

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?

WHERE UNI7LIVE_ENCASE.RECEPD)<=DateAdd("d",102,[UNI7LIVE_ENCASE].[RECEPD])
 AND (UNI7LIVE_ENCASE.ENSTAT Not Like "*" & [Forms]![MainScreen].[cmbEnStat] & "*" OR Trim([Forms]![MainScreen].[cmbEnStat]='')
 AND (UNI7LIVE_ENCASE.CLOSED)Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Sorry for the typo.
WHERE UNI7LIVE_ENCASE.RECEPD)<=DateAdd("d",102,[UNI7LIVE_ENCASE].[RECEPD])
AND (UNI7LIVE_ENCASE.ENSTAT Not Like "*" & [Forms]![MainScreen].[cmbEnStat] & "*" OR Trim([Forms]![MainScreen].[cmbEnStat])='')
AND (UNI7LIVE_ENCASE.CLOSED)Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Dear PHV and Duane,

Thank you both for your time and effort, it is much appreciated

I have tried both your solutions and it is Duane's that works.
PHV the result from yours is still producing no data.

Thank you again


Thank you,

Kind regards

Triacona

 

[Triacona]

Dear Duane and PHV,

I have also though of using the NOT IN("","") statement to this effect.

Not In ("*" & [Forms]![MainScreen].[AddSrch] & "*","*" & [forms]![MainScreen].[SrchCrit] & "*")

I have tried inputting NOTICE into AddSrch and PLNREC into SrchCrit, but it does not exclude as expected.

Any thoughts.

Thank you

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,

I have tried the following..

Not In ([Forms]![MainScreen].[AddSrch],[Forms]![MainScreen].[SrchCrit])

This works if there are values within the 2 text boxes - AddSrch and SrchCrit

How do I get it to accept NULL values and therefore give all the data?

I have tried:

 Not In (Nz([Forms]![MainScreen].[AddSrch],[Forms]![MainScreen].[SrchCrit]))

I have also tried

Nz(Not In([Forms]![MainScreen].[AddSrch],[Forms]![MainScreen].[SrchCrit]))

Thanks for your help

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,

I have also tried:

Not In (Nz([Forms]![MainScreen].[AddSrch]),Nz([Forms]![MainScreen].[SrchCrit]))

Thanks

Thank you,

Kind regards

Triacona