Error 2950

[relativehunter]

I am having a problem with a form and a query. When I open the form (called
Paper), it runs a query that adds a row to a table called Time. If Paper is
open and I try to run a query to see my total time on the form, I get a 2950
error. If I close the Paper form, then I have no problem calling the total
time query. I tried doing an explicit close of the Time table in the macro
that sends the row to the Time table, but that did not solve the problem. Any
ideas?

 

[Wayne-I-M]

Does your query have a start time and end time.
If so, you can't run the query to see your time on Paper with Paper still
open as you will not have an end time and the query will (when you try and
open it) try a half calculation (that like say 1 + = intead of 1 + 1 =).

Maybe I am not understanding.

Can you post the sql so we cna see it

 

[Daryl S]

Post the SQL to your query, so we can see if we spot something. What is the
text of Error 2950? What version of Access are you using?

 

[relativehunter]

The message specifically is "The expression is typed incorrectly, or it is
too complex to be evaluated." And gives an example.

The query is: SELECT Sum(Times.[Total time]) AS [Sum Of Total time],
[Forms]![Times]![Start] AS Start, [Forms]![Times]![End] AS [End]
FROM Times
WHERE (((DateValue([Start]))>=DateValue([Forms]![Times]![Start])) AND
((DateValue([End]))<=DateValue([Forms]![Times]![End])))
GROUP BY [Forms]![Times]![Start], [Forms]![Times]![End];
I have a form to select the beginning and ending dates.

 

[relativehunter]

I tried to insert both the start and end times (just made end time = start
time so that there was not a null in the end time, but that did not seem to
work either).

 

[relativehunter]

I changed the update query to fill in the same time in both begin and end, so
that end had a non-null time. Got the same exact message. Simplifying the
query does not seem to be necessary, since when the Paper form is not open,
the query runs fine. Seems to be some kind of interaction between the two
open objects.

Relativehunter -

Can your Start or End values in the table be null? If so, you need to
address that. I assume if they are null, then you don't have a Total time
for those records, and they should be exluded (see the WHERE clause below).

Also, I assume the form's Start and End fields are true dates. If your
table Start and End fields are true date/time fields, then simplify the query:

SELECT Sum(Times.[Total time]) AS [Sum Of Total time],
[Forms]![Times]![Start] AS Start, [Forms]![Times]![End] AS [End]
FROM Times
WHERE (((Times.[Start])>=[Forms]![Times]![Start]) AND
((DateValue(Times.[End]))<=[Forms]![Times]![End]))
GROUP BY [Forms]![Times]![Start], [Forms]![Times]![End]
WHERE [Start] is not null and [End] is not null;

--
Daryl S

The message specifically is "The expression is typed incorrectly, or it is
too complex to be evaluated." And gives an example.

The query is: SELECT Sum(Times.[Total time]) AS [Sum Of Total time],
[Forms]![Times]![Start] AS Start, [Forms]![Times]![End] AS [End]
FROM Times
WHERE (((DateValue([Start]))>=DateValue([Forms]![Times]![Start])) AND
((DateValue([End]))<=DateValue([Forms]![Times]![End])))
GROUP BY [Forms]![Times]![Start], [Forms]![Times]![End];
I have a form to select the beginning and ending dates.

 

[Daryl S]

Relativehunter -

Can your Start or End values in the table be null? If so, you need to
address that. I assume if they are null, then you don't have a Total time
for those records, and they should be exluded (see the WHERE clause below).

Also, I assume the form's Start and End fields are true dates. If your
table Start and End fields are true date/time fields, then simplify the query:

SELECT Sum(Times.[Total time]) AS [Sum Of Total time],
[Forms]![Times]![Start] AS Start, [Forms]![Times]![End] AS [End]
FROM Times
WHERE (((Times.[Start])>=[Forms]![Times]![Start]) AND
((DateValue(Times.[End]))<=[Forms]![Times]![End]))
GROUP BY [Forms]![Times]![Start], [Forms]![Times]![End]
WHERE [Start] is not null and [End] is not null;

 

[Daryl S]

Relativehunter -

You talk about the Paper form, but your query uses data from the Times form.
Could this be your problem?

Should [Forms]![Times]![Start] be [Forms]![Paper]![Start], etc.?

--
Daryl S

I changed the update query to fill in the same time in both begin and end, so
that end had a non-null time. Got the same exact message. Simplifying the
query does not seem to be necessary, since when the Paper form is not open,
the query runs fine. Seems to be some kind of interaction between the two
open objects.

Relativehunter -

Can your Start or End values in the table be null? If so, you need to
address that. I assume if they are null, then you don't have a Total time
for those records, and they should be exluded (see the WHERE clause below).

Also, I assume the form's Start and End fields are true dates. If your
table Start and End fields are true date/time fields, then simplify the query:

SELECT Sum(Times.[Total time]) AS [Sum Of Total time],
[Forms]![Times]![Start] AS Start, [Forms]![Times]![End] AS [End]
FROM Times
WHERE (((Times.[Start])>=[Forms]![Times]![Start]) AND
((DateValue(Times.[End]))<=[Forms]![Times]![End]))
GROUP BY [Forms]![Times]![Start], [Forms]![Times]![End]
WHERE [Start] is not null and [End] is not null;

--
Daryl S

The message specifically is "The expression is typed incorrectly, or it is
too complex to be evaluated." And gives an example.

The query is: SELECT Sum(Times.[Total time]) AS [Sum Of Total time],
[Forms]![Times]![Start] AS Start, [Forms]![Times]![End] AS [End]
FROM Times
WHERE (((DateValue([Start]))>=DateValue([Forms]![Times]![Start])) AND
((DateValue([End]))<=DateValue([Forms]![Times]![End])))
GROUP BY [Forms]![Times]![Start], [Forms]![Times]![End];
I have a form to select the beginning and ending dates.