Query to use Date Range Prompt 2

[jsaliers]

I have created a query that does what I want it to do, except for one thing. I want it to prompt the user for a start date, and an end date, and then only accept those records with a date in the field WeekEnding between those values. How do I do this?

Thanks in advance!

Jon Saliers

 

[longhair]

Jsaliers,

In the criteria of the query put
"between [enter start date] and [enter end date]"
without the quotes.

HTH

Regards,
longhair

 

[Rick39]

In the criteria for WeekEnding, put:

Between [Enter Start Date] And [Enter End Date]

 

[jsaliers]

OK, this works, but here is another question. Here is my resultant SQL, and it works fine:

SELECT Hours.ProgramNumber AS ProgramNumber, Hours.ProgramName AS ProgramName, Hours.Project AS ProjectNumber, Hours.ProjectName AS ProjectName, Ref_Employee.Job AS Job, Sum(Hours.Hours) AS SumOfHours
FROM Ref_Employee INNER JOIN Hours ON Ref_Employee.Name = Hours.Engineer
GROUP BY Hours.ProgramNumber, Hours.ProgramName, Hours.Project, Hours.ProjectName, Ref_Employee.Job, Hours.WeekEnding
HAVING (((Hours.WeekEnding) Between [Enter Start Date:] And [Enter End Date:]));

I want this to show up in a form, and I already had the form working, but when I paste in the SQL into the code, it no longer works:

Report.RecordSource = "SELECT Hours.ProgramNumber As ProgramNumber, Hours.ProgramName As ProgramName, Hours.Project As ProjectNumber, Hours.ProjectName As ProjectName, Ref_Employee.Job As Job, Sum(Hours.Hours) AS SumOfHours FROM Ref_Employee INNER JOIN Hours ON Ref_Employee.Name = Hours.Engineer GROUP BY Hours.ProgramNumber, Hours.ProgramName, Hours.Project, Hours.ProjectName, Ref_Employee.Job HAVING (((Hours.WeekEnding) Between [Enter Start Date:] And [Enter End Date:]));"

The error I get is: You tried to run a query that does not include the specified expression 'Hours.Weekending Between [Enter Start Date:] And [Enter End Date:]' as part of the aggregate function.

How can I get this to work?

Thanks in advance!

Jon

 

[jsaliers]

I take it back, the query is not working properly. Thanks again.

Jon

 

[longhair]

jsaliers,

Rather than 'HAVING....' I would use
WHERE(((Hours.WeekEnding) Between [Enter Start Date:] And [Enter End Date:]))
So that the the SQL follows the below pattern.
SELECT...
FROM...
WHERE...
GROUP BY...

Regards,

longhair

 

[jsaliers]

Thank you for the suggestion, but it would not have made a difference. In order to work properly, a query first had to be run to catch only the correct date range. Then the report can be opened using the statement I originally had, drawing records from the new table created by the date query. Otherwise, there would have been multiple records for the same discipline, something I did not originally explain, but which I figured out. That is why I stated that the query did not work properly, I meant the original query I had been running. I now have it working properly though. On the form open event, I run a make table query to sort by date, then use the recordsource from this newly created table. Now if only I could automatically delete the table on form close...? Is there a way to do this?

Thanks in advance!

Jon Saliers

 

[longhair]

jsaliers,

If you are creating the table dynamically ie each time the query is run you create a whole new temp table (all the fields, field types, etc). The best way to do it would probably be in VBA. Build an expression linked to form close and try the following code:
DoCmd.DeleteObject acTable, "your table name here"
If you need to keep the table schema post back & I'll try to work out a way to do it.

Regards,

longhair

 

[jsaliers]

I used the code that you gave me and put it in the On_Close Event:

Private Sub Report_Close()

Dim stDocName As String

stDocName = "DateTable"
DoCmd.DeleteObject acTable, stDocName

End Sub

However, I get the following error message when I close the report:

Run-time error '3211':
The database engine could not lock table 'DateTable' because it is already in use by another person or process.

Any ideas?? Thanks in advance!

Jon Saliers

 

[EnS]

This is because the table is still tied to the report on the Close Event. Therefore, since the report is still technically opened, you will not be able to delete the table until the report is fully closed. If you are using Access XP, you can place the delete statment in the On_UnLoad event and this should delete your table object. If I'm correct, this is because the report closes before it is unloaded from memory. ERM

 

[EnS]

Just another thought, since you will not be able to delete an object that has an active link to another object, such as your table to your form, try resetting the forms RecordSource to Null String in your Close Event. For example:

Private Sub Form1_Close()

Me.RecordSource = ""

DoCmd.DeleteObject "ObjectName"

End Sub

This will release the active link between the objects allowing you to delete your table object.

Just an after thought. ERM

 

[EnS]

Oops, code should read:

Private Sub Report1_Close()

Me.RecordSource = ""

DoCmd.DeleteObject acTable "TableName"

End Sub

This should also work for reports too. ERM