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
5 3219 Rabbit 12,516
Recognized Expert Moderator MVP
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()
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 12,516
Recognized Expert Moderator MVP
Try Date([Assigned Date]) = Date()
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 12,516
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Stephen Miller |
last post by:
Firstly, sorry for the long post, but I've included a fair bit of
sample data.
Im doing a comparision of 10yr Bond prices and CPI adjustments, with
an 18 week moving average of the CPI. I'm using a nested sub-query to
calculate the moving average, but I'm having difficulty selecting
exactly 18 data points (ie When I include the 'HAVING...
|
by: Steve M |
last post by:
I need help getting a report to do what I want.
I have an archive table that contains the date, day of week, different
types of work and the arrival time for each type of work. I also have
2 columns in another table for the time each type of work was due (as
opposed to when it actually arrived). One column is for Monday through
Thursday,...
|
by: StBond |
last post by:
Hi everyone,
I am new to Access and Visual Basic so things my be getting across a
bit cloudy. I only started using VB for one week.
I am having a little problem with the database that I am working on. I
am working with MS Access 2002. And I am having a problem with one of
my charts.
I will explain how everything is laid out then go...
|
by: DeanL |
last post by:
Hi all,
I have a problem that I'm a little stumped by and need some help if
possible. I need to generate a report in Access 97 from 2 tables (easy
so far) but it requires a calculated date based on several factors
including whether a date is present in one of three fields.
Basically, I have 3 date fields (Award Date, RTL Date and Target...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source=" & msDbFilename
moConn.Properties("Persist Security Info") = False
moConn.ConnectionString = msConnString
moConn.CursorLocation = adUseClient...
| |
by: ShadesOfGrey |
last post by:
Hi, new to the group, not to Access...but it's been awhile since I've
done much with it. I am creating a database that tracks student and
instructor attendance. I have two tables, one for student information
and one for the attendance records. I want to create a report that
takes the attendance record with fields Record Number, Student ID,...
|
by: DeanL |
last post by:
Hi Guys,
I've been handed a database (Access 97) that is a couple of years old
that no one has maintained and is now experiencing a problem. The
database is used to run several reports based on the data contained in
the db and all data is stored with a unique project id. The original
designer set the db up to have a single subform that...
|
by: pokerboy801 |
last post by:
OK, I will try to explain this as clearly and as concise as possible.
I am using Access, which has three MS Excel Linked tables, to store
call center metrics for reps. My Excel workbook has three separate
worksheets, all linked to Access. I have a query that performs some
minor calculations, not to hard, and will be ued to drive a report....
|
by: mskapek |
last post by:
I need some advise on how to best create an Access 2002 report from multiple "total" queries, each which result in 3- 5 values that I need displayed on my report. Most of the queries do simple counts based on 3 different tables. For instance, I need to count the number of new clients enrolled in a program for a given month or year-to-date...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |