Solved

Awkward Query

Posted on 2001-09-06
35
298 Views
Last Modified: 2008-02-01
I am stumped on this one.

I have a 'search' form where users can select/enter criteria to search on. Five of them are date to and from boxes for 5 different date fields.

When the user types in a from/to date the query is simple, I use a between forms!frmMenuInquiry!txtFrom and to syntax. This works. However, how do I get it to work if the user doesn't enter a date? ie a null entry means find everything. One way would be to create a query for every eventuality for the form and use a Select Case statement to see if a date has been entered in the date field. That would be ok for one maybe two dates, but 5 results in over 20 different possible combinations.

Does this make much sense?
0
Comment
Question by:mksilk
  • 10
  • 9
  • 8
  • +6
35 Comments
 
LVL 15

Expert Comment

by:ericpete
ID: 6462970
It's been a while, but it seems like there's a sample application somewhere that shows how to use one form that selects different queries chosen from a list of several, which you could use to narrow down the search. In other words, create a query for the potential date range, and another for everything else.

My email is under my handle; I did this a couple of years ago at work, but since I won't be in the office until Monday to send it, maybe someone else will come up with something.
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6462977
You can write a funtion that reads the text boxes and if no date (is null) then return like"*" or something like that. I can help you if you like.


Cheers
Marcus
0
 

Author Comment

by:mksilk
ID: 6462992
Marcus,

Sounds good. I was thinking along those lines, but how do I place that into the query itself?
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6463000
In the QBE grid of the query in the Criteria section you place the function name  - i.e. BetweenDates()
I am just writing a sample function for you will be a couple of mins
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6463015
This is quick but here goes;

this is an example of you could do

Function FirstDate() As Date
Dim strVal As String
If Forms![Form1]![txtDate] > #1/1/1900# Then
strVal = "Forms![Form1]![txtDate]"
Else
strVal = "1/1/1900"
End If
BetweenDates = strVal
End Function

If you make a function for each textbox then you can place something like Between FirstDate() And SecondDate().


And in the function if the textbox is blank make the first date 1900 or some thing and the second date year 2100.


Sorry this a bit rough but I am on my way home.

Cheers
Marcus
0
 

Author Comment

by:mksilk
ID: 6463027
Marcus,

Like your thinking, but how would that get around the problem of a null entry in a date field in a record? ie by placing 1900 and 2100 as the extreme parameters, some fields may not even have an entry and by not entering any dates in the to and from search fields  should find all records, with or without dates.
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6463035
if you look at the code it does the same thing of placing extreme dates in if the fields are blank.
0
 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6463042
What are you actually doing with the data?

Opening a query, report...?

What about filters?

When the search button is clicked:
stDocName = "Query/Form/whatever you are opening"
DoCmd.OpenQuery(or form or whatever) stDocName, acNormal, acEdit
DoCmd.ApplyFilter stDocName, "fieldname between Forms.[searchformname]![searchfield1] and  forms.[searchformname]![searchfield2]"
0
 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6463046
Ack...need an if statement before the ApplyFilter.
0
 

Author Comment

by:mksilk
ID: 6463054
Marcus,

Yep, I know it puts in dates if the date fields are blank but it will not find any records whose date fields are blank....
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6463073
Thats what it will handle.
0
 

Author Comment

by:mksilk
ID: 6463084
But it doesn't, it will only find records that have a date entry.eg

Record#  Date
1       01/01/2001
2       02/01/2001
3       03/01/2001
4       Empty
5       04/01/2001
6       05/01/2001
7       06/01/2001

If txtfrom is blank then your function will make txtfrom = 1/1/1900 and txtto = 01/01/2100 this will find records 1,2,3,5,6,7 but *not* record 4. I want the query to say if the txtfrom and txtto are blank then find all...

Sorry about the confusion.








0
 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6463086
He means in the table.  If the date field in the table is blank, that will not find them.

dill
0
 

Author Comment

by:mksilk
ID: 6463093
sure do! (I suppose I could have said that earlier on!)
0
 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6463103
I like the filter suggestion.

The above would work for a query.

For a report, it'd look like:
stDocName = "ReportName"
If isnull(Forms.[searchformname]![searchfield1]) and  isnull(forms.[searchformname]![searchfield2]) Then
  DoCmd.OpenReport "ReportName", A_PREVIEW
Else
  DoCmd.OpenReport "ReportName", A_PREVIEW, , "fieldname between Forms.[searchformname]![searchfield1] and  forms.[searchformname]![searchfield2]"
End If
0
 

Author Comment

