SQL Agregate function return #Error

[shiro]

Hi all,
I put an unbound textbox in my form footer.
And then type
=Max([My field name])
but it return me #Error in that field.
Few days ago I did see it work well,but now it won't.
Why?Help some advise please.

 

[John W. Vinson]

Hi all,
I put an unbound textbox in my form footer.
And then type
=Max([My field name])
but it return me #Error in that field.
Few days ago I did see it work well,but now it won't.
Why?Help some advise please.

This will work only if [My field name] is in fact the name of a field in the
Form's Recordsource query. It will not work if it is just the name of a
Control on the form.

John W. Vinson [MVP]

 

[shiro]

I'm sure [My Field name] comes from my query's field name.
The property of Data source = with the name in my form.
And I changed the field name in my form ( cause I though
access confused to differ data source with the field name )
but it still doesn't work.Any other idea about what might prevent
the function return the correct result?Thank's.

Hi all,
I put an unbound textbox in my form footer.
And then type
=Max([My field name])
but it return me #Error in that field.
Few days ago I did see it work well,but now it won't.
Why?Help some advise please.

This will work only if [My field name] is in fact the name of a field in the
Form's Recordsource query. It will not work if it is just the name of a
Control on the form.

John W. Vinson [MVP]

 

[John W. Vinson]

I'm sure [My Field name] comes from my query's field name.
The property of Data source = with the name in my form.
And I changed the field name in my form ( cause I though
access confused to differ data source with the field name )
but it still doesn't work.Any other idea about what might prevent
the function return the correct result?Thank's.

Is the sum in the Form Footer (where it will work) or the Page Footer or
Detail section (where it will not)?

Perhaps you could post the SQL view of the form's Recordsource and the actual
control source of the textbox.

It may also be helpful to delete the control from the form; compact the
database; and recreate the control - you might have some subtle form
corruption.

John W. Vinson [MVP]

 

[shiro]

The sum fields are in the form footer.
Here is the sql statement of the query where the recordsource of
the form come from :

SELECT [Inspection result_tbl].Model, [Inspection result_tbl].[Input
voltage], [Inspection result_tbl].[Lot no], [Inspection result_tbl].[Input
date], [Inspection result_tbl].RPM_1, [Inspection result_tbl].Current_1,
[Inspection result_tbl].[Lock current_1], [Inspection result_tbl].RPM_2,
[Inspection result_tbl].Current_2, [Inspection result_tbl].[Lock current_2],
[Inspection result_tbl].Inspector, ([Input voltage]*[Current_1]) AS
WattCurr1, ([Input voltage]*[Current_2]) AS WattCurr2, ([Input
voltage]*[Lock current_1]) AS WattLockCurr1, ([Input voltage]*[Lock
current_2]) AS WattLockCurr2
FROM [Inspection result_tbl]
WHERE ((([Inspection result_tbl].Model)=[Forms]![Start input
data_frm]![list_Model]) AND (([Inspection result_tbl].[Lot
no])=[Forms]![Start input data_frm]![LotNo]))
ORDER BY [Inspection result_tbl].[Input date];

I never compact a database before,how to do it.
Does it will influence another database.Thank's for
your advance.

I'm sure [My Field name] comes from my query's field name.
The property of Data source = with the name in my form.
And I changed the field name in my form ( cause I though
access confused to differ data source with the field name )
but it still doesn't work.Any other idea about what might prevent
the function return the correct result?Thank's.

Is the sum in the Form Footer (where it will work) or the Page Footer or
Detail section (where it will not)?

Perhaps you could post the SQL view of the form's Recordsource and the actual
control source of the textbox.

It may also be helpful to delete the control from the form; compact the
database; and recreate the control - you might have some subtle form
corruption.

John W. Vinson [MVP]

 

[John W. Vinson]

The sum fields are in the form footer.
Here is the sql statement of the query where the recordsource of
the form come from :

