Expression using AND, OR 2

[jgeneie]

hi all,

i have a problem displaying my results.

i have created a table with 3 fields: "customername","dealers" and "ISP"

-customername will store names of customer( obviously )
- ISP and dealers data type are just yes/no

lets say i have created a OR expression between "dealers" and "ISP". when i select "dealers" and "ISP".

customername dealers ISP
------------ ------- ---
bob yes yes
cat yes
dog yes

but all i want is BOB's name to appear.

But lets say i use AND expression instead and select the same 2 fields: dealers and ISP

customername dealers ISP
------------ ------- ---
bob yes yes

that will satify my criteria but lets say i only select "dealers", "bob" name will not appear but instead only
"dog" will appear.

thats not i expect, i expect "dog" and "Bob" to appear.

how do i solve this problem using expressions?

 

[jgeneie]

hi jim,
thanks for your help
u are a life saver

can u send to another email as well juz in case it doesnt get through

space_7@hotmail.com

thanks again

 

[JimHorton]

jgeneie,
Sure, I'll send it tonight...
--Jim

 

[JimHorton]

jgeneie,
Did you get the email?
--Jim

 

[jgeneie]

hi jim,
i got the attachment but i cannot open the database due to some permission problem.

its states: " the current user account doesn't have permission to convert or enable this database

regards

 

[JimHorton]

jgeneie,
ooops...
That's me using my normal development user's logon. I'll email it to you, you can then create the user and open it from there.
--Jim

 

[jgeneie]

hi jim,
i have received your second email but still cannot open the database due to same permission error

regards

 

[JimHorton]

jgenie,
Just in case, I've posted the main bit of code here. They key is to name the checkboxes on the form to a common standard, and I've used:
"chkNNNNN" Where NNNNN is the exact table field name. It is also highly recommended to never use spaces, dashes reserved words, or any 'special' characters in any fieldnames, not just to facilitate this type of coding, but it's just a general rule of clean programming.

With these characters, Access needs to put [SQUARE BRACKETS] around table & fieldnames, and it makes sql and code look VERY messy and hard to read, especially when you have lots of parenthesis.

The below runs on the click of a button named btnRun. Query2 can start out being *anything*, ie select * from [customer profile].

Private Sub btnRun_Click()
'This is a better way to deal with dynamic criteria
'BTW, a personal preference of mine, and something I highly recommend: It makes working with sql MUCH easier if you never use spaces in fieldnames, and make them short. You can always set the Caption property in the table design to be more descriptive for the end user. I stuck with your table-field names here, though. However, with no spaces, dashes, etc, you'd never ever need to deal with the brackets [] around table/fieldnames again, they can be a pain, especially when you've got lots of parenthesis too.


