Control Source Problem

[kkleung89]

Okay, so I have a form "formWorkloads" that has a control source of sql
statement

SELECT tbWorkloads.[Maximum Workload], (select count(*) from
tbAppointments where tbAppointments.[Appointment
Date]=forms!formWorkloads!txtDate) AS Expr1
FROM tbWorkloads, tbAppointments
WHERE (((tbWorkloads.[Workload
Date])=[forms]![formWorkloads]![txtDate]));

I'm pretty sure that that's all "right", as in no typos. txtDate has a
default value of now(), so when it loads, I would hope that it would
just load the information from the first day. Maximum Workload and
Expr1 go into other text boxes on the form. When I try and view the
form, however, it just gives me a blank screen. Can you tell me why?
Thanks in advance.

 

[fredg]

Okay, so I have a form "formWorkloads" that has a control source of sql
statement

SELECT tbWorkloads.[Maximum Workload], (select count(*) from
tbAppointments where tbAppointments.[Appointment
Date]=forms!formWorkloads!txtDate) AS Expr1
FROM tbWorkloads, tbAppointments
WHERE (((tbWorkloads.[Workload
Date])=[forms]![formWorkloads]![txtDate]));

I'm pretty sure that that's all "right", as in no typos. txtDate has a
default value of now(), so when it loads, I would hope that it would
just load the information from the first day. Maximum Workload and
Expr1 go into other text boxes on the form. When I try and view the
form, however, it just gives me a blank screen. Can you tell me why?
Thanks in advance.

Now() includes a time value as well as a date value, so if you open
the form on 7/25/2006 at 4:15 PM, the SQL is looking for records that
equal 7/25/2006 4:15 PM. Unless the data has had a time value stored
with the date, you will never get records returned. And each time you
run the SQL that time value will change.
Try =Date() as default value. Date() does not carry a time value
(other than Midnight). Also make sure the [txtDate] control's Format
is set to a valid date format so that it is recognizes 7/25/2006 as a
date, rather than a string "7/25/2006"

 

[fredg]

Okay, so I have a form "formWorkloads" that has a control source of sql
statement

SELECT tbWorkloads.[Maximum Workload], (select count(*) from
tbAppointments where tbAppointments.[Appointment
Date]=forms!formWorkloads!txtDate) AS Expr1
FROM tbWorkloads, tbAppointments
WHERE (((tbWorkloads.[Workload
Date])=[forms]![formWorkloads]![txtDate]));

I'm pretty sure that that's all "right", as in no typos. txtDate has a
default value of now(), so when it loads, I would hope that it would
just load the information from the first day. Maximum Workload and
Expr1 go into other text boxes on the form. When I try and view the
form, however, it just gives me a blank screen. Can you tell me why?
Thanks in advance.

Now() includes a time value as well as a date value, so if you open
the form on 7/25/2006 at 4:15 PM, the SQL is looking for records that
equal 7/25/2006 4:15 PM. Unless the data has had a time value stored
with the date, you will never get records returned. And each time you
run the SQL that time value will change.
Try =Date() as default value. Date() does not carry a time value
(other than Midnight). Also make sure the [txtDate] control's Format
is set to a valid date format so that it is recognizes 7/25/2006 as a
date, rather than a string "7/25/2006"

i just noticed, as I sent my previous reply, that your first sentence
"Okay, so I have a form "formWorkloads" that has a control source of
sql statement" refers to the form's Control source. That is incorrect.
The SQL statement is the form's Record Source.

 

[kkleung89]

Changing it to date didn't work (though still a good tip), and I meant
record source. Other ideas?

Okay, so I have a form "formWorkloads" that has a control source of sql
statement

SELECT tbWorkloads.[Maximum Workload], (select count(*) from
tbAppointments where tbAppointments.[Appointment
Date]=forms!formWorkloads!txtDate) AS Expr1
FROM tbWorkloads, tbAppointments
WHERE (((tbWorkloads.[Workload
Date])=[forms]![formWorkloads]![txtDate]));

I'm pretty sure that that's all "right", as in no typos. txtDate has a
default value of now(), so when it loads, I would hope that it would
just load the information from the first day. Maximum Workload and
Expr1 go into other text boxes on the form. When I try and view the
form, however, it just gives me a blank screen. Can you tell me why?
Thanks in advance.