SELECT [Inspection result_tbl].Model, [Inspection result_tbl].[Input
voltage], [Inspection result_tbl].[Lot no], [Inspection result_tbl].[Input
date], [Inspection result_tbl].RPM_1, [Inspection result_tbl].Current_1,
[Inspection result_tbl].[Lock current_1], [Inspection result_tbl].RPM_2,
[Inspection result_tbl].Current_2, [Inspection result_tbl].[Lock current_2],
[Inspection result_tbl].Inspector, ([Input voltage]*[Current_1]) AS
WattCurr1, ([Input voltage]*[Current_2]) AS WattCurr2, ([Input
voltage]*[Lock current_1]) AS WattLockCurr1, ([Input voltage]*[Lock
current_2]) AS WattLockCurr2
FROM [Inspection result_tbl]
WHERE ((([Inspection result_tbl].Model)=[Forms]![Start input
data_frm]![list_Model]) AND (([Inspection result_tbl].[Lot
no])=[Forms]![Start input data_frm]![LotNo]))
ORDER BY [Inspection result_tbl].[Input date];

Again: what is the Control Source of the sum field? What is the Datatype of
the field that you're summing?

I never compact a database before,how to do it.
Does it will influence another database.Thank's for
your advance.

Make a backup of the .mdb file first - it's very rare but compacting *can*
damage or destroy the database. Then use Tools... Database Utilities...
Compact and Repair. It will not affect any other database, just the one you're
compacting.

John W. Vinson [MVP]

 

[shiro]

I tried to compact my database
Tools > Databse Utilities > Compact and repair.
It works well now,but how to prevent the databse from
corrupted.
Thank's

The sum fields are in the form footer.
Here is the sql statement of the query where the recordsource of
the form come from :

SELECT [Inspection result_tbl].Model, [Inspection result_tbl].[Input
voltage], [Inspection result_tbl].[Lot no], [Inspection result_tbl].[Input
date], [Inspection result_tbl].RPM_1, [Inspection result_tbl].Current_1,
[Inspection result_tbl].[Lock current_1], [Inspection result_tbl].RPM_2,
[Inspection result_tbl].Current_2, [Inspection result_tbl].[Lock current_2],
[Inspection result_tbl].Inspector, ([Input voltage]*[Current_1]) AS
WattCurr1, ([Input voltage]*[Current_2]) AS WattCurr2, ([Input
voltage]*[Lock current_1]) AS WattLockCurr1, ([Input voltage]*[Lock
current_2]) AS WattLockCurr2
FROM [Inspection result_tbl]
WHERE ((([Inspection result_tbl].Model)=[Forms]![Start input
data_frm]![list_Model]) AND (([Inspection result_tbl].[Lot
no])=[Forms]![Start input data_frm]![LotNo]))
ORDER BY [Inspection result_tbl].[Input date];

I never compact a database before,how to do it.
Does it will influence another database.Thank's for
your advance.

I'm sure [My Field name] comes from my query's field name.
The property of Data source = with the name in my form.
And I changed the field name in my form ( cause I though
access confused to differ data source with the field name )
but it still doesn't work.Any other idea about what might prevent
the function return the correct result?Thank's.

Is the sum in the Form Footer (where it will work) or the Page Footer or
Detail section (where it will not)?

Perhaps you could post the SQL view of the form's Recordsource and the actual
control source of the textbox.

It may also be helpful to delete the control from the form; compact the
database; and recreate the control - you might have some subtle form
corruption.

John W. Vinson [MVP]

 

[John W. Vinson]

I tried to compact my database
Tools > Databse Utilities > Compact and repair.
It works well now,but how to prevent the databse from
corrupted.

That's a bit like saying "how can I prevent my car from having mechanical
problems". There's no absolute protection, in other words! Regular backups and
regular compaction are a good starting point, though.

Take a look at Tony Toews' corruption FAQ:

http://www.granite.ab.ca/access/corruptmdbs.htm

for some common causes, symptoms, and treatments for corruption.

John W. Vinson [MVP]