Please Help with Query

[cdl1701]

I have been trying to get this to work for about 2 weeks now with verry little luck. Can some one take a look at my DB and tell me what I am doing wrong

This post decribes what I am doing

http://www.tek-tips.com/viewthread.cfm?qid=1196043&page=1

This thread is the problem that I am having now

http://www.tek-tips.com/viewthread.cfm?qid=1196816&page=1

 

[MajP]

However, if normalizing is too much work then make an "Items" option group. In actuallity it makes the code easier, although not very flexible. Something like

Select Case Me.optGrpItems
Case 1
itemWhereClause = "(TransPorter = True)"
Case 2
itemWhereClause = "(BeanieBears = True)"
Case 15
itemWhereClause = "(Clothing = True)"
End Select

Then still have another option group for autograph

Select Case Me.optGrpAutograph
Case 1
autographWhereClause = "(blnHasAutograph = False)"
Case 2
autographWhereClause = "(blnHasAutograph = True)"
Case 3
autographWhereClause = ""
End Select

 

[MajP]

Yes. Here are a few of many reasons why. As a user, I could have an items type table that I use as a lookup table. I could add a new item to this and I am done. In your design I would have to redesign the table. Instead of querying fifteen fields, I only query one field.

 

[cdl1701]

That is true.. If I do it this way then the standard

[Forms]![Search]![Manufacture] Or [Forms]![Search]![Manufacture] Is Null

in a query would work fine wouldn't it?

one more question. In my Year field it is formated as text but I am putting 2000, 2001, 2002...etc. Does it matter that it is set up as text or should it be number.

 

[MajP]

The year thing matter depending on what you want. For this simple database probably not. The big difference is the criteria expressions in sql using string or other numeric.

Example

dim myStr as string
dim myInt as integer
dim strWhere as string

myStr = "123"
myIne = 123

strWhere = "[fldName] = '" & myStr & "'"
or
strWhere = "[fldName] =" & myInt

depending on how you reference a date you may also have to use #1/1/2006# . The pound represent a date is enclose.

I do not understand the first part of your question.

 

[cdl1701]

Oh for my first part of my question was if I create a Query using the query wizard and place

[Forms]![FrmSearch]![ChbManufacture] Or [Forms]![FrmSearch]![ChbManufacture] Is Null

in the criteria for the query field (in this case the Manufacture field)this will allow me to select the criteria from the search form before running the query. Putting the OR and Is Null lets me select or leave fields in the search form blank and still have the query be able to run.

 

[lespaul]

have you read the fundamentals document linked below to understand what "normalizing" the database means and how to accomplish it?

If you are going to fix the tables then your existing questions are meaningless. You will have something completely different after you fix the tables.

HTH



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[cdl1701]

No, I have not. Thanks for the link by the way I will read it and endeavor to apply it to my DB. Would you guys have any other suggestions on which books are good to get started on? Access, VBA & SQL. I have looked at allot of them in the book stores but am just unsure where to get started.

 

[MajP]

I think we are coming full circle. The approach that I was going at was to do away with parameter queries, and allow you to build a "where" string useable in many different places. Personally, I hate parameter queries and never use them. A lot of people use them, but I just think they are completely inflexible and present a bunch of ugly message boxes.

In my idea you are presented with a form. You make your choices. Click go and the report, form, or query opens with the proper records selected.

However, I am interested in how this works. I have never tried this. I am pretty good with boolean logic, but I need this explained to me.

[Forms]![FrmSearch]![ChbManufacture] Or [Forms]![FrmSearch]![ChbManufacture] Is Null

The way I interpret this is

value or True = Value
null or True = null

 

[lespaul]

here's another useful link:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/

 

[PHV]

MajP, the expression is entered in a criteria cell of the query grid.
The generated SQL is then something like this:
SELECT ...
FROM ...
WHERE ([some field]=[Forms]![FrmSearch]![ChbManufacture] Or [Forms]![FrmSearch]![ChbManufacture] Is Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

PHV I know how to use it, but my problem is with the logic table. Is this right?

Case 1: The control is not null

control.value) Or (contorl.value Is Null)
value or False
= value
That would makes sense

Case 2: The control is null

(control.value) Or (contorl.value Is Null)
Null or False
=Null
This is what confuses me. Is this equivalent to no criteria

 

[PHV]

MajP, in your Case 2, the right part of the OR is TRUE.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886