Solved

Access 2007 query for a Character or no value

Posted on 2011-03-18
32
331 Views
Last Modified: 2012-06-27
I'm trying to query and database field to show if it has and asterisk (*) as the first character or if the field is blank.  Below are 2 examples that I've tried.  If I just use the first part I can query out fields that have the *, but I can't get just blank fields to show up.  The examples just give me everything.

Trap6: (InStr(1,[CellBock6],Chr(42)) Or Len([CellBock6])=0)

Trap7: (InStr(1,[CellBock7],"*") Or (Len([CellBock7])=0))

Mitch
0
Comment
Question by:dbqluthier
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 12
  • 7
32 Comments
 
LVL 75
ID: 35168098

try this:
Trap6: (InStr(1,[CellBock6],Chr(42)) > 0 Or Len([CellBock6])=0)
0
 
LVL 75
ID: 35168120
Example:

SELECT Table1.*
FROM Table1
WHERE (((InStr(1,[CellBock6],Chr(42))>0)=True)) OR (((Nz([CellBlock],"")="")=True));


,x
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35168233
WHERE Nz(CellBlock6,"*") = "*"
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 44

Expert Comment

by:GRayL
ID: 35168411
Sorry, In case there are characters after "*"

WHERE Nz(Left(CellBlock6,1),"*") = "*"
0
 

Author Comment

by:dbqluthier
ID: 35168431
To DatabaseMX:
I can only work with VBA Code - SQL statements are a little beyond me.

I did try the first example, but that gave the same thing I was getting ... every record in the database comes through.  There are some fields with data in them, but they do not start with an *.  I don't want to see these records.
0
 
LVL 75
ID: 35168447
"I can only work with VBA Code -"
ok ... but what you posted was a query expression:

Trap6: (InStr(1,[CellBock6],Chr(42)) Or Len([CellBock6])=0)
"Access 2007 query "

So ... is this in a query or not ?  If so ... then use the SQL example I posted ...  or post the SQL of your query.

mx
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35168488
Trap6: Nz(Left(CellBlock6,1),"*") = "*"
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35168489
Trap6: Nz(Left(CellBlock6,1),"*") = "*"
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35168500
Are you using the Query Builder? - that is not VBA code.
0
 

Author Comment

by:dbqluthier
ID: 35168568
This is my SQL code which is generated in Access 2007 (query designer).  I have little idea how to read the SQL part.  It looks rather incomplete to me.

SELECT tblInspecChData.MOS_IMKEY, tblInspecChData.IDNumb, tblInspecChData.OpNumb, tblInspecChData.CellBock5, tblInspecChData.CellBock6, tblInspecChData.CellBock7, tblInspecChData.CellBock8, tblInspecChData.CellBock9, tblInspecChData.CellBock10, tblInspecChData.CellBock11
FROM tblInspecChData;
0
 

Author Comment

by:dbqluthier
ID: 35168594
GRayL:

The example below gives me every record in the database ... with an * or data without an * or just a blank field.  I need a return for a field that either begins with an * or the field is blank.

Trap6: Nz(Left(CellBlock6,1),"*") = "*"
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 35168610
Try adding this to the SQL ... ie, paste this back into the query designer - SQL view.

SELECT tblInspecChData.MOS_IMKEY, tblInspecChData.IDNumb, tblInspecChData.OpNumb, tblInspecChData.CellBock5, tblInspecChData.CellBock6, tblInspecChData.CellBock7, tblInspecChData.CellBock8, tblInspecChData.CellBock9, tblInspecChData.CellBock10, tblInspecChData.CellBock11
FROM tblInspecChData
WHERE (((InStr(1,[CellBock6],Chr(42))>0)=True)) OR (((Nz([CellBlock],"")="")=True));
0
 

Author Comment

by:dbqluthier
ID: 35168813
When I run the query I get "Enter Parameter Value"  for CellBlock

I'm clueless on SQL

I don't think I'm able to explain this very well.
There are 10 fields I'm looking at.  The first 3 fields have no parameters. In the next 7 fields I'm looking for either the * character in the first position OR if that field has no characters at all in it.

Some fields will have data that does not start with an * and I don't want to see that record is if this is true.


temp01.jpg
0
 
LVL 75
ID: 35168836
Sorry ... I had a type in the post - corrected here

SELECT tblInspecChData.MOS_IMKEY, tblInspecChData.IDNumb, tblInspecChData.OpNumb, tblInspecChData.CellBock5, tblInspecChData.CellBock6, tblInspecChData.CellBock7, tblInspecChData.CellBock8, tblInspecChData.CellBock9, tblInspecChData.CellBock10, tblInspecChData.CellBock11
FROM tblInspecChData
WHERE (((InStr(1,[CellBock6],Chr(42))>0)=True)) OR (((Nz([CellBlock6],"")="")=True));

And this is just for one CellBlock case (6) ... but lets get this working first.

mx
0
 

Author Comment

by:dbqluthier
ID: 35168907
I still get every record in the database.

In the 2nd filie I uploaded temp02.jpg you can see CellBock6.  There are blank field, fields starting with an * and fields with data and no starting *.  Records that have data in CellBock6 that do not start with an * I do not want to show up in the query.

