Creating a query to display a weeks records

[shabbaranks]

Hi,



Im trying to create a query which prompts the user to input a date (which will be the begining of a week) and then add the additional 4 days which makes up the working week. This will eventually output to a report but for now I cant seem to get the query working.

I have tried the between funtion but that seems to miss out the end date unless you specifiy an additional date at the end which captures all the dates. Any ideas please?

Thanks

[jzwp11]

In the query criteria you would have something like this


WHERE yourdatefield BETWEEN forms!formname!datecontrolname AND dateadd("d",4,forms!formname!datecontrolname)

Does your date field include the time component? If so, you may want to use the datevalue() function : datevalue(yourdatefield)

[shabbaranks]

I notice your example above relates to a form but its a column within a table I am trying to query - does this make a difference?

Thanks

[jzwp11]

I typically use a form for the user to input the necessary information. You could use a parameter query also just adjust the WHERE clause with a parameter instead of the form reference

WHERE yourdatefield BETWEEN [Enter the beginning date] AND dateadd("d",4,[Enter the beginning date])

If the query asks for the date multiple times then I would go with the form approach to save your users the hassle.

[shabbaranks]

How do those parameter queries work? I tried to use it but couldnt get it going. Im sure this is really simple but I am being a little thick...

[jzwp11]

A parameter field can be used in the SELECT clause or the WHERE clause of a query. It is typically enclosed within square bracket [] and the name cannot be the same as an existing field in the query's record source (as listed in the FROM clause). When you run the query, SQL will look to have any parameters satisfied prior to running the query. So when you run the query you will be presented with a prompt for each parameter field. In some cases, the query may prompt you for the same parameter multiple times; if this occurs, it is best to handle it by using an unbound form and referencing the form control name

Here is an example parameter query that will prompt the user for two dates

SELECT tblEmployees.txtFName, tblEmployees.txtLName, tblEmployees.dteHire
FROM tblEmployees
WHERE (((tblEmployees.dteHire) Between [date1] And [date2]));

Let's say that you want to find all employees hired in 2011. You would input 1/1/2011 for date1 and 12/31/2011 for date2 when prompted


You can also reference the name of controls on a form. The controls act as parameters

SELECT tblEmployees.txtFName, tblEmployees.txtLName, tblEmployees.dteHire
FROM tblEmployees
WHERE (((tblEmployees.dteHire) Between forms!formname!date1 And forms!formname!date2));


If the form is not open at the time you run the query, Access will treat the form control references as direct parameters and prompt you for them.

[shabbaranks]

But with the between statement you wouldnt have 12/31/2011 as this date doesnt fall between. Or atleast thats whats happening with my example. I have dates ranging from 02/01/2012 to 08/01/2012 I specify within the query between 02/01/2012 and 06/01/2012 and I get 02/11 - 05/11 but no 06...

[jzwp11]

The BETWEEN should return records that are equal to the beginning and ending dates in addition to the dates between the two end members unless you have the time component included in the date and that is why I suggested stripping off the time component by using the datevalue() function.

BTW, what date format are you using mm/dd/yyyy or dd/mm/yyyy?

[shabbaranks]

Hi,
I dont have the time component with it just the date, so Im not too sure why it doesnt work?

[jzwp11]

You could also try

WHERE datefield >=[date1] and datefield<=[date2]

This assumes that date2>=date1.

[shabbaranks]

I think the column might be using the time and date so I could try the date value. How do you input it?

Ive tried

Between "datevalue[Week Commencing]" And "datevalue[Enter End Date]"

but its not working - thanks again

[shabbaranks]

Ive tried >=[Week Commencing] And <=[Week Ending] which as I understand it should be greater than or equal to and less than or equal to. But it keeps missing off the less than or equal to. In the table there are dates 02/01/2012 - 06/01/2012 but its missing off the 06. Likewise for any higher dates if I try a different query??

[jzwp11]

The datevalue() function has to enclose your field name not the parameter names. Could you post your database with any sensitive data removed? Perhaps we can get to a quicker solution that way & we can see how the field is set up.

[shabbaranks]

Yeah sure thanks - its attached

[jzwp11]

In an earlier post you said the following:

In the table there are dates 02/01/2012 - 06/01/2012 but its missing off the 06.

However, I did not see any table with date values as you described, so I went ahead and modified the dates in your timesheettable in the attached database.

I created 4 queries to illustrate the use of parameters, the BETWEEN...AND, the <=,>= and the datevalue() function.

The query named qryParameterQueryExampleBETWEEN illustrates the use of the BETWEEN...AND as well as a parameter. I also use the dateadd() function to calculate the ending date (4 days after the date that is entered). For this query I used the task date field which does not have the time component. If you run the query and input 1/4/2012, you should see all records that have dates between 1/4/2012 and 1/8/2012 inclusive.

The query named qryParameterQueryExampleNotUsingBetween should return the same results when 1/4/2012 is entered but in this query I used the <= and >= approach. This query also uses the task date field.

For the other 2 queries I used the Time_of_Submission field since it has the time component.

If you run the query qryParameterNotUsingDatevalueFunction using the 1/4/2012 date again, you will have 9 records returned. The record with ID=25 is not returned by the query even though the Time_of_Submission date falls within the 1/4/2012 to 1/8/2012 (1/8/2012 8:36:17 AM).

If you run the qryParameterUsingDatevalueFunction, you will see that the query returns 10 records and the record with ID=25 is now present.