08-28-08, 12:25 #1Registered User
- Join Date
- Aug 2008
Unanswered: Query linked to Form Text Box / Criteria unable to find records with a blank field
I have created a form (Frm_Lettersearch) that contains 5 unbound text boxes. Four of these boxes are used for inputing data. The other box is to be left untouched, as it is already linked seperately (i will explain a little further on).
The Four unbound text boxes for data input are:
The way things are set out to work is this:
I have a table (tbl_letters) that contains all the data for letters/post that are received. These letters are directly linked to a particular client, with the idea being that one client can have many letters. Each letter is given a unique number to ensure that each letter is entirely seperate. This autonumber is the primary key.
The form I have created enables the user to type in some details of a particular letter and retrieve that letter ID. For example, I type "David" in First Name text box, and "Jones" in surname. Each text box contains an event procedure (after update) which contains a Me.requery function. As the data is input, a query automatically runs and identifies a possible match on the data input. The letter ID of David Jones is then displayed in the fifth text box (not used for entering data).
Now then.. the query! The query is called SearchletterID. It is linked to the data from the tbl_letters and basically identifies the data in that table that corresponds with the criteria of the query itself. This criteria is determined by the data entered onto the form.
The way I had the criteria laid out in the query was as follows:
Field -----> First Name
Criteria---> Like IIf(NZ([Forms]![Frm_Lettersearch]![FirstName],"")="","*","*" & [Forms]![Frm_Lettersearch]![FirstName] & "*")
Field -----> Surname / Company
Criteria---> Like IIf(NZ([Forms]![Frm_Lettersearch]![Surname],"")="","*","*" & [Forms]![Frm_Lettersearch]![Surname] & "*")
Field ----->Date of Document
Criteria---> Like IIf(NZ([Forms]![Frm_Lettersearch]![DocumentDate],"")="","*","*" & [Forms]![Frm_Lettersearch]![DocumentDate] & "*")
Field -----> Date Received
Criteria ---> Like IIf(NZ([Forms]![Frm_Lettersearch]![DateReceived],"")="","*","*" & [Forms]![Frm_Lettersearch]![DateReceived] & "*")
As you may see, the idea was that If the text box (example Surname) was blank, it would not effect things.. where as if it contained data, the query would attempt to match that data.
The reason I needed it like this, was because I wanted it to work on the basis of whatever data you add, it would automatically search. So even if I only typed in the name "Jones" into the surname text box - and nothing else - it would still be able to find the letterID.
That was all working nicely.. but the problem that I now have is this:
Of these main 4 fields in the table (first name, surname, doc. date and date received) they do not always contain data! With a typical individual, I would have First Name: Jack Surname: Smith Document Date: 11/07/2008 etc.
However, with a company, there is no first name! And so the company name will go in the surname field so it will be like this:
First Name: (... blank!!! ...) Surname: Harrowsmith Enterprises Ltd. Document Date: 11/07/2008 etc.
This is how the data is entered into the tbl_letters. The field "First Name" does not always have data! sometimes it is completely blank.
Now then, the problem I have noticed is that when I try to use the form to identify the LetterID for a company, it will not be found! I have discovered that this is because of the blank field in the first name field of the tbl_letters. If I add a name to the [first name] field of the company, it will find it. As soon as i remove the first name of the company, the company will not be found again by the query. So the fault lies in the blank data!
But how can I alter the query criteria code to be able to overcome this problem?
I was advised to change the code of the First Name criteria to:
Like IIf(Len([Forms]![Frm_Lettersearch]![FirstName])=0), "*", _
"*" & [Forms]![Frm_Lettersearch]![FirstName] & "*")
Unfortunately however, this does not seem to solve the problem.
Bit of a complex question, but I really could do with some help here. I haven't a clue how to fix it myself.
In summary then:
The query will find a person (because that person has data in all the fields.... first name, surname, document date and date received), but it will not find a company (because a company does not have a first name... and therefore the field is empty). Any ideas on how to alter this IFF statement to be able to find records that have a blank field too?
I appreciate any help received.
Last edited by ijaw; 08-28-08 at 12:30.
08-28-08, 19:56 #2Registered User
- Join Date
- Apr 2004
- metro Detroit
Assuming that SearchletterID is the source of the form.
build the query SQL based on the user input
set the souce of the form to the SQL statement
requery the form
To build the SQL go back to the query you now have and open it in SQL view. Copy what you find there.
Create the new query in the form module
dim strSQL as string
strSQL = "copied text from query, delete everything after WHERE"
if NZ([Forms]![Frm_Lettersearch]![FirstName],"")="" then
strSQL = strSQL
strSQL = strSQL & "FirstName = '*' & [Forms]![Frm_Lettersearch]![FirstName] & '*'"
Create if statements for each of your criteria.
I don't have access available right now so I can't test this. You may have some trouble with the wildcards, but this should get you started.
Post back your code with any problems you run into.
08-28-08, 20:47 #3Moderator
- Join Date
- Dec 2004
- Madison, WI
You can also try something like this....
Like """ & Forms!Frm_Lettersearch!FirstName & """"
(I have had better luck using 3 and 4 " versus the '*' especially if a ' is in the name.)Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)
09-24-09, 12:08 #4Registered User
- Join Date
- Sep 2009
Use blank form data in a query
Test if the control is Null instead of checking for a length of zero.
03-08-11, 11:42 #5Registered User
- Join Date
- Aug 2005
Query criteria from form text box
I'm trying to accomplish something similar to the ijaw post. I have a form (unbound) with several text and combo boxes on it. I have a command button on the form with the following code: DoCmd.OpenQuery "qry_AdvancedTicketSearch", acViewNormal, acReadOnly
The query criteria for several of the fields in the query are similar to this: Like IIf(IsNull([Forms]![frm_AdvancedTicketSearch].[cmb_SubmittedBy]),"*",[Forms]![frm_AdvancedTicketSearch].[cmb_SubmittedBy])
The query runs but returns less than expected results, i.e. not all records are returned that should be returned. And when no selections are mede on the form, the query returns no records (my intent in this case would be to return ALL records, because no selection criteria was requested).
There are 20+ fields in the table, yet I'm only using 6 for search criteria, and, I want to allow for any number of the 6 to be used, i.e. some the form fields may be blank (not selected). I did a Debug.Print for all the fields on the form and they seem to be returning the correct data (data when selected, blank when not selected). I suspect when the form sends a blank (no data) the query is looking for a field with no data in it. My intent is to return records based on the selected fields, and those with no data selected to return a record as if no criteria was requested, i.e. not look at that field for criteria, which means the record is returned regardless of it being blank or not blank. So... question is... can a form containing multiple text/combo boxes (some with no user selection) be passed to a query as field criteria. If so, am I going about this all wrong?
Thanks so much for looking at this.