Help With Calendar Control/Query

  • Thread starter Thread starter Shelly Jackson
  • Start date Start date
S

Shelly Jackson

I built a form that has not data source. Its purpose is to ask the user for
a start date and end date and then print a report. I inserted two calendar
controls. I want the user to select a Startdate from Calendar1 and an
EndDate from Calendar2.

I right clicked each Calendar, choose Properties and choose Event, On
Updated then I choose Code Builder:

I changed the event to OnClick and put in the following code:

Private Sub Calendar1_Click()
StartDate = MyCalendar.Value
End Sub

And for Calendar2:

Private Sub Calendar2_Click()
EndDate = MyCalendar.Value
End Sub

Someone said I now use StartDate and EndDate in my query for my report. How
do I do this?

TIA
S. Jackson
 
Your code needs to look like this:
Private Sub Calendar1_Click()
StartDate = Calendar1.Value
End Sub

And for Calendar2:

Private Sub Calendar2_Click()
EndDate = Calendar2.Value
End Sub

Then in the Query for your report you use Startdate and
Enddate for your dates e.g
"Select Mytable.* from Mytable
Where Mytable.date between #" & Startdate &"# and #" &
Enddate & #"

Replace Mytable with the table name and date with the name
of your date field.

Dave
 
Thank you, but when I attempt to paste:

between #" & StartDate & "#and #" & EndDate & #"

under the correct column of my query (in design view), I get this error:
The expression you entered as an invalid date value.

When I go to SQL View and cut and paste:

SELECT [ADMIN CASES].DHSNo, [ADMIN CASES].CaseName, [ADMIN CASES].Region,
[ADMIN CASES].AssignDate, [ADMIN CASES].DHSAttny, STATUS.ClosedDate
FROM [ADMIN CASES] INNER JOIN STATUS ON [ADMIN CASES].DHSNo = STATUS.DHSNo;
WHERE [ADMIN CASES].AssignDate between #" & Startdate &"# and #" &
Enddate & #"

I get an error saying : Propery not found.

Any ideas why this is happening?

TIA

S. Jackson
 
Here is exactly what I've done:

I built a form that has not data source. Its purpose is to ask the user for
a start date and end date and then print a report. I inserted two calendar
controls named Calendar1 and Calendar2. I want the user to select a
Startdate from Calendar1 and an
EndDate from Calendar2.

I clicked view Code:

I changed the event to OnClick and put in the following code:

Private Sub Calendar1_Click()
StartDate = Calendar1.Value
End Sub

And for Calendar2:

Private Sub Calendar2_Click()
EndDate = Calendar2.Value
End Sub

I also insert a button on the form that will run a macro that opens my
report.

Now I am trying to build a query for the report that collects data from the
Startdate selected by the user on Caldendar1 up to and including the EndDate
selected on Caldendar2.

The SQL View of my query looks like this:

SELECT [ADMIN CASES].DHSNo, [ADMIN CASES].CaseName, [ADMIN CASES].Region,
[ADMIN CASES].AssignDate, [ADMIN CASES].DHSAttny, STATUS.ClosedDate, [ADMIN
CASES].Program, [ADMIN CASES].Asstnt
FROM [ADMIN CASES] INNER JOIN STATUS ON [ADMIN CASES].DHSNo = STATUS.DHSNo


I'm lost. Please help! I don't know how to write the WHERE portion of the
query.

S. Jackson
 
That was my point in my previous reply since I wasn't where StartDate and
EndEnd come from.

Forget about the 2 Calendar_Click Event since you can use the values from
the Calendars directly.

The SQL String of your Query should be something like:

SELECT [ADMIN CASES].DHSNo, [ADMIN CASES].CaseName, [ADMIN CASES].Region,
[ADMIN CASES].AssignDate,
[ADMIN CASES].DHSAttny, STATUS.ClosedDate
FROM [ADMIN CASES] INNER JOIN STATUS
ON [ADMIN CASES].DHSNo = STATUS.DHSNo;
WHERE [ADMIN CASES].AssignDate
BETWEEN Forms!YourFormName!Calendar1.Value
AND Forms!YourFormName!Calendar2.Value

