Problem with Query Returning report based on current date

[bruce24444]

I have a database which assigns warranty claims to people with a main screen showing number of files assigned to each person. The number assigned shows day, week, month and year numbers so they can be evenly distributed.

The problem I'm having is getting the query to return a number of files for the current date. Week, month and year appear to work fine.

Below are the SQL's for both day and week.

Any suggestions as to what's wrong would be appreciated...


Current Date:

SELECT Staff.[Last Name], Staff.[First Name], Loss_Type.[Type of Loss], Claim_Assignment.[Assigned Date], Start_End.[Current Date]
FROM Start_End, Loss_Type INNER JOIN (Staff INNER JOIN Claim_Assignment ON Staff.[Last Name] = Claim_Assignment.[Last Name]) ON Loss_Type.[Type of Loss] = Claim_Assignment.[Type of Loss]
GROUP BY Staff.[Last Name], Staff.[First Name], Loss_Type.[Type of Loss], Claim_Assignment.[Assigned Date], Start_End.[Current Date]
HAVING (((Claim_Assignment.[Assigned Date]) Between [Start_End]![Current Date] And [Start_End]![Current Date]))
ORDER BY Staff.[Last Name];

Week:

SELECT Staff.[Last Name], Staff.[First Name], Loss_Type.[Type of Loss], Claim_Assignment.[Assigned Date]
FROM Start_End, Loss_Type INNER JOIN (Staff INNER JOIN Claim_Assignment ON Staff.[Last Name] = Claim_Assignment.[Last Name]) ON Loss_Type.[Type of Loss] = Claim_Assignment.[Type of Loss]
WHERE (((Claim_Assignment.[Assigned Date]) Between [Start_End]![Week Start Date] And [Start_End]![Week End Date]))
ORDER BY Staff.[Last Name];

The [Start_End] Table has columns for current date, week start date, week end date, month start date, month end date, year start date, year end date and are all updated from a form.

Thanks in advance for any suggestions provided

[Rabbit]

I don't know what Start_End is but if you're trying to return the current system date then you do:
[Assigned Date] = Date()

[bruce24444]

I still get nothing

SELECT Staff.[Last Name], Staff.[First Name], Loss_Type.[Type of Loss], Claim_Assignment.[Assigned Date]
FROM Loss_Type INNER JOIN (Staff INNER JOIN Claim_Assignment ON Staff.[Last Name] = Claim_Assignment.[Last Name]) ON Loss_Type.[Type of Loss] = Claim_Assignment.[Type of Loss]
GROUP BY Staff.[Last Name], Staff.[First Name], Loss_Type.[Type of Loss], Claim_Assignment.[Assigned Date]
HAVING (((Claim_Assignment.[Assigned Date])=Date()))
ORDER BY Staff.[Last Name];

I tried that in the beginning and I couldn't get results so I added a fixed date column in the Tbl: Start_End and that didn't work either yet in the Tbl:Claim_Assig nment I have assigned a file to myself and it shows todays date as Date_Assigned.

Like I said earlier the week, month and year Queries return the proper results.

[Rabbit]

Try Date([Assigned Date]) = Date()

[bruce24444]

I tried your suggestion and still nothing. I then noticed that on the week query the assigned files for the last day of the sequence where not being included. I tried:

Between Date() And Date()+1

and all of a sudden everything works.

Thanks for your suggestions.

[Rabbit]

That's because there's a time element to the date variables. Using Between Date and Date + 1 will give you everything from 12:00 AM to 12:00 AM the next day.

For the solution I provided to work, you wouldn't put it in the criteria as I suspect you may have.