Searching "Between Dates"

[unclefink]

I am having some difficulty creating a query I was hoping someone might be able to help me with.

I have two separate "Date/Time" fields in a single table, the first titled "Start Date/Time" and the second "End Date/Time". What i am trying to do is create a query to search for any dates between the start and end dates.

Example: a job starts on 10/5/12 at 1500 and ends on 10/10/12 at 0600. I want to create a query that will give me results if the search date i am searching falls between the start and end date of any existing data. Also, I only want to search the Date WITHOUT time criteria.

Hopefully this makes since.

Thanks in advance for any help.

 

[AccessMSSQL]

Your query criteria box needs to have BETWEEN #01/01/2012# AND #01/30/2012#.

http://www.accessmssql.com/

 

[unclefink]

Please forgive me if I make no sence as I am learning this program small bits at a time.

In your response, you indicated to place BETWEEN #01/01/2012# AND #01/30/2012# in the search criteria.

* Where do I put this criteria at when i have two different date fields.
* Once i figure out where to put this criteria, i then have the problem of having to change the criteria to coordinate with dates outside of 1/1/12 and 1/30/12. i'd like to have a criteria that will work "with any date entered"

 

[joeKra]

Add 2 parameters >=[Enter From Date ] under the start Date\Time and another param =<[Enter End Date ] also list the parameters in the parameter list in query,
hope it help

 

[cookiegary01]

Hi,
In your query design view, go to the criteria row below "Start Date/Time" and also below "End Date/Time".
In both places type the following: Between [Enter date1] And [Enter date2].
When you run the query, it will prompt you to enter dates. You need to enter both dates twice, because you do not have a form open with these 2 dates that you could refer to.

 

[unclefink]

It appears as though I am not explaining my intention all too great.

Once the query is built, i want to be able to only enter one date to see if that specific single date falls anywhere between the existing 2 job dates.

 

[AccessMSSQL]

Oh..I see. Okay. You can create a form to run the query.
Create a form with a textbox on it so you can enter a date. Call it txtDate or something. Put a button on the form that uses docmd.openquery "your query name", etc.

In your query you need to add an expression field that points to the forms textbox. See below

SELECT Date1, date2, [Forms]![frmDate]![txtDate] AS Expr1
FROM Table1
WHERE ((([Forms]![frmDate]![txtDate]) Between [date1] And [date2]))

http://www.accessmssql.com/

 

[spikepl]

Actually, since your dates have the time component, it should be

SELECT Date1, date2, [Forms]![frmDate]![txtDate] AS Expr1 
FROM Table1 
WHERE ((([Forms]![frmDate]![txtDate]) >= [date1] And [Forms]![frmDate]![txtDate]  <[date2]+1))

or perhaps

 SELECT DateValue(Date1) AS StartDate , DateValue(date2) AS EndDate , [Forms]![frmDate]![txtDate] AS Expr1 
FROM Table1 
WHERE ((([Forms]![frmDate]![txtDate]) Between DateValue(Date1) AND DateValue(Date2))

or some mix thereof - depending on what is to be output.

 

[unclefink]

So i've got a portion of this process down, however; being a newbie to creating a database, i'm still learning and for the most part have a lot of issues with understanding coding.

As instructed, i've got the form created with a text box and a button as indicated in the directions.

The problem i'm having is where do i add the code to the query. When I go to the code for the query, this already exists, presumably the coding for the existing query.

SELECT [Main Data].[Request Date], [Main Data].[Order Number], [Main Data].[Physical Location], [Main Data].[Start Date/Time], [Main Data].[End Date/Time], [Main Data].[Position 1], [Main Data].[Position 2], [Main Data].[Position 3], [Main Data].[Position 4]
FROM [Main Data];

Where do i put the new/added code, and what portion of the "New code" do i need to change to coinside with my database.

Thanks in advance for the help.

 

[mahenkj2]

As advised by AccessMSSQL, please see if attached dB is some help to you. May not be complete but just for reference.

best regards.

 

[mahenkj2]

If Access 2003 then see attached.

 

[Galaxiom]

I would strongly advise you to desist from using special characters and spaces in your names. They make the code harder to read and necessitate the square brackets around everything.