Substitute the name of the Form and Calendars as appropriate.
 
Well, we're getting further!

I deleted the 2 click events for the two calendar controls in my form.

I added the sql string to my query which now looks like this:

SELECT [ADMIN CASES].DHSNo, [ADMIN CASES].CaseName, [ADMIN CASES].Region,
[ADMIN CASES].AssignDate, [ADMIN CASES].DHSAttny, STATUS.ClosedDate, [ADMIN
CASES].Program, [ADMIN CASES].Asstnt
FROM [ADMIN CASES] INNER JOIN STATUS ON [ADMIN CASES].DHSNo = STATUS.DHSNo
WHERE ((([ADMIN CASES].AssignDate) Between
[Forms]![frmCaldendar]![Calendar1].[Value] And
[Forms]![frmCalendar]![Calendar2].[value]));

I added a button to my frmCaldendar that runs a macro that opens the report.
The report is generated from the query above. However, I get this popup:
Enter Parameter Value
Forms!frmCaldendar!Calendar1.Value

So, now what???
Why isn't the query reading the value of Calendar1?

S. Jackson

Van T. Dinh said:
That was my point in my previous reply since I wasn't where StartDate and
EndEnd come from.

Forget about the 2 Calendar_Click Event since you can use the values from
the Calendars directly.

The SQL String of your Query should be something like:

SELECT [ADMIN CASES].DHSNo, [ADMIN CASES].CaseName, [ADMIN CASES].Region,
[ADMIN CASES].AssignDate,
[ADMIN CASES].DHSAttny, STATUS.ClosedDate
FROM [ADMIN CASES] INNER JOIN STATUS
ON [ADMIN CASES].DHSNo = STATUS.DHSNo;
WHERE [ADMIN CASES].AssignDate
BETWEEN Forms!YourFormName!Calendar1.Value
AND Forms!YourFormName!Calendar2.Value

Substitute the name of the Form and Calendars as appropriate.

--
HTH
Van T. Dinh
MVP (Access)



Shelly Jackson said:
Here is exactly what I've done:

I built a form that has not data source. Its purpose is to ask the user for
a start date and end date and then print a report. I inserted two calendar
controls named Calendar1 and Calendar2. I want the user to select a
Startdate from Calendar1 and an
EndDate from Calendar2.

I clicked view Code:

I changed the event to OnClick and put in the following code:

Private Sub Calendar1_Click()
StartDate = Calendar1.Value
End Sub

And for Calendar2:

Private Sub Calendar2_Click()
EndDate = Calendar2.Value
End Sub

I also insert a button on the form that will run a macro that opens my
report.

Now I am trying to build a query for the report that collects data from the
Startdate selected by the user on Caldendar1 up to and including the EndDate
selected on Caldendar2.

The SQL View of my query looks like this:

SELECT [ADMIN CASES].DHSNo, [ADMIN CASES].CaseName, [ADMIN CASES].Region,
[ADMIN CASES].AssignDate, [ADMIN CASES].DHSAttny, STATUS.ClosedDate, [ADMIN
CASES].Program, [ADMIN CASES].Asstnt
FROM [ADMIN CASES] INNER JOIN STATUS ON [ADMIN CASES].DHSNo = STATUS.DHSNo


I'm lost. Please help! I don't know how to write the WHERE portion of the
query.

S. Jackson
Click to expand...
Click to expand...
 
Never mind!!! I see the error - typo!

Argh!!


Shelly Jackson said:
Well, we're getting further!

I deleted the 2 click events for the two calendar controls in my form.

I added the sql string to my query which now looks like this:

