Solved

Access 2007 query for a Character or no value

Posted on 2011-03-18
32
323 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
  • 13
  • 12
  • 7
32 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility

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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
WHERE Nz(CellBlock6,"*") = "*"
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Sorry, In case there are characters after "*"

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

Author Comment

by:dbqluthier
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
Comment Utility
Trap6: Nz(Left(CellBlock6,1),"*") = "*"
0
 
LVL 44

Expert Comment

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

Expert Comment

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

Author Comment

by:dbqluthier
Comment Utility
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
Comment Utility
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 - Access MVP) earned 250 total points
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Can you upload a db the does this ... and show exactly how to reproduce the issue.

thx.mx
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
gRay ... just don't end up in one of those Cell Blocks !!

:-)
0
 
LVL 44

Expert Comment

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

Author Comment

by:dbqluthier
Comment Utility
(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
Comment Utility
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
Comment Utility
Mitch, I have a solution.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now