Search with two unbound txt boxes and a command button.

[Scooterbug]

Hmmm...I copied what you had posted into a new query and ran it without incident. I've attached your db with the modifications. It should work for you.

 

[hardhitter06]

I thought life was easy but I guess not. Now my boss wants me to modify this search to return all ACTIVE records between my date range. Right now we set it up to look at the "Contract Start" date but I also need to figure out a way to search for all records that are active within this searched range. Simply looking at the End date as well will not work...something more complex needs to be set up and I'm not quite sure how I would do this?

 

[hardhitter06]

For example:

Records with dates entered:

Start:----End:
1. 7/1/09---7/1/10
2. 8/1/09---8/1/10

Right now when we search between 8/1/09 and 8/20/09...the second record is returned because it looks at the start date.

What I need help with is when this same search is done, both records should be returned since the Date range search is within both records ACTIVE time.

 

[hardhitter06]

Can anyone help me with this query search?

 

[Kryst51]

How about this? I Did not format.

 

[hardhitter06]

This isn't working like it should. If a user searches by Start and End date, if there is an active record who's dates fall within that search (even if it isn't the actual Contract Start and End date)...the record should be returned....

For example:

I searched Start Date: 7/1/2010
End Date: 7/1/2011

All three records should be returned because during this date range search, all 3 records are active.

 

[Scooterbug]

Use the following for the SQL statement for qryMain:

SELECT tblMain.ConsultantName, tblMain.ConsultantStreetAddress, tblMain.City, tblMain.State, tblMain.Zip, tblMain.[Contract No], tblMain.ContractName, tblMain.Email, tblMain.[Contract Start], tblMain.[Contract End], tblMain.DescriptionofServices, tblMain.[Account No], tblMain.[# of Employees], tblMain.[# of Hours Worked], tblMain.[Amount Payable Under Contract]
FROM tblMain
WHERE (((tblMain.[Contract Start])<=[forms]![frmdash]![text4]) AND ((tblMain.[Contract End])>=[forms]![frmdash]![text2]));

That should do the trick for ya

 

[hardhitter06]

I just ran that search with the same Date search and no records were returned??

 

[Scooterbug]

Can you post the db you are using? I was using the one you posted a few days ago.

EDIT: I dl the one Kryst uploaded. Take out the filter criteria in the form properties

 

[hardhitter06]

I think this needs an IF statement of some sort..

so Start Date (Text 2) of the Search on the MainDashboard: 7/1/10
End Date (Text 4) of the Search on the MainDashboard: 7/1/11

Record 1: 7/1/09-7/1/20
Record 2: 7/3/09-7/3/15
Record 3: 7/5/09-7/5/10

IF (Text 2) >= Contract Start And (Text 2) <=Contract End = Return Record

IF (Text 2) >= Contract Start And (Text 2) >=Contract End = Don't Return Record

IF (Text 4) >= Contract Start And (Text 4) <=Contract End = Return Record

IF (Text 4) >= Contract Start And (Text 4) >=Contract End = Don't Return Record

If (Text 2) <= Contract Start And [(Text 4) >= Contract Start And (Text 4) <= Contract End)] = Return Record

If (Text 2) <= Contract Start And [(Text 4) >= Contract Start And (Text 4) >= Contract End)] = Don't Return Record

If (Text 4) >= Contract End And [(Text 2) >= Contract Start And (Text 2) <= Contract End)] = Return Record

If (Text 4) >= Contract End And [(Text 2) >= Contract Start And (Text 2) >= Contract End)] = Don't Return Record

For the last two pieces, I changed Text 2 to 7/1/08 and Text 4 to 7/1/21


I think that should get the job done, Scooter or whoever, you think you could put that in pretty SQL for me haha..I'm also not sure if you need the Don't Return Record lines or if there are Else statements or what...?

 

[hardhitter06]

Here is the updated DB. TY

 

[Scooterbug]

The query I gave you works fine. Go into the code for your button and rem out the line for the string criteria. That is overriding the WHERE clause in the query

EDIT: Or change it to:

stLinkCriteria = "[Contract Start] <= #" & Forms!frmDash.Text4 & "# And [Contract End] >= #" & Forms!frmDash!Text2 & "#"

Personally, I would just Rem it out...obviously it's easier to remember if it's in the query and not the code

 

[hardhitter06]

Thanks SB!

 

[Scooterbug]

Anytime...glad I could help