SELECT [ADMIN CASES].DHSNo, [ADMIN CASES].CaseName, [ADMIN CASES].Region,
[ADMIN CASES].AssignDate, [ADMIN CASES].DHSAttny, STATUS.ClosedDate, [ADMIN
CASES].Program, [ADMIN CASES].Asstnt
FROM [ADMIN CASES] INNER JOIN STATUS ON [ADMIN CASES].DHSNo = STATUS.DHSNo
WHERE ((([ADMIN CASES].AssignDate) Between
[Forms]![frmCaldendar]![Calendar1].[Value] And
[Forms]![frmCalendar]![Calendar2].[value]));

I added a button to my frmCaldendar that runs a macro that opens the report.
The report is generated from the query above. However, I get this popup:
Enter Parameter Value
Forms!frmCaldendar!Calendar1.Value

So, now what???
Why isn't the query reading the value of Calendar1?

S. Jackson

That was my point in my previous reply since I wasn't where StartDate and
EndEnd come from.

Forget about the 2 Calendar_Click Event since you can use the values from
the Calendars directly.

The SQL String of your Query should be something like:

SELECT [ADMIN CASES].DHSNo, [ADMIN CASES].CaseName, [ADMIN CASES].Region,
[ADMIN CASES].AssignDate,
[ADMIN CASES].DHSAttny, STATUS.ClosedDate
FROM [ADMIN CASES] INNER JOIN STATUS
ON [ADMIN CASES].DHSNo = STATUS.DHSNo;
WHERE [ADMIN CASES].AssignDate
BETWEEN Forms!YourFormName!Calendar1.Value
AND Forms!YourFormName!Calendar2.Value

Substitute the name of the Form and Calendars as appropriate.

--
HTH
Van T. Dinh
MVP (Access)



Shelly Jackson said:
Here is exactly what I've done:

I built a form that has not data source. Its purpose is to ask the
Click to expand...
user
for
a start date and end date and then print a report. I inserted two calendar
controls named Calendar1 and Calendar2. I want the user to select a
Startdate from Calendar1 and an
EndDate from Calendar2.

I clicked view Code:

I changed the event to OnClick and put in the following code:

Private Sub Calendar1_Click()
StartDate = Calendar1.Value
End Sub

And for Calendar2:

Private Sub Calendar2_Click()
EndDate = Calendar2.Value
End Sub

I also insert a button on the form that will run a macro that opens my
report.

Now I am trying to build a query for the report that collects data
Click to expand...
from
the
Startdate selected by the user on Caldendar1 up to and including the EndDate
selected on Caldendar2.

The SQL View of my query looks like this:

SELECT [ADMIN CASES].DHSNo, [ADMIN CASES].CaseName, [ADMIN CASES].Region,
[ADMIN CASES].AssignDate, [ADMIN CASES].DHSAttny, STATUS.ClosedDate, [ADMIN
CASES].Program, [ADMIN CASES].Asstnt
FROM [ADMIN CASES] INNER JOIN STATUS ON [ADMIN CASES].DHSNo = STATUS.DHSNo


I'm lost. Please help! I don't know how to write the WHERE portion
Click to expand...
of
the
query.

S. Jackson
Click to expand...
Click to expand...
Click to expand...
 
I was about to point out the typo then I saw your second post.

Is everything working now?
 
Yes! Thank you so much for all of your help! These newsgroups are such a
godsend for me. I have been tasked with building this database at work.
Its ridiculous, really, that they think I can do this with no training. All
knowledge is self-taught from the past 3 years of building and managing a
case management system for our office. Now they want my system to go
state-wide and cover 11 different offices! Insane! (I have many more
questions to come!)

Sorry, enough venting! But, AGAIN, THANK YOU so much for being here. Know
that you are helping many others like me.

S. Jackson
 
My Access knowledge is entirely self-taught with help from various books (I
am a big spender on books), reading these newsgroups and on the job trial &
error.

In fact, take the opportunity and you can learn a lot more.
 
Back
Top