by:mksilk
ID: 6463107
Yeah, but I have 5 date fields and 17 text fields the user can search on. Which makes it very difficult to do as a filter. I think I may have to set up a Select Case statement and have 20 different queries to match the 20 different outcomes.
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6463114
OH OK you can put Or Is Null in the query grid.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6463130
How about:

filter = ""

If Forms.[searchformname]![searchdatebeg1] > "" and Forms.[searchformname]![searchdateend1] > "" Then
  addfilter("(date1 between " & Forms.[searchformname]![searchdatebeg1] & " and " & Forms.[searchformname]![searchdateend1] & ")")
End If

If Forms.[searchformname]![searchdatebeg2] > "" and Forms.[searchformname]![searchdateend2] > "" Then
  addfilter("(date2 between " & Forms.[searchformname]![searchdatebeg2] & " and " & Forms.[searchformname]![searchdateend2] & ")")
End If

...blah blah blah...   more Ifs here

DoCmd.OpenReport "ReportName", A_PREVIEW, ,filter


Sub addfilter(newfilter as text)
  If filter = "" Then
    filter = newfilter
  Else
    filter = filter & " and " & newfilter
  End If
End Sub



Or something like that.  Off the cuff you know.

dill
0
 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6463133
   "OH OK you can put Or Is Null in the query grid. "

**slaps forehead and says 'DOH'**
0
 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6463145
Oooo, that is one hella query grid taking all fields into account that are going to be checked.
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6463148
Hey its 4:40 on Friday afternoon here and its been a looong week.....
0
 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6463154
HAHAHA, it is 1:37 on Friday morning here.

Nearly bedtime.

Can each field have:

(Between fielda and fieldb) or (Is Null)

or does Access move the Is Null down to the next grid?
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 6463164
yes it can