Mitch
temp01.jpg
temp02.jpg
0
 
LVL 75
ID: 35168986
Can you upload a db the does this ... and show exactly how to reproduce the issue.

thx.mx
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35169769
Problem with the spelling.  That's why you the the Parameters Window.

This:  Trap6: Nz(Left(CellBlock6,1),"*") = "*"

Should be:  Trap6: Nz(Left(CellBock6,1),"*") = "*"
0
 

Author Comment

by:dbqluthier
ID: 35181661
The last SQL statement works for CellBock6.

Hopefully, I'm getting better at explaining myself.  I can see now that I could have clearer.

I'm sending a smaller database to work with and also included PDF file query results.

CellBock5 should always start with an * ... the rest of the columns 6 thru 11 should format like CellBock6.

Thanks for your patience with me.  It's beginning to make sense.

Mitch
CChartsTest.mdb
EE-CCharts01.pdf
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 250 total points
ID: 35182998
I ran this query and got the identical results to your qrsPrestage2-EE

SELECT tblInspecChData.*
FROM tblInspecChData
WHERE Nz(Left(CellBock6,1),"*")="*";

Are you trying to identify all records in which either the first character of CellBock5 to CellBock11 begins with an ansterisk or the field is null?  I thought that was the case from your question but after reviewing your PDF file I'm not so sure.  If indeed that is the case run this query:

SELECT tblInspecChData.*
FROM tblInspecChData
WHERE
Nz(Left(CellBock5,1),"*")="*" OR
Nz(Left(CellBock6,1),"*")="*" OR
Nz(Left(CellBock7,1),"*")="*" OR
Nz(Left(CellBock8,1),"*")="*" OR
Nz(Left(CellBock9,1),"*")="*" OR
Nz(Left(CellBock10,1),"*")="*" OR
Nz(Left(CellBock11,1),"*")="*";
0
 

Author Comment

by:dbqluthier
ID: 35183521
The attached PDF file is my attempt to clarify what is needed from the query.  i can't add anything here that in not in the attachment.

Mitch
EE-CCharts02.pdf
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35184832
Time for a recap.  You have 7 fields named CellBockn in the table.  You stated qrsPrestage2-EE gave correct results for CellBock6 yet there are several other CellBock fields which are Null, several records in which CellBock5 start with a '*'.  It may be clear to you what you want but the water is still pretty muddy at this end.  Can we go back and state:

If Cellbock5 <Some Condition> Show me these records
If Cellbock6 <Some Condition> Show me these records
etc.

Are these conditions ANDed or ORed?  All this aside, perhaps a better way.  I see the large table tblInspecChDataOld with over 24k records and the smaller table tblInspecchData with 93 records - this is the smaller database?  I take it if you are happy with the query on the 93 records, you will be able to 'trust' the results on the table with 24k+ records.  Can you show after running the 'yet to be invented' query which of those 93 records you want returned?

0
 
LVL 75
ID: 35184851
gRay ... just don't end up in one of those Cell Blocks !!

:-)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35185776
mx:  That's why I only deal with CellBocks ;-)
0
 

Author Comment

by:dbqluthier
ID: 35188988
(1) If CellBock 5 does not start with an * do not show me this record (all records have to past this test)
 (2) If any CellBock 5 through 11 has data, but does not start with an * do not show me this record.
    If we're still in the game after statements 1 and 2, then look at CellBock 5-11 and if they are in any combination of starting with an * or being null (" ") then show me the record.

Yes the database is a sub-set of the 24k base.  This is only a piece of the logic needed.

I'm thinking that just using query is not going work.  If I use AND I limit the records and if I use OR I just get everything (or nearly).

I can use the query to pass the CellBock name and the first character to a function, etc.
0
 

Author Closing Comment

by:dbqluthier
ID: 35189915
I did learn some tools from this.  The database I was given (from the company I work for) was not designed to provide the answers we need.  I think we will need restructure the database or manually configure a new field.

My thanks to EE for working with me on this problem.  It was somewhat helpful.

Mitch
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35194919
Mitch, I have a solution.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35195004
In your test recordset it returns 21 records.

In the huge recordset it returns 1052 records, but then you have the fields CellBock1 to CellBock4.
0
 

Author Comment

by:dbqluthier
ID: 35197949
GRayL

So how did you solve it?  Even if we can't get 100% (larger database), it would be useful to get close.

Mitch
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35199303
The solution involves a different approach from what you put forth in your question and what both DatabaseMX and myself proposed as solutions which you then accepted.  I think we need a new question.
0
 

Author Comment

by:dbqluthier
ID: 35199519
I'll post a new question: MS Access  - How to get order from a non-structured database.

Most screen shots of the forms used and an explanation (story problem) may be helpful, too.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35199746
Mitch:  If you copy the address of the new question and paste it back here, we will all be notified when you post the new question.
0
 

Author Comment

by:dbqluthier
ID: 35200345
I started a new Ask a Question at the URL below.  I also uploaded new files to explain what the task is.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26906630.html

Mitch
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question