Now() includes a time value as well as a date value, so if you open
the form on 7/25/2006 at 4:15 PM, the SQL is looking for records that
equal 7/25/2006 4:15 PM. Unless the data has had a time value stored
with the date, you will never get records returned. And each time you
run the SQL that time value will change.
Try =Date() as default value. Date() does not carry a time value
(other than Midnight). Also make sure the [txtDate] control's Format
is set to a valid date format so that it is recognizes 7/25/2006 as a
date, rather than a string "7/25/2006"

i just noticed, as I sent my previous reply, that your first sentence
"Okay, so I have a form "formWorkloads" that has a control source of
sql statement" refers to the form's Control source. That is incorrect.
The SQL statement is the form's Record Source.

 

[Duane Hookom]

Trouble-shoot...
Open the datasheet view of your Record Source or create a query with this
SQL:

SELECT tbWorkloads.[Maximum Workload], (select count(*) from
tbAppointments where tbAppointments.[Appointment
Date]=forms!formWorkloads!txtDate) AS Expr1
FROM tbWorkloads, tbAppointments
WHERE (((tbWorkloads.[Workload
Date])=[forms]![formWorkloads]![txtDate]));

I'm not sure why you are selecting form tbAppointments since this table is
in the subquery.

This query will not be updateable. What are some of your form data
properties?
--
Duane Hookom
MS Access MVP

Changing it to date didn't work (though still a good tip), and I meant
record source. Other ideas?

On 25 Jul 2006 15:40:48 -0700, [email protected] wrote:

Okay, so I have a form "formWorkloads" that has a control source of
sql
statement

SELECT tbWorkloads.[Maximum Workload], (select count(*) from
tbAppointments where tbAppointments.[Appointment
Date]=forms!formWorkloads!txtDate) AS Expr1
FROM tbWorkloads, tbAppointments
WHERE (((tbWorkloads.[Workload
Date])=[forms]![formWorkloads]![txtDate]));

I'm pretty sure that that's all "right", as in no typos. txtDate has
a
default value of now(), so when it loads, I would hope that it would
just load the information from the first day. Maximum Workload and
Expr1 go into other text boxes on the form. When I try and view the
form, however, it just gives me a blank screen. Can you tell me why?
Thanks in advance.

Now() includes a time value as well as a date value, so if you open
the form on 7/25/2006 at 4:15 PM, the SQL is looking for records that
equal 7/25/2006 4:15 PM. Unless the data has had a time value stored
with the date, you will never get records returned. And each time you
run the SQL that time value will change.
Try =Date() as default value. Date() does not carry a time value
(other than Midnight). Also make sure the [txtDate] control's Format
is set to a valid date format so that it is recognizes 7/25/2006 as a
date, rather than a string "7/25/2006"

i just noticed, as I sent my previous reply, that your first sentence
"Okay, so I have a form "formWorkloads" that has a control source of
sql statement" refers to the form's Control source. That is incorrect.
The SQL statement is the form's Record Source.

 

[kkleung89]

Okay, I'm doing a bit better. After plugging in values into the sql
statement in place of txtDate, I've figured 2 things out:
1) If the date is one without a record for, then I get the blank
screen.
2) If I give it today, then it works. When the default value for
txtDate() is set to today (even typed literally), it does not work.

It may be the same problem, but my suspicion is that there are two
different problems. For problem 1, I think I just need to use an iif
to see if it's null ahead of time. That should be an easy fix, right?
For problem 2, I'm guessing that the record source is figured before
txtDate is assigned its default value. Am I right about that, and what
do I do about that?

Trouble-shoot...
Open the datasheet view of your Record Source or create a query with this
SQL:

SELECT tbWorkloads.[Maximum Workload], (select count(*) from
tbAppointments where tbAppointments.[Appointment
Date]=forms!formWorkloads!txtDate) AS Expr1
FROM tbWorkloads, tbAppointments
WHERE (((tbWorkloads.[Workload
Date])=[forms]![formWorkloads]![txtDate]));

I'm not sure why you are selecting form tbAppointments since this table is
in the subquery.

This query will not be updateable. What are some of your form data
properties?
--
Duane Hookom
MS Access MVP

Changing it to date didn't work (though still a good tip), and I meant
record source. Other ideas?

On Tue, 25 Jul 2006 16:13:25 -0700, fredg wrote:

On 25 Jul 2006 15:40:48 -0700, [email protected] wrote:

Okay, so I have a form "formWorkloads" that has a control source of
sql
statement