Dim sqWhere As String, qd As QueryDef, db As Database, sqSel As String, i As Integer
'First set db and query objects
Set db = CurrentDb
Set qd = db.QueryDefs("Query2&quot 'use any query you like
'Set the base select clause (note the leading space--personal preference, I like to end these lines on a comma, and some lines end with, say a table name, and I want to be in the habit of leading next line with a space, just in case. Also I put crlf's in so the line isn't too long for the query sql view
sqSel = "SELECT [Customer Profile].isp, [Customer Profile].telco, [Customer Profile].dealers, [Customer Profile].vendors,"
sqSel = sqSel & " [Customer Profile].[system integrator], [Customer Profile].[software developer], [Customer Profile].manufacturer," & vbCrLf
sqSel = sqSel & " [Customer Profile].others , [Customer Profile].[Mobile Network], [Customer Profile].[fixed line network], " & vbCrLf
sqSel = sqSel & " [Customer Profile].[public radio network], [Customer Profile].[Private Radio Network], [Customer Profile].[dial-up internet svc]," & vbCrLf
sqSel = sqSel & " [Customer Profile].[broadband internet svc], [Customer Profile].accessories," & vbCrLf
sqSel = sqSel & " [Customer Profile].hardware, [Customer Profile].software, [Customer Profile].other, [Customer Profile].[customer name]," & vbCrLf
sqSel = sqSel & " [Customer Profile].[company name], [Customer Profile].country, [Customer Profile].Designation," & vbCrLf
sqSel = sqSel & " [Customer Profile].department, [Customer Profile].address, [Customer Profile].[website address]," & vbCrLf
sqSel = sqSel & " [Customer Profile].[email address], [Customer Profile].[email address], [Customer Profile].[office number]," & vbCrLf
sqSel = sqSel & " [Customer Profile].[ext number], [Customer Profile].[mobile number], [Customer Profile].[pager number]," & vbCrLf
sqSel = sqSel & " [Customer Profile].[fax number], [Customer Profile].[home number], [Customer Profile].connector," & vbCrLf
sqSel = sqSel & " [Customer Profile].notes , [Customer Profile].[last update]" & vbCrLf
sqSel = sqSel & " FROM [Customer Profile]" & vbCrLf

'Now, here's where Naming makes a big difference:
'I named all the checkboxes identical to the fieldnames, but with a 'chk' prefix. (again, shorter names w/no spaces, etc mean less coding). If there happen to be any checkboxes not meant to be used in query can be prefixed in another way
For i = 0 To Me.Controls.Count - 1
If TypeOf Me(i) Is CheckBox Then
If Left$(Me(i).Name, 3) = "chk" Then 'here we make sure only 'chk' prefixed boxes are used--we might happen to have others on the form, but they can be prefixed differently
'Now, here's the key. If the box is NOT null, then we use it in the query, else, we ignore
If Not IsNull(Me(i)) Then
'The query just directly says: "And Field = True" ie, or "AND Field = False" depending on value of checkbox.
'Note the parsing of the right side of the checkbox name--it is same as fieldname, and we use Mid(), with the Start of 4 (after 'chk'), and the len() is the TOTAL, which overshoots by 3, but who cares--Mid() lets us be lazy and knows where the string really ends if we overshoot. Just us Len(me(i).name) to make sure we can't undershoot length.
sqWhere = sqWhere & " AND [customer profile].[" & Mid$(Me(i).Name, 4, Len(Me(i).Name)) & "] = " & Me(i)
End If 'endif Not Null me(i)
End If 'endif 'chk'
End If 'endif Typeof is Checkbox
Next i 'next Control on form
'Now deal with texboxes, only 2 and we know their names
If Nz(Me.txtComanyName, &quot;&quot <> &quot;&quot; Then
sqWhere = sqWhere & &quot; AND [customer profile].[Company name] = &quot;&quot;&quot; & Me.txtComanyName & &quot;&quot;&quot;&quot;
End If
If Nz(Me.txtCustomerName, &quot;&quot <> &quot;&quot; Then
sqWhere = sqWhere & &quot; AND [customer profile].[customer name] = &quot;&quot;&quot; & Me.txtCustomerName & &quot;&quot;&quot;&quot;
End If

'Strip the leading 'AND'
sqWhere = Trim(sqWhere)
sqWhere = Mid$(sqWhere, 4, Len(sqWhere))
'put it together
qd.SQL = sqSel & &quot; WHERE &quot; & sqWhere
'Either open the query, or report that it's based upon
DoCmd.OpenQuery qd.Name
End Sub

Let me know if this does it, it works like a charm on the mockup I made.
--Jim

 

[JimHorton]

jgeneie,
BTW, be sure to make the checkboxes TripleState, so they can be set to Grey (NULL)--which means 'don't use in criteria'.
--Jim

 

[jgeneie]

hi jim,

what more can i say, u r a genius!
btw how do i display my resulted query in a report rather than a table format.

regards

 

[JimHorton]

jgeneie,
All you need to do is base a report on that query, and use:
docmd.openreport &quot;report name&quot;
...instead of docmd.OpenQuery

No criteria is required in the docmd. call, since that's taken care of.
--Jim

 

[jgeneie]

hi jim,
regarding your sample program.
the chkboxes has 3 states rt?
if i leave the chkbox of &quot;dealers&quot; state as &quot;white box&quot; instead of &quot;gray shade&quot;, the query will still look for those not involve in &quot;dealer&quot;. Is there anyway i can go around this?

regards

 

[jgeneie]

hi jim,
I still not able to able to display out as a report/form directly.
i need to query it out as a table i display it to a from/report. is there a way to display it directly.

i have tried : DoCmd.OpenForm &quot;customer profile&quot;

where &quot;customer profile is my form name

regards

 

[JimHorton]

jgeneie,
For the first quesion--the 'white' or 'grey'--when you select Grey, this is NULL, and the criteria is not considered at all. If the box is White, then the criteria is:
FieldValue = False The purpose of the tri-state is so that you can have the option of ignoring that boolean field in criteria, or using it, since it's only 2 values are True and False, you need the third, 'grey' state that says 'Don't use this field at all in criteria'

As far as reports, go to the Report Wizard, and create a report based on the query you're using in the code (query2 in my example. Then design it the way you want, do the arranging of fields, etc, and sorting or grouping if you like. Then, lets say it's named Report2, you'd do:
docmd.openreport &quot;Report2&quot;

...in place of the docmd.OpenQuery statement. This should work,
--Jim

 

[jgeneie]

hi jim,
how do i make use of a pop up menu to &quot;count&quot; the number of records that is within my criteria and display a &quot;no record menu&quot; if no record is found??

eg.

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox &quot;There are no attendees for this event.&quot;, vbInformation, &quot;No Matching Records&quot;
End If
End Sub

*i have used this method but i have an error msg that says:
&quot;Run time error 2501&quot;
the openform action was cancelled

I understand that the because i have a form displayed if there are records, so how do i get rid of the error msg

regards

 

[JimHorton]

jgeneie,
You need to change 2 things: First, in that forms Open event, you must first do:
me.RecordsetClone.MoveLast
That will give an accurate count, since the Clone does not visit each record when it opens, so it has no idea how many are there--it just opens the clone and goes to the first record, unaware if there are hundreds more or whatever.

Second, in the event where you call the docmd.openform method, you could either put an On Error Resume Next just prior to that call, and if there is any previous On Error GoTo XXXX statement, you should put that statement in right after the OpenForm call (unless it's the last line in the procedure, in which case it wouldn't matter), or if there is that previous ON Error goto xxxx, then in the xxxx block, put:
If Err.Number = 2501 then
Resume Next
ElseIf err.Number = nnnnn 'your other handlers.

--Jim

 

[jgeneie]

hi jim,

sorrie but i not very sure what u toking about

can u give me an example using the previous sample u sent me??
thanks

 

[jgeneie]

hi jim,

sorrie, but i dnt really understand what u mean

can u give me an example using the previous code u previded me

thanks

 

[JimHorton]

jgeneie,
Here is an abbreviated version of the code, with just the relevant stuff:

Private Sub btnRun_Click()
Dim sqWhere As String, qd As QueryDef, db As Database, sqSel As
String, i As Integer
On Error Goto ErrRun

'Strip the leading 'AND'
sqWhere = Trim(sqWhere)
sqWhere = Mid$(sqWhere, 4, Len(sqWhere))
'put it together
qd.SQL = sqSel & &quot; WHERE &quot; & sqWhere
'Either open the query, or report that it's based upon
'--In your example you use a form, not query--DoCmd.OpenQuery qd.Name
Docmd.OpenForm &quot;MyOtherForm&quot; '<--this is the form where you count the records

ExRun:
Exit Sub
ErrRun:
If Err.Number = 2501 then '2501=OpenForm action cancelled
'Do nothing, the msgbox was in the Form Open event
Resume ExRun
Else
'for now, all other errors are just messaged to the user
msgbox &quot;Error in the query creation: &quot; & err.Number & &quot; &quot; & err.description
Resume ExRun
End if
End Sub

Now, the form Open event, for the above form &quot;MyOtherForm&quot;
Private Sub Form_Open(Cancel As Integer)
'Below the eof and bof just tells if empty or not, but if you want to actually count..
'...you'd use: Me.RecordsetClone.MoveLast, then use the .RecordCount property
If Me.RecordsetClone.Eof and Me.RecordsetClone.Bof then
Cancel = True
MsgBox &quot;There are no attendees for this event.&quot;, vbInformation, _
&quot;No Matching Records&quot;
End If
End Sub
--Jim

 

[jgeneie]

hi jim,
using the code u provided about i am only able to do it on a form but not a report. it states &quot;compile error: method or data member not found&quot;

Private Sub report_Open(Cancel As Integer)
'have change form_open to report_open

If Me.RecordsetClone.Eof and Me.RecordsetClone.Bof then
Cancel = True
MsgBox &quot;There are no attendees for this event.&quot;, vbInformation, _
&quot;No Matching Records&quot;
End If

and can u explain the &quot;count&quot; part as i would like to count the number of records. u talk about the .RecordCount, but i dnt really understand

thanks

 

[JimHorton]

jgeneie,
For a report, you can use the NoData event, if all you want is to know whether or not the criteria has found something.
To get an easy count for the report's data, assuming there is no filter on the report, you could do the following:
...starting from a few lines before the Open in the original code...
Strip the leading 'AND'
sqWhere = Trim(sqWhere)
sqWhere = Mid$(sqWhere, 4, Len(sqWhere))
'put it together
qd.SQL = sqSel & &quot; WHERE &quot; & sqWhere
'Either open the query, or report that it's based upon
'Report's recordsource is the qd's query (Query2 I think)
Dim Rst as recordset
set rst = qd.openrecordset()
if rst.eof and rst.bof then
'no records, dont open form or report or query
else
rst.movelast
msgbox rst.Recordcount & &quot; Records found&quot;
docmd.openreport &quot;report name&quot;
end if

--Jim