Access 2007 and using <> in queries 3

[GreyHounds]

What have I done? And what do I need to do to go back to my simple syntax in a simple query?

I enter <>'Approved' and it returns an empty set when I know that 25 out of about 200 records have that value. If I enter Is Null in the field I get the values that are essentially not approved. It is so annoying to try to figure out what syntax Access 2007 wants me to use today...

I have spent too long searching posts and the web trying to out why it isn't working in this db while it works in other dbs that I have.

Please help...thx

 

[dhookom]

Consider Null as meaning "unknown". Can you state the an unknown value is not "Approved"? Not really.

If I have a field named [Status] that I want to query, I use:

[Status] & "" <> "Approved"

This converts the Nulls to a zero-length-string allowing a comparison to another string such as "Approved".

Duane
Hook'D on Access
MS Access MVP

 

[GreyHounds]

Thank you for responding and i am still having issue.

so if i find a setting within access 2007 to convert all nulls to zero length i should be able to use the <> syntax?

hmmm... below is a screen shot of the area i am talking about. this is just using their simple querry that i use for quick data validation and record counts. It's something i have done hundreds of times but this time - I created a new db in access 2007 and it isn't working. I then converted it to 2003 in the hopes it would just do what i need it to do but no joy...

 

[Golom]

Can you switch to SQL view and post the SQL code?

It looks like the query designer screen that you posted would still have

[blue]WHERE Status_Cd <> "Approved"[/blue]

What Duane recommended was

[blue]WHERE Status_Cd [red]& ""[/red] <> "Approved"[/blue]

Where the portion in [red]red[/red] will convert NULLs to empty strings and you can do the comparison.

 

[GreyHounds]

this is my original query. so when i look at you and duane recommended it should be (pt_s_lit.STATUS_CD) &"" <>"Approved" ?

i guess my fundamental question is why do i need to do this now and haven't had to do it before? is there a setting that i have inadvertently set that makes this neccessary?

SELECT pt_s_lit.ROW_ID, pt_s_lit.CREATED, pt_s_lit.LAST_UPD, pt_s_lit.BU_ID, pt_s_lit.NAME, pt_s_lit.SUBST_FLG, pt_s_lit.EXPIRATION_DT, pt_s_lit.FILE_EXT, pt_s_lit.FILE_NAME, pt_s_lit.FILE_SRC_PATH, pt_s_lit.FILE_SRC_TYPE, pt_s_lit.LIT_CD, pt_s_lit.OBJ_TYPE_CD, pt_s_lit.SRC_OU_ID, pt_s_lit.STATUS_CD, pt_s_lit.TYPE_CD, pt_s_lit.VOLUME_NAME, pt_s_lit.X_TEMPLATE_NAME
FROM pt_s_lit
WHERE (((pt_s_lit.FILE_EXT) Is Null) AND ((pt_s_lit.STATUS_CD)<>"Approved") AND ((pt_s_lit.VOLUME_NAME) Is Null) AND ((pt_s_lit.X_TEMPLATE_NAME) Is Null));

 

[PHV]

haven't had to do it before?
Really ?
Before what ?

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

 

[dhookom]

GreyHounds,
This is the behaviour I have experienced since Access version 2.0.

Duane
Hook'D on Access
MS Access MVP

 

[GreyHounds]

First Duane and PHV, I have seen your names all over this website and want to thank you for responding to my query on queries and in addition to your responses to other questions people have posed. I appreciate your contributions very much.

Now, before when? Before Access 2007. However - I just opened an old Acc2003 database, did the same pass-thru query and it is giving me the same blank screen. So there it is. I stand corrected and will futz around with the null values. I would have bet money that I hadn't had this difficulty before but I imagine I must have done it correctly in the past, and that my frustrations with the changes that showed up in 2007 have me tripping all over the place.

Again thank you for your assistance and taking time to respond!
Enjoy the weekend.

Laura

 

[dhookom]

I wasn't aware before that your query is a "pass-through". Different databases will have different flavors of SQL. Most however will treat NULLs in a similar manner. Some databases may default to "" versus NULL for text fields.

Duane
Hook'D on Access
MS Access MVP

 

[GreyHounds]

Yuppers. It's an oracle 10g db and i know that when i report using crystal i have to discretely deal with nulls in formulas. So a little bit of panic and uncertainty set in because i have data processes running, i went back and started checking and realize that most of what i do is based on data values selected to be included vs excluded. So it is Friday and I believe i will go home and reminisce on times when i actually knew stuff...


Thanks again Duane.

Laura

 

[dhookom]

it is Friday and I believe i will go home and reminisce on times when i actually knew stuff.

I think I need a t-shirt with this on it.

Duane
Hook'D on Access
MS Access MVP

 

[GreyHounds]

I'll send you a t-shirt if you can draw me a map to that 'safe place' where i left my brain...