SELECT Table1.a, Table1.b
FROM Table1
WHERE (((Table1.a) Between #1/1/01# And #12/31/01# Or (Table1.a) Is Null));
0
 

Author Comment

by:mksilk
ID: 6463167
Access moves it down to the next grif so currently I am up to row 4 using 3 date fields. But it could work...

Am not a fan of the filter method 'coz I will have too many search criteria.

This is a classic example where I get very infuriated with Access as all I want is a flexible search engine for a database but Acces doesn't seem to want to play. Mind you it is 5pm here and time for the weekend.
0
 
LVL 2

Expert Comment

by:JamesMayfield
ID: 6463550
In your SQL put...


Iff (IsNull([Forms]![MyForm]![MyControl].[Value]),Like "*",">([Forms]![MyForm]![MyControl].[Value]))

It works best if you use raw SQL instead of a Query, 'cause then you can modify it as a string.
0
 
LVL 4

Expert Comment

by:zuijdhoek
ID: 6463798
One way to get around this problem is to provide the to and fro textboxcontrols  with default values.
For example: set the defaultvalue property of txtFrom to the first date and the defaultvalue property of txtTo to the latest date . In case a user blanks out one of these textboxes this default value will be restored by implementing AfterUpdate-events. Following code will hopefully explain you a lot more. It is the code implemented on a form with two textboxes to specifiy the to and from createria, a commandbutton to refresh the recourdsource of a subform and a subform.

' this events will be fired in case a click on the commandbutton (cmdFilter)
' in order to change the recordsource of the subform (subTime) which displays the dates
'
Private Sub cmdFilter_Click()
  Dim strSource As String
  strSource = _
   "SELECT * FROM tblTime WHERE " & _
   "[Date] BETWEEN #" & txtFrom & "# AND #" & txtTo & "#"
   
  subTime.Form.RecordSource = strSource
 
End Sub

' retrieve first date in case a user blanks out textbox txtFrom
'
Private Sub txtFrom_AfterUpdate()
  If IsNull(txtFrom) Then
    txtFrom = DMin("Date", "tblTime")
  End If
End Sub

' some validationstuff
'
Private Sub txtFrom_BeforeUpdate(Cancel As Integer)
  If Not IsNull(txtFrom) And Not IsDate(txtFrom) Then
    MsgBox "Please enter a valid date", vbExclamation, "Invalid entry"
    Cancel = True
  End If
End Sub

' retrieve last date in case a user blanks out textbox txtTo
'
Private Sub txtTo_AfterUpdate()
  If IsNull(txtTo) Then
    txtTo = DMax("Date", "tblTime")
  End If
End Sub

' some validationstuff
'
Private Sub txtTo_BeforeUpdate(Cancel As Integer)
  If Not IsNull(txtTo) And Not IsDate(txtTo) Then
    MsgBox "Please enter a valid date", vbExclamation, "Invalid entry"
    Cancel = True
  End If
End Sub


Bottomline is make sure all searchcriteria are defined. This simplefies creating new recordsources and makes you code far more readable. If you want to offer your users this kind of flexibility you have to do some work.

Mark    
0
 
LVL 1

Accepted Solution

by:
rscaiefe earned 100 total points
ID: 6464255
I have had problems similar to this and have programmed around it by creating two querys: a template query and the actual run query.  For example, the template query contains "select * from orders;"

Then, in code, I read the template query, modify the run query then open the report

dim q1 as querydef
dim q2 as querydef
dim db as database
dim SQL as variant
dim AddAnd as variant
set db=currentdb()
set q1=db.querydefs("Template query name")
set q2=db.querydefs("Run query name")
SQL=q1.sql

' now, modify the SQL statement
sql=replace(sql,";","where (") ' replace the trailing ; with the where clause

' add in the tests you want
if isnull(form1!text1) = true ' I always use =true for readability
    ' no need to do anything - will return all dates
else
    if addand = 1 then  ' only required after first field
        sql=sql & " and "
    endif
    sql=sql & "orders.date1 between " &_
        format(form1.text1,"\#mm/dd/yyyy\#") & _
        " and "
        format(form1.text2,"\#mm/dd/yyyy\#")
    AddAnd = 1
endif

' repeat the last block for each field you want

' finish the query after last field
sql=sql & ");"

' now save it in the "run" query
q2.sql=sql

' wrap up
q1.close
q2.close

' q2 now has the modified SQL
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6465440
mksilk:

One question for you, which someone else has asked, but seems to have been overlooked:

What are you doing with this query/filter once you've generated it? Are you using it to restrict the view of records in a form? Are you using it to specify the criterea for a report? etc.


As to your question of what to do when the user leaves the To and From date fields blank, the answer is simply DO NOTHING. You simply do not have to do anything in regard to that field.


For example, suppose you have an Points field, and a DateTested field. Also suppose you have 3 field objects on your search form:  txtPoints, txtDateFrom, and txtDateTo

You could then add the following function to your form's code module (this makes the assumption that if the user fills in one date field, the user also has the other date field filled in -- that is, both date fields are filled in or neither are filled in):

Function ComposeFilter() As String
   Dim Crit As String 'Criterea expression

   If Not IsNull(txtPoints) Then
      If Len(Crit) Then Crit = Crit & " AND "
      Crit = Crit & "([Points]=" & txtPoints &")"
   End If
   If Not IsNull(txtDateFrom) Then
      ' **** We assume that if one date field is filled in,
      ' **** then both date fields are filled in
      If Len(Crit) Then Crit = Crit & " AND "
      Crit = Crit & "([DateTested] BETWEEN #" & txtDateFrom & "# AND #" & txtDateTo &")
   End If

   ComposeFilter = Crit
End Function


This function will now return the criterea for your search form. If you think of this in terms of a query, this will give you the part you would use for the WHERE clause, without the word "WHERE".

You can use this as the Filter property of your form or report; you can use this as the WHERE clause of a Query you are generating, or the WHERE clause of a on-the-fly SQL Statement you are generating.



Make sense?


-Dennis Borg
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6465442
I had a small typo in the following line:


   Crit = Crit & "([DateTested] BETWEEN #" & txtDateFrom & "# AND #" & txtDateTo &")


This should actually read:

   Crit = Crit & "([DateTested] BETWEEN #" & txtDateFrom & "# AND #" & txtDateTo & "#)"


-Dennis Borg
0
 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6465458
Ummm, that one makes sense to me...hehehe
0
 

Author Comment

by:mksilk
ID: 6465755
DennisBorg - the objective is to produce a report. I am querying a database with about 5000 records growing at about 3 records a day.

rscaiefe  - I like your suggestion, I think in a simpler query it would work but mine seems to be very complex - 5 date fields that can be blank or filled in and 17 possible text fields to search on. Yes the client is a pain in the a$*e.

0
 
LVL 2

Expert Comment

by:Mavreich
ID: 6469432
This might be out in left field...  I have a sample database called Query by Form.  Below is the readme associated with the database.

If you would like a copy please email.
mavreichh@hotmail.com

nb.. Last line reads.... "you can use it to create a filter on a different form or to create a new
table, or you can save it for later use."  

You could base your report of the new table created using this data.

Good luck


======================================================================
Qbf.exe Readme File
April 1997
======================================================================

----------------------------------------------------------------------
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY    |
| ACCOMPANY THIS DOCUMENT (collectively referred to as a Microsoft   |
| Software Library [MSL] file) IS PROVIDED "AS IS" WITHOUT WARRANTY  |
| OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT        |
| LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR        |
| FITNESS FOR A PARTICULAR PURPOSE. The user assumes the entire risk |
| as to the accuracy and the use of this MSL file. This MSL file     |
| may be copied and distributed subject to the following conditions: |
| 1) All text must be copied without modification and all pages must |
| be included; 2) If software is included, all files must be copied  |
| without modification; 3) All components of this MSL file must be   |
| distributed together; and 4) This MSL file may not be distributed  |
| for profit.                                                        |
|                                                                    |
| Copyright 1997 Microsoft Corporation. All Rights Reserved.         |
| Microsoft is a registered trademark of Microsoft Corporation.      |
----------------------------------------------------------------------

