By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,365 Members | 3,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,365 IT Pros & Developers. It's quick & easy.

Problem with Query Returning report based on current date

P: 20
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Staff.[Last Name], Staff.[First Name], Loss_Type.[Type of Loss], Claim_Assignment.[Assigned Date], Start_End.[Current Date]
  2. 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]
  3. GROUP BY Staff.[Last Name], Staff.[First Name], Loss_Type.[Type of Loss], Claim_Assignment.[Assigned Date], Start_End.[Current Date]
  4. HAVING (((Claim_Assignment.[Assigned Date]) Between [Start_End]![Current Date] And [Start_End]![Current Date]))
  5. ORDER BY Staff.[Last Name];
  6.  
Week:
Expand|Select|Wrap|Line Numbers
  1. SELECT Staff.[Last Name], Staff.[First Name], Loss_Type.[Type of Loss], Claim_Assignment.[Assigned Date]
  2. 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]
  3. WHERE (((Claim_Assignment.[Assigned Date]) Between [Start_End]![Week Start Date] And [Start_End]![Week End Date]))
  4. ORDER BY Staff.[Last Name];
  5.  

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
Aug 1 '07 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,359
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()
Aug 1 '07 #2

P: 20
I still get nothing

Expand|Select|Wrap|Line Numbers
  1. SELECT Staff.[Last Name], Staff.[First Name], Loss_Type.[Type of Loss], Claim_Assignment.[Assigned Date]
  2. 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]
  3. GROUP BY Staff.[Last Name], Staff.[First Name], Loss_Type.[Type of Loss], Claim_Assignment.[Assigned Date]
  4. HAVING (((Claim_Assignment.[Assigned Date])=Date()))
  5. ORDER BY Staff.[Last Name];
  6.  
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_Assignment 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.
Aug 2 '07 #3

Rabbit
Expert Mod 10K+
P: 12,359
Try Date([Assigned Date]) = Date()
Aug 2 '07 #4

P: 20
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.
Aug 8 '07 #5

Rabbit
Expert Mod 10K+
P: 12,359
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.
Aug 8 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.