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

 

[bddewell408]

I replaced it with that code and it doesn't return any values, I found a mistake I made in the code, but when I changed it I still don't get anything back, I also changed

(([tblContact-SuspectRecord].strFirstName) Like "*" & [forms]![frmSearchMenu]![txtLast] & "*")

to (([tblContact-SuspectRecord].strFirstName) Like "" & [forms]![frmSearchMenu]![txtFirst] & "*")


same for Middle Name

it wont return any values after the change, also there is a little problem I now see, I do not have everyone's date of birth, so I cannot possibly get an age at first contact from my older records, I truly need the age range to be an optional field, and not just cheat by putting in 1-999, I guess if we could include an Is Null in there somehow





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]![txtFirst] & "*") AND (([tblContact-SuspectRecord].strMiddleName) Like "" & [forms]![frmSearchMenu]![txtMiddle] & "*") 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;

 

[PHV]

Between Nz([Forms]![frmSearchMenu]![txtBeginAge],0) And Nz([Forms]![frmSearchMenu]![txtEndAge],999) OR dtmDOB 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

 

[bddewell408]

I would like to thank everyone who responded to this post the below ended up working after all.

Between Nz([Forms]![frmSearchMenu]![txtBeginAge],0) And Nz([Forms]![frmSearchMenu]![txtEndAge],999)

I ended up having to change the criteria little to get it to finally work, I did have :

Like "*"&[Forms]![frmName]![field]&"*" it didn't work

this did Like [Forms]![frmName]![field &"*"

Thanks again everyone

Bryan