----------------------------------------------------------------------
| This sample was adapted from "Microsoft Access 2 Developer's       |
| Handbook" by Getz, Litwin, and Reddick (Sybex)                     |
| Copyright 1994 - 1997.  All rights reserved. (ISBN 0-7821-1327-3)  |
|                                                                    |
| The "Access 97 Developer's Handbook" is also available from Sybex. |
| (ISBN 0-7821-1941-7)                                               |
----------------------------------------------------------------------

Introduction
------------

The Qbf.mdb is a sample Microsoft Access 97 database. It includes one
form and two modules that allow you to create a query-by-form form for
any form in your own database. The Microsoft Access 97 filter-by-
form built-in feature isn't available in a run-time application. The
purpose of this sample database is to provide developers a filter-by-
form substitute in run-time applications.

The Qbf.exe file contains the following files:

   File Name      Size      Date       Description
   ----------------------------------------------------------------------

   Qbf.mdb        169,984   4-9-97     Create Filter-by-Form Form database

   Readme.txt                          Information and instructions

Installation
------------

To test Qbf.mdb, move it to your working directory and open it with
Microsoft Access 97. To use the functionality in your database, import
the following objects into your database:

   Object Name      Object Type
   ------------     -----------
   frmCreateQBF     Form
   basParse         Module
   basBuildSQL      Module

Summary
-------

There are two steps involved in creating the QBF form and using it:

1. At application design time, open the frmCreateQBF form and create QBF
   forms for all forms for which you want QBF functionality. You will then
   have two copies of your form; the original one, and the one that
   you use for QBF input, for example, Customers and Customers_QBF. If
   you want your users to use the QBF form like the filter-by-form built
   -in functionality, add a button to the original form that opens the
   associated QBF form.

2. At run time, the user opens the QBF form, fills in any criteria, and
   clicks the OK button at the bottom of the QBF form. This runs the
   glrDoQBF() function, and then opens the original form with the records
   filtered as per the user's criteria.

The concept of this database is simple: you call the glrDoQBF() function,
specifying the form you want to use. The glrDoQBF() function pops up a
modal form, with one control per field from the underlying dataset. You
can enter text into these controls, make choices from list boxes, or
click check boxes just as though you're entering data. Once you've indicated
that you're finished, the code goes through each control on the form.
The contents of controls that have been specially prepared and have
values in them are added to an SQL expression, which are returned to your
code. At this point you can do whatever you like with that SQL expression:
you can use it to create a filter on a different form or to create a new
table, or you can save it for later use.
0
 
LVL 8

Expert Comment

by:DennisBorg
ID: 6471113
mksilk:

>DennisBorg - the objective is to produce a report. I am
>querying a database with about 5000 records growing at
>about 3 records a day.

Then you can use the WhereCondition parameter of the OpenReport method of the DoCmd object.

For example, if you used a function similiar to the ComposeFilter() function I gave as a sample above, you could have the following code behind your "Print" button on your form:

   DoCmd.OpenReport "MyReport" , , , ComposeFilter()

This would cause your report to be opened, but filtered as the user instructed on the form.


-Dennis Borg
0
 

Author Comment

by:mksilk
ID: 6492522
Have done a very similar solution using temporary tables. Seems very tricky for what seemed a simple problem. Thanks for all your input.
0
 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6745584
mksilk,

In an effort to help clean up the open questions, I am going thru old questions that I have participated in.

This question needs closed either by deleting it or PAQing it.

Please do one of the following:
1. Award points.  If a point split is necessary, post a zero point question in Customer Service specifying how to split them and a link to the question.

2. If you felt any help was worth points, then post a zero point question in the Customer Service area, with a link to the question, asking for points to be reduced then award them.

3. Otherwise, just delete it.

Thanks,
dill
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 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

12 Experts available now in Live!

Get 1:1 Help Now