SELECT tbWorkloads.[Maximum Workload], (select count(*) from
tbAppointments where tbAppointments.[Appointment
Date]=forms!formWorkloads!txtDate) AS Expr1
FROM tbWorkloads, tbAppointments
WHERE (((tbWorkloads.[Workload
Date])=[forms]![formWorkloads]![txtDate]));

I'm pretty sure that that's all "right", as in no typos. txtDate has
a
default value of now(), so when it loads, I would hope that it would
just load the information from the first day. Maximum Workload and
Expr1 go into other text boxes on the form. When I try and view the
form, however, it just gives me a blank screen. Can you tell me why?
Thanks in advance.

Now() includes a time value as well as a date value, so if you open
the form on 7/25/2006 at 4:15 PM, the SQL is looking for records that
equal 7/25/2006 4:15 PM. Unless the data has had a time value stored
with the date, you will never get records returned. And each time you
run the SQL that time value will change.
Try =Date() as default value. Date() does not carry a time value
(other than Midnight). Also make sure the [txtDate] control's Format
is set to a valid date format so that it is recognizes 7/25/2006 as a
date, rather than a string "7/25/2006"

i just noticed, as I sent my previous reply, that your first sentence
"Okay, so I have a form "formWorkloads" that has a control source of
sql statement" refers to the form's Control source. That is incorrect.
The SQL statement is the form's Record Source.

 

[Duane Hookom]

1) I'm not sure what you want to display if there is nothing to display
2) Do you have any appointment dates for today?
What did you enter into the default property?
What do you see in txtDate?
Does you Appointment Date store any time portion or just the date?

--
Duane Hookom
MS Access MVP

Okay, I'm doing a bit better. After plugging in values into the sql
statement in place of txtDate, I've figured 2 things out:
1) If the date is one without a record for, then I get the blank
screen.
2) If I give it today, then it works. When the default value for
txtDate() is set to today (even typed literally), it does not work.

It may be the same problem, but my suspicion is that there are two
different problems. For problem 1, I think I just need to use an iif
to see if it's null ahead of time. That should be an easy fix, right?
For problem 2, I'm guessing that the record source is figured before
txtDate is assigned its default value. Am I right about that, and what
do I do about that?

Trouble-shoot...
Open the datasheet view of your Record Source or create a query with this
SQL:

SELECT tbWorkloads.[Maximum Workload], (select count(*) from
tbAppointments where tbAppointments.[Appointment
Date]=forms!formWorkloads!txtDate) AS Expr1
FROM tbWorkloads, tbAppointments
WHERE (((tbWorkloads.[Workload
Date])=[forms]![formWorkloads]![txtDate]));

I'm not sure why you are selecting form tbAppointments since this table
is
in the subquery.

This query will not be updateable. What are some of your form data
properties?
--
Duane Hookom
MS Access MVP

Changing it to date didn't work (though still a good tip), and I meant
record source. Other ideas?
fredg wrote:
On Tue, 25 Jul 2006 16:13:25 -0700, fredg wrote:

On 25 Jul 2006 15:40:48 -0700, [email protected] wrote:

Okay, so I have a form "formWorkloads" that has a control source of
sql
statement

SELECT tbWorkloads.[Maximum Workload], (select count(*) from
tbAppointments where tbAppointments.[Appointment
Date]=forms!formWorkloads!txtDate) AS Expr1
FROM tbWorkloads, tbAppointments
WHERE (((tbWorkloads.[Workload
Date])=[forms]![formWorkloads]![txtDate]));

I'm pretty sure that that's all "right", as in no typos. txtDate
has
a
default value of now(), so when it loads, I would hope that it
would
just load the information from the first day. Maximum Workload and
Expr1 go into other text boxes on the form. When I try and view
the
form, however, it just gives me a blank screen. Can you tell me
why?
Thanks in advance.

Now() includes a time value as well as a date value, so if you open
the form on 7/25/2006 at 4:15 PM, the SQL is looking for records
that
equal 7/25/2006 4:15 PM. Unless the data has had a time value stored
with the date, you will never get records returned. And each time
you
run the SQL that time value will change.
Try =Date() as default value. Date() does not carry a time value
(other than Midnight). Also make sure the [txtDate] control's Format
is set to a valid date format so that it is recognizes 7/25/2006 as
a
date, rather than a string "7/25/2006"

i just noticed, as I sent my previous reply, that your first sentence
"Okay, so I have a form "formWorkloads" that has a control source of
sql statement" refers to the form's Control source. That is incorrect.
The SQL statement is the form's Record Source.