Need Help with Like "*" Statement 1

[bddewell408]

Like "*" & Between [forms]![frmSearchMenu]![txtBeginAge] And [forms]![frmSearchMenu]![txtEndAge] & "*"

this is the criteria I have in my query, it does not work

I have an unbound form that includes 7 unbound fields that can be typed into to search for a list a results that will appear in a list box, including

txtLast Like "*" & [Forms]![frmSearchMenu]![txtLast] & "*"
txtFirst
txtMiddle
txtBeginAge
txtEndAge
txtSex
txtRace

unfortunately that same syntax is not working for the query with the BETWEEN statement


Any help would be greatly appreciated.

Bryan

 

[cranebill]

What exactly are you trying to do?

"Between" is usually criteria... such as:

Between [forms]![frmSearchMenu]![txtBeginAge] And [forms]![frmSearchMenu]![txtEndAge]

this will pull the dates between begin age and end age

using the "like" function in your instance will not work.

 

[cranebill]

for clarification...

in your table you probably have an age column.. or a function that creates the age, so it would be something like this:

where
your_table.age Between [forms]![frmSearchMenu]![txtBeginAge] And [forms]![frmSearchMenu]![txtEndAge]

 

[bddewell408]

I am trying to give the user as many different search options as possible, without putting limitations on the search, you can use 1 field or all 7.

In my table I have [dtmDOB] and [dtmDate] (which is the date of contact with that individual.

I am creating a query for the list box that will return all of the search criteria that match. If the user wants to search all females named SMITH, or all black males, or all males age 30-40, they will be able to

To get the age I simply created a formula in the query ([dtmDate]-[dtmDOB])/365.25 to get the age at first contact.

 

[lespaul]

plus, when the txtBeginAge and txtEndAge are filled in, the following piece of code translates to:

Like "*" & Between [forms]![frmSearchMenu]![txtBeginAge] And [forms]![frmSearchMenu]![txtEndAge] & "*"

Like Between *8 AND 15*

and I don't think that's valid SQL syntax and unless the field you are searching for contains values of '8 AND 15', you'll never get any matching records.


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

 

[bddewell408]

Actually, that is exactly what I am trying to do, get an age range, but if one is not provided, I want the query to return all of the results that match the inputted criteria from the other fields.

I just don't know the correct syntax to make a like statement work with a between statement

 

[lespaul]

So you are saying that you have a single field that contains the information:


FieldName
8 and 15
9 and 20
10 and 26

leslie

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

 

[cranebill]

When
round(( (IIf(IsNull( [dtmDate]),date(),[dtmDate])-IIf(IsNull([dtmDOB]),1,[dtmDOB]))/365)-.5) Between [forms]![frmSearchMenu]![txtBeginAge] And [forms]![frmSearchMenu]![txtEndAge]

try this

 

[cranebill]

a little cleaner as I am not familiar with all access functions:

When
DateDiff("yyyy", IIf(IsNull([date1]),1,date1), IIf(IsNull([date2]),date(),date2))
Between [forms]![frmSearchMenu]![txtBeginAge] And [forms]![frmSearchMenu]![txtEndAge]

 

[cranebill]

drat:

When
DateDiff("yyyy", IIf(IsNull([dtmDOB]),1,[dtmDOB]), IIf(IsNull([dtmDate]),date(),[dtmDate]))
Between [forms]![frmSearchMenu]![txtBeginAge] And [forms]![frmSearchMenu]![txtEndAge]

 

[bddewell408]

My field contains the person's Date of Birth [dtmDOB].

In my search query, I use [dtmDate] which is the date I first made contact with the person (minus) [dtmDOB] / 365.25

My unbound search form contains two fields [txtBeginAge] and [txtEndAge] to use as the two fields to enter any two ages in to search that one field in the query with the between function.

Hope this helps, I probably wasn't clear before. I also have fields [txtLast], [txtFirst], [txtMiddle], [txtSex], [txtRace], and I need all of these to search at one time, or just stand aside and let one field search, I have all but the age one working with the Like "*" & [forms]![frmname]![fieldname] &"*"

Thanks

 

[cranebill]

so then just remove your attempt for age in your where clause and add the following:

and
DateDiff("yyyy", IIf(IsNull([dtmDOB]),1,[dtmDOB]), IIf(IsNull([dtmDate]),date(),[dtmDate]))
Between [forms]![frmSearchMenu]![txtBeginAge] And [forms]![frmSearchMenu]![txtEndAge]

 

[randy700]

What field in your table are you searching to obtain a number between txtBeginAge and txtEndAge?

Try something like...
CurrentAge BETWEEN Forms!frmSearchForm!txtBeginAge AND Forms!frmSearchForm!txtEndAge

Randy

 

[cranebill]

He has two fields...
[dtmDOB]: date of birth
[dtmDate]:date of contact with that individual

DateDiff("yyyy",[dtmDOB]), [dtmDate]) = currentage

I just added in constraints for null values so that they do not need to be filled in

 

[PHV]

Replace this:
Like "*" & Between [forms]![frmSearchMenu]![txtBeginAge] And [forms]![frmSearchMenu]![txtEndAge] & "*"
with this:
Between Nz([Forms]![frmSearchMenu]![txtBeginAge],0) And Nz([Forms]![frmSearchMenu]![txtEndAge],999)

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

 

[bddewell408]

I am not getting anything back, here is the SQL statement. I created this query in Design View, I am not very SQL literate, but that is how most of you like it:

Background all but one field is from [tblContact-SuspectRecord], the one field [dtmDate] is from [tblAActivityRecord], the primary key to foreign key on the two tables is [intActivityID]

SELECT [tblContact-SuspectRecord].intActivityID, [tblContact-SuspectRecord].strLastName, [tblContact-SuspectRecord].strFirstName, [tblContact-SuspectRecord].strMiddleName, [tblContact-SuspectRecord].dtmDOB, tblAActivityRecord.dtmDate, ([dtmDate]-[dtmDOB])/365.25 AS AgeatContact, [tblContact-SuspectRecord].strGender, [tblContact-SuspectRecord].strRace
FROM tblAActivityRecord INNER JOIN [tblContact-SuspectRecord] ON tblAActivityRecord.intActivityId = [tblContact-SuspectRecord].intActivityID
WHERE ((([tblContact-SuspectRecord].strLastName) Like "*" & [forms]![frmSearchMenu]![txtLast] & "*") AND (([tblContact-SuspectRecord].strFirstName) Like "*" & [forms]![frmSearchMenu]![txtLast] & "*") AND (([tblContact-SuspectRecord].strMiddleName) Like "*" & [forms]![frmSearchMenu]![txtLast] & "*") AND ((([dtmDate]-[dtmDOB])/365.25) Between Nz([Forms]![frmSearchMenu]![txtBeginAge],0) And Nz([Forms]![frmSearchMenu]![txtEndAge],999)) AND (([tblContact-SuspectRecord].strGender) Like "*" & [forms]![frmSearchMenu]![txtSex] & "*") AND (([tblContact-SuspectRecord].strRace) Like "*" & [forms]![frmSearchMenu]![txtRace] & "*"))
ORDER BY [tblContact-SuspectRecord].strLastName, [tblContact-SuspectRecord].strFirstName, [tblContact-SuspectRecord].strMiddleName
WITH OWNERACCESS OPTION;

Thanks
Bryan

 

[bddewell408]

I have found a way to cheat, it will take me about an hour or more to set up, but it will be worth it. I have four different searches that can be performed on this one search form, all fields and results are INVISIBLE until you click a command button to make the search criteria text boxes appear. For each search, I will have two list boxes tied to different queries, and depending if an age range, or date range is entered will determine which query is run and which list box is made visible with the results.

EXAMPLE (using the one we have been playing with above

[txtLast] [txtFirst] [txtMiddle] [txtBeginAge] [txtEndAge] [txtSex] [txtGender]

I will simply have two queries, each going to a different listbox on the same frmSearchMenu

I will then build a macro with the Condition Statement [txtAgeBegin]="" then lstContactNoAgeRange.visible ELSE lstContactAgeRange.visible

it is a round about way to get things done, but it should work, I am going to bed now, will try to work on it around 0500 and let you know how it works

Bryan

 

[PHV]

[txtAgeBegin]=""
Are you saying the controls aren't Null but ZeroLengthString when not populated ?

For the Nz function to work put the following code just before launching the query:
If Trim([txtBeginAge] & "") = "" Then [txtBeginAge] = Null
If Trim([txtEndAge] & "") = "" Then [txtEndAge] = Null

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

 

[bddewell408]

PH,

not knowing much about code, where do I put that code you wrote just before opening the query, I built the query in design view and simply added the other like statementsin the criteria fields of design view

Thanks

 

[cranebill]

go to your sql view and replace your existing sql with this:

SELECT [tblContact-SuspectRecord].intActivityID, [tblContact-SuspectRecord].strLastName, [tblContact-SuspectRecord].strFirstName, [tblContact-SuspectRecord].strMiddleName, [tblContact-SuspectRecord].dtmDOB, tblAActivityRecord.dtmDate, ([dtmDate]-[dtmDOB])/365.25 AS AgeatContact, [tblContact-SuspectRecord].strGender, [tblContact-SuspectRecord].strRace
FROM tblAActivityRecord INNER JOIN [tblContact-SuspectRecord] ON tblAActivityRecord.intActivityId = [tblContact-SuspectRecord].intActivityID
WHERE ((([tblContact-SuspectRecord].strLastName) Like "*" & [forms]![frmSearchMenu]![txtLast] & "*") AND (([tblContact-SuspectRecord].strFirstName) Like "*" & [forms]![frmSearchMenu]![txtLast] & "*") AND (([tblContact-SuspectRecord].strMiddleName) Like "*" & [forms]![frmSearchMenu]![txtLast] & "*") AND (([tblContact-SuspectRecord].strGender) Like "*" & [forms]![frmSearchMenu]![txtSex] & "*") AND (([tblContact-SuspectRecord].strRace) Like "*" & [forms]![frmSearchMenu]![txtRace] & "*"))and
DateDiff("yyyy", IIf(IsNull([dtmDOB]),1,[dtmDOB]), IIf(IsNull([dtmDate]),date(),[dtmDate]))
Between [forms]![frmSearchMenu]![txtBeginAge] And [forms]![frmSearchMenu]![txtEndAge]
ORDER BY [tblContact-SuspectRecord].strLastName, [tblContact-SuspectRecord].strFirstName, [tblContact-SuspectRecord].strMiddleName
WITH OWNERACCESS OPTION;