Complicated elements in a table

[vanguardaccess]

I have a table with the following fields:

[ID]---[Mark]

The [Mark] field is set as text, and can have values that are numerical
(from 0-100) or text (P or F).

I have a query that is built of this table with the same two fields.
However, when I run it, the following error occurs:

"The expression is typed incorrectly, or is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Any idea of what is wrong?

 

[Wayne-I-M]

Hi

This will not cause any problems

SELECT TableName.ID, TableName.Mark
FROM TableName;

Do you have anything else in the query that could make the error

 

[vanguardaccess]

Thanks for your reply. I think that I did a poor job of thinking through the
issue and explaining, as the more I look at my database the bigger the
problem I think this is...

First, I have a table with the following fields:
[ID]---[Mark]
[Mark] needs to be a text field because it can either be a number (from
1-100) or letter (P or F). It is being imported from an outside excel
spreadsheet (which needs to happen regularly), and if it is numerical the
table excludes the P's or F's.

The query that I'm running refers to a module. It turns the mark column
into a different scale (from 0-64 to 0,5, from 65-95 it applies the formula
([mark]-55)/10, from 96-100 to 4.0, and P's and F's stay as are).

I see that I'm going to have to redesign elements of my database, which is
okay. But what I need to be able to do is calculate on the text field in
both queries and reports. Is that possible? I would like it to ignore the
P's and F's, treat the numbers (from 0.5 to 4.0) that result from the module
as numbers, then calculate averages, for example.

Any help would be greatly appreciated.


--
--vanguardaccessman

Hi

This will not cause any problems

SELECT TableName.ID, TableName.Mark
FROM TableName;

Do you have anything else in the query that could make the error



--
Wayne
Manchester, England.

I have a table with the following fields:

[ID]---[Mark]

The [Mark] field is set as text, and can have values that are numerical
(from 0-100) or text (P or F).

I have a query that is built of this table with the same two fields.
However, when I run it, the following error occurs:

"The expression is typed incorrectly, or is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Any idea of what is wrong?

 

[Rob Parker]

Please post the SQL of your query, and the code for the custom function
(which is what I assume you mean when you say "module")you are using.
Without this, no-one will be able to help effectively.

Rob

Thanks for your reply. I think that I did a poor job of thinking through
the
issue and explaining, as the more I look at my database the bigger the
problem I think this is...

First, I have a table with the following fields:
[ID]---[Mark]
[Mark] needs to be a text field because it can either be a number (from
1-100) or letter (P or F). It is being imported from an outside excel
spreadsheet (which needs to happen regularly), and if it is numerical the
table excludes the P's or F's.

The query that I'm running refers to a module. It turns the mark column
into a different scale (from 0-64 to 0,5, from 65-95 it applies the
formula
([mark]-55)/10, from 96-100 to 4.0, and P's and F's stay as are).

I see that I'm going to have to redesign elements of my database, which is
okay. But what I need to be able to do is calculate on the text field in
both queries and reports. Is that possible? I would like it to ignore
the
P's and F's, treat the numbers (from 0.5 to 4.0) that result from the
module
as numbers, then calculate averages, for example.

Any help would be greatly appreciated.


--
--vanguardaccessman

Hi

This will not cause any problems

SELECT TableName.ID, TableName.Mark
FROM TableName;

Do you have anything else in the query that could make the error



--
Wayne
Manchester, England.

I have a table with the following fields:

[ID]---[Mark]

The [Mark] field is set as text, and can have values that are numerical
(from 0-100) or text (P or F).

I have a query that is built of this table with the same two fields.
However, when I run it, the following error occurs:

"The expression is typed incorrectly, or is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Any idea of what is wrong?

 

[Wayne-I-M]

Not sure at all what your trying to do but I always try and keep things
simple. Why not just use VAL (if >0) to get the number sectin of the field
and use that as you need. If VAL = 0 then use the text


--
Wayne
Manchester, England.

Thanks for your reply. I think that I did a poor job of thinking through the
issue and explaining, as the more I look at my database the bigger the
problem I think this is...

First, I have a table with the following fields:
[ID]---[Mark]
[Mark] needs to be a text field because it can either be a number (from
1-100) or letter (P or F). It is being imported from an outside excel
spreadsheet (which needs to happen regularly), and if it is numerical the
table excludes the P's or F's.

The query that I'm running refers to a module. It turns the mark column
into a different scale (from 0-64 to 0,5, from 65-95 it applies the formula
([mark]-55)/10, from 96-100 to 4.0, and P's and F's stay as are).

I see that I'm going to have to redesign elements of my database, which is
okay. But what I need to be able to do is calculate on the text field in
both queries and reports. Is that possible? I would like it to ignore the
P's and F's, treat the numbers (from 0.5 to 4.0) that result from the module
as numbers, then calculate averages, for example.

Any help would be greatly appreciated.


--
--vanguardaccessman

Hi

This will not cause any problems

SELECT TableName.ID, TableName.Mark
FROM TableName;

Do you have anything else in the query that could make the error



--
Wayne
Manchester, England.

I have a table with the following fields:

[ID]---[Mark]

The [Mark] field is set as text, and can have values that are numerical
(from 0-100) or text (P or F).

I have a query that is built of this table with the same two fields.
However, when I run it, the following error occurs:

"The expression is typed incorrectly, or is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Any idea of what is wrong?

 

[vanguardaccess]

Thanks for looking at this. What I'm trying to do is, from a spreadsheet
that I import from excel of student transcript data, ultimately create
transcripts. The table [Mark] field has values from 0-100, P or F (and some
other text values). I need to ultimately create a report for each student
that changes the 0-100 score to a 4.0 scale using the following calculation
(from 0-64 to 0.5, from 65-94 using the formula [Mark-55/10], from 95-100 to
4.0, and all text fields to stay as they are). Then I want the report to
calculate the average of the new grades (from the 4.0 scale), excluding any
text values.

There are many other fields that need to go on the report, so I apologize
for the excess info from the query (SQL below). But this is it:

SELECT tbl_bios.ACTIVE, tbl_allstudentsallgrades_hsst.StudentID,
tbl_allstudentsallgrades_hsst.LastName,
tbl_allstudentsallgrades_hsst.FirstName, tbl_allstudentsallgrades_hsst.Year,
tbl_allstudentsallgrades_hsst.Term, tbl_allstudentsallgrades_hsst.Course,
DLookUp("Department","tbl_CourseCodes_departments","CourseCodes='" & [Course]
& "'") AS DEPT, tbl_department_requiredcredits.Required_Credits,
tbl_allstudentsallgrades_hsst.Title, tbl_allstudentsallgrades_hsst.Mark,
tbl_allstudentsallgrades_hsst.Credits, DetermineResult([Mark]) AS Vangrade,
[FirstName] & " " & [LastName] AS FullName, tbl_bios.ADVISOR, tbl_bios.DOB,
tbl_bios.SEX, tbl_bios.ADDRESS, tbl_bios.CITY, tbl_bios.STATE, tbl_bios.ZIP,
tbl_bios.GRADE, tbl_bios.PARENT1, tbl_bios.PHONE1, [ADDRESS] & ", " & [CITY]
& ", " & [STATE] & " " & [ZIP] AS FnlAddress, [PARENT1] & ", " & [PHONE1] AS
FullContact, tbl_bios.COHORT, qry_finalGPA.AvgOfMark, qry_finalGPA.MarktoGPA,
tbl_bios.SPEDSTS, tbl_bios.ETHNICITY, tbl_bios.LOWEST_THIRD_CITYWIDE,
tbl_bios.LOWEST_THIRD_ELA_SCHOOL, tbl_bios.LOWEST_THIRD_MATH_SCHOOL,
tbl_allstudentsallgrades_hsst.School
FROM tbl_bios INNER JOIN ((qry_finalGPA INNER JOIN
tbl_allstudentsallgrades_hsst ON
qry_finalGPA.StudentID=tbl_allstudentsallgrades_hsst.StudentID) INNER JOIN
(tbl_coursecodes_departments INNER JOIN tbl_department_requiredcredits ON
tbl_coursecodes_departments.Department=tbl_department_requiredcredits.DepartmentList)
ON
tbl_allstudentsallgrades_hsst.Course=tbl_coursecodes_departments.CourseCodes)
ON tbl_bios.OSIS=tbl_allstudentsallgrades_hsst.StudentID
GROUP BY tbl_bios.ACTIVE, tbl_allstudentsallgrades_hsst.StudentID,
tbl_allstudentsallgrades_hsst.LastName,
tbl_allstudentsallgrades_hsst.FirstName, tbl_allstudentsallgrades_hsst.Year,
tbl_allstudentsallgrades_hsst.Term, tbl_allstudentsallgrades_hsst.Course,
DLookUp("Department","tbl_CourseCodes_departments","CourseCodes='" & [Course]
& "'"), tbl_department_requiredcredits.Required_Credits,
tbl_allstudentsallgrades_hsst.Title, tbl_allstudentsallgrades_hsst.Mark,
tbl_allstudentsallgrades_hsst.Credits, ([Mark]-55)/10, [FirstName] & " " &
[LastName], tbl_bios.ADVISOR, tbl_bios.DOB, tbl_bios.SEX, tbl_bios.ADDRESS,
tbl_bios.CITY, tbl_bios.STATE, tbl_bios.ZIP, tbl_bios.GRADE,
tbl_bios.PARENT1, tbl_bios.PHONE1, [ADDRESS] & ", " & [CITY] & ", " & [STATE]
& " " & [ZIP], [PARENT1] & ", " & [PHONE1], tbl_bios.COHORT,
qry_finalGPA.AvgOfMark, qry_finalGPA.MarktoGPA, tbl_bios.SPEDSTS,
tbl_bios.ETHNICITY, tbl_bios.LOWEST_THIRD_CITYWIDE,
tbl_bios.LOWEST_THIRD_ELA_SCHOOL, tbl_bios.LOWEST_THIRD_MATH_SCHOOL,
tbl_allstudentsallgrades_hsst.School
HAVING (((tbl_allstudentsallgrades_hsst.LastName) Like [Enter a Last Name] &
"*") AND ((tbl_bios.ADVISOR) Like [What Advisor Are You Looking For?] & "*")
AND ((tbl_bios.GRADE) Like [What Grade Are You Looking For?] & "*") AND
((tbl_bios.COHORT) Like [What Cohort Are You Looking For?] & "*"));


The DetermineResult[Mark] is the module that changes the numerical values
from 0-100 to the appropriate 4.0 scale. The code for that is:

Option Compare Database

Option Explicit

Public Function DetermineResult(Mark As Variant) As Variant
On Error Resume Next

If Len(Mark & "") > 0 Then

Select Case Mark
Case "P", "F"
DetermineResult = Mark
Case "0"
DetermineResult = 0.5
Case IsNumeric(Mark) = True
Mark = CCur(Mark)
Select Case Mark
Case 0 To 64
DetermineResult = 0.5
Case 65 To 95
DetermineResult = Format(([Mark] - 55) / 10, "0.0")
Case 96 To 100
DetermineResult = Format(4, "0.0")
Case Else
DetermineResult = "Out of range."
End Select
Case Else
DetermineResult = "Out of range."
End Select
End If

End Function

I'm having a few problems, all depending on the approach to solving the
problem. It seems to me that one solution would be to import a purely text
file so that all the non-numerical values show up. (If the Mark field is set
to number, the non-numerical values do not appear). However, this creates
the problem that now the calculations do not work on the numerical values
(because they are designated as text). So I suppose the question is, how can
I make the query only deal with the numerical items form the table and treat
them as such, even though on the table they are demarcated as text. (note:
the module will not work if the mark field is set to number).

Thanks again.



--
--vanguardaccessman

Please post the SQL of your query, and the code for the custom function
(which is what I assume you mean when you say "module")you are using.
Without this, no-one will be able to help effectively.

Rob

Thanks for your reply. I think that I did a poor job of thinking through
the
issue and explaining, as the more I look at my database the bigger the
problem I think this is...

First, I have a table with the following fields:
[ID]---[Mark]
[Mark] needs to be a text field because it can either be a number (from
1-100) or letter (P or F). It is being imported from an outside excel
spreadsheet (which needs to happen regularly), and if it is numerical the
table excludes the P's or F's.

The query that I'm running refers to a module. It turns the mark column
into a different scale (from 0-64 to 0,5, from 65-95 it applies the
formula
([mark]-55)/10, from 96-100 to 4.0, and P's and F's stay as are).

I see that I'm going to have to redesign elements of my database, which is
okay. But what I need to be able to do is calculate on the text field in
both queries and reports. Is that possible? I would like it to ignore
the
P's and F's, treat the numbers (from 0.5 to 4.0) that result from the
module
as numbers, then calculate averages, for example.

Any help would be greatly appreciated.


--
--vanguardaccessman

Hi

This will not cause any problems

SELECT TableName.ID, TableName.Mark
FROM TableName;

Do you have anything else in the query that could make the error



--
Wayne
Manchester, England.



:

I have a table with the following fields:

[ID]---[Mark]

The [Mark] field is set as text, and can have values that are numerical
(from 0-100) or text (P or F).

I have a query that is built of this table with the same two fields.
However, when I run it, the following error occurs:

"The expression is typed incorrectly, or is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Any idea of what is wrong?

 

[Wayne-I-M]

Hi

Sorry about this but you really should cut it down and simplify it.

1st run the inport through an append query - use this to extract the numbers
or text from the field and then place then into different fields (one text
one number) "then" use the data as you need it. You can alocate the scores
within the append very simply - looking at your sql you know how to do this
already so won't go through nested IIf's.

I have looked at your sql and it seems to me that it is way too much for
what you need (not the basic query which looks fine but the add on's you are
trying to run). The trick is to do a little as possible to get the job done.
If you want to run this a some type of test for an exam then fine but if you
just need it to work - cut it all down.



--
Wayne
Manchester, England.

Thanks for looking at this. What I'm trying to do is, from a spreadsheet
that I import from excel of student transcript data, ultimately create
transcripts. The table [Mark] field has values from 0-100, P or F (and some
other text values). I need to ultimately create a report for each student
that changes the 0-100 score to a 4.0 scale using the following calculation
(from 0-64 to 0.5, from 65-94 using the formula [Mark-55/10], from 95-100 to
4.0, and all text fields to stay as they are). Then I want the report to
calculate the average of the new grades (from the 4.0 scale), excluding any
text values.

There are many other fields that need to go on the report, so I apologize
for the excess info from the query (SQL below). But this is it:

SELECT tbl_bios.ACTIVE, tbl_allstudentsallgrades_hsst.StudentID,
tbl_allstudentsallgrades_hsst.LastName,
tbl_allstudentsallgrades_hsst.FirstName, tbl_allstudentsallgrades_hsst.Year,
tbl_allstudentsallgrades_hsst.Term, tbl_allstudentsallgrades_hsst.Course,
DLookUp("Department","tbl_CourseCodes_departments","CourseCodes='" & [Course]
& "'") AS DEPT, tbl_department_requiredcredits.Required_Credits,
tbl_allstudentsallgrades_hsst.Title, tbl_allstudentsallgrades_hsst.Mark,
tbl_allstudentsallgrades_hsst.Credits, DetermineResult([Mark]) AS Vangrade,
[FirstName] & " " & [LastName] AS FullName, tbl_bios.ADVISOR, tbl_bios.DOB,
tbl_bios.SEX, tbl_bios.ADDRESS, tbl_bios.CITY, tbl_bios.STATE, tbl_bios.ZIP,
tbl_bios.GRADE, tbl_bios.PARENT1, tbl_bios.PHONE1, [ADDRESS] & ", " & [CITY]
& ", " & [STATE] & " " & [ZIP] AS FnlAddress, [PARENT1] & ", " & [PHONE1] AS
FullContact, tbl_bios.COHORT, qry_finalGPA.AvgOfMark, qry_finalGPA.MarktoGPA,
tbl_bios.SPEDSTS, tbl_bios.ETHNICITY, tbl_bios.LOWEST_THIRD_CITYWIDE,
tbl_bios.LOWEST_THIRD_ELA_SCHOOL, tbl_bios.LOWEST_THIRD_MATH_SCHOOL,
tbl_allstudentsallgrades_hsst.School
FROM tbl_bios INNER JOIN ((qry_finalGPA INNER JOIN
tbl_allstudentsallgrades_hsst ON
qry_finalGPA.StudentID=tbl_allstudentsallgrades_hsst.StudentID) INNER JOIN
(tbl_coursecodes_departments INNER JOIN tbl_department_requiredcredits ON
tbl_coursecodes_departments.Department=tbl_department_requiredcredits.DepartmentList)
ON
tbl_allstudentsallgrades_hsst.Course=tbl_coursecodes_departments.CourseCodes)
ON tbl_bios.OSIS=tbl_allstudentsallgrades_hsst.StudentID
GROUP BY tbl_bios.ACTIVE, tbl_allstudentsallgrades_hsst.StudentID,
tbl_allstudentsallgrades_hsst.LastName,
tbl_allstudentsallgrades_hsst.FirstName, tbl_allstudentsallgrades_hsst.Year,
tbl_allstudentsallgrades_hsst.Term, tbl_allstudentsallgrades_hsst.Course,
DLookUp("Department","tbl_CourseCodes_departments","CourseCodes='" & [Course]
& "'"), tbl_department_requiredcredits.Required_Credits,
tbl_allstudentsallgrades_hsst.Title, tbl_allstudentsallgrades_hsst.Mark,
tbl_allstudentsallgrades_hsst.Credits, ([Mark]-55)/10, [FirstName] & " " &
[LastName], tbl_bios.ADVISOR, tbl_bios.DOB, tbl_bios.SEX, tbl_bios.ADDRESS,
tbl_bios.CITY, tbl_bios.STATE, tbl_bios.ZIP, tbl_bios.GRADE,
tbl_bios.PARENT1, tbl_bios.PHONE1, [ADDRESS] & ", " & [CITY] & ", " & [STATE]
& " " & [ZIP], [PARENT1] & ", " & [PHONE1], tbl_bios.COHORT,
qry_finalGPA.AvgOfMark, qry_finalGPA.MarktoGPA, tbl_bios.SPEDSTS,
tbl_bios.ETHNICITY, tbl_bios.LOWEST_THIRD_CITYWIDE,
tbl_bios.LOWEST_THIRD_ELA_SCHOOL, tbl_bios.LOWEST_THIRD_MATH_SCHOOL,
tbl_allstudentsallgrades_hsst.School
HAVING (((tbl_allstudentsallgrades_hsst.LastName) Like [Enter a Last Name] &
"*") AND ((tbl_bios.ADVISOR) Like [What Advisor Are You Looking For?] & "*")
AND ((tbl_bios.GRADE) Like [What Grade Are You Looking For?] & "*") AND
((tbl_bios.COHORT) Like [What Cohort Are You Looking For?] & "*"));


The DetermineResult[Mark] is the module that changes the numerical values
from 0-100 to the appropriate 4.0 scale. The code for that is:

Option Compare Database

Option Explicit

Public Function DetermineResult(Mark As Variant) As Variant
On Error Resume Next

If Len(Mark & "") > 0 Then

Select Case Mark
Case "P", "F"
DetermineResult = Mark
Case "0"
DetermineResult = 0.5
Case IsNumeric(Mark) = True
Mark = CCur(Mark)
Select Case Mark
Case 0 To 64
DetermineResult = 0.5
Case 65 To 95
DetermineResult = Format(([Mark] - 55) / 10, "0.0")
Case 96 To 100
DetermineResult = Format(4, "0.0")
Case Else
DetermineResult = "Out of range."
End Select
Case Else
DetermineResult = "Out of range."
End Select
End If

End Function

I'm having a few problems, all depending on the approach to solving the
problem. It seems to me that one solution would be to import a purely text
file so that all the non-numerical values show up. (If the Mark field is set
to number, the non-numerical values do not appear). However, this creates
the problem that now the calculations do not work on the numerical values
(because they are designated as text). So I suppose the question is, how can
I make the query only deal with the numerical items form the table and treat
them as such, even though on the table they are demarcated as text. (note:
the module will not work if the mark field is set to number).

Thanks again.



--
--vanguardaccessman

Please post the SQL of your query, and the code for the custom function
(which is what I assume you mean when you say "module")you are using.
Without this, no-one will be able to help effectively.

Rob

Thanks for your reply. I think that I did a poor job of thinking through
the
issue and explaining, as the more I look at my database the bigger the
problem I think this is...

First, I have a table with the following fields:
[ID]---[Mark]
[Mark] needs to be a text field because it can either be a number (from
1-100) or letter (P or F). It is being imported from an outside excel
spreadsheet (which needs to happen regularly), and if it is numerical the
table excludes the P's or F's.

The query that I'm running refers to a module. It turns the mark column
into a different scale (from 0-64 to 0,5, from 65-95 it applies the
formula
([mark]-55)/10, from 96-100 to 4.0, and P's and F's stay as are).

I see that I'm going to have to redesign elements of my database, which is
okay. But what I need to be able to do is calculate on the text field in
both queries and reports. Is that possible? I would like it to ignore
the
P's and F's, treat the numbers (from 0.5 to 4.0) that result from the
module
as numbers, then calculate averages, for example.

Any help would be greatly appreciated.


--
--vanguardaccessman


:

Hi

This will not cause any problems

SELECT TableName.ID, TableName.Mark
FROM TableName;

Do you have anything else in the query that could make the error



--
Wayne
Manchester, England.



:

I have a table with the following fields:

[ID]---[Mark]

The [Mark] field is set as text, and can have values that are numerical
(from 0-100) or text (P or F).

I have a query that is built of this table with the same two fields.
However, when I run it, the following error occurs:

"The expression is typed incorrectly, or is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Any idea of what is wrong?

 

[Ken Sheridan]

Leaving the complexity or otherwise of your query aside, lets concentrate on
the fundamental point at issue. You can easily identify the numeric return
values, i.e. those strings which are valid numbers, of the DetermineResult
function by means of the IsNumeric function, just as you've done in the
function itself. You can then use this as the criterion of an IIF function
call to return values which are acceptable to whatever aggregation operator
is being used.

Taking a simple example, say we have a table StudentMarks as follows:

StudentID SubjectID Mark
1 1 35
1 2 68
1 3 P
1 4 F
2 1 80
2 2 P
2 3 59
2 4 75

If we apply your function to this table:

SELECT StudentID, SubjectID,
DetermineResult([Mark]) AS Result
FROM StudentMarks;

we get a result set of:

StudentID SubjectID Result
1 1 0.5
1 2 1.3
1 3 P
1 4 F
2 1 2.5
2 2 P
2 3 0.5
2 4 2.0

Say you want the average results (as returned by your function) per subject,
computing the averages on only those rows with numeric values, i.e. ignoring
the non-numeric values in the above result set, then you'd get the IIF
function to return the numeric value or a NULL like so:

SELECT SubjectID,
AVG(DETERMINERESULT(IIF(ISNUMERIC([Mark]),[Mark],NULL)))
AS [Average Result Per Subject]
FROM StudentMarks
GROUP BY SubjectID;

This gives a result set of:

SubjectID Average Result Per Subject
1 1.5
2 1.3
3 0.5
4 2

If on the other hand you wish to compute the averages per subject on all
rows, including those where the DetremineFunction returns a non-numeric
value, i.e. treating the values for these as 0 where the second query above
has returned NULL, then you'd apply the AVG operator to the return value of
The NZ fuction like this:

SELECT SubjectID,
AVG(NZ(DETERMINERESULT(IIF(ISNUMERIC([Mark]),[Mark],NULL)),0))
AS [Average Result Per Subject]
FROM StudentMarks
GROUP BY SubjectID;

which gives a result set of:

SubjectID Average Result Per Subject
1 1.5
2 0.65
3 0.25
4 1

The same principles would apply of course however you group the query or
whatever aggregation operator you use.

Ken Sheridan
Stafford, England

Thanks for looking at this. What I'm trying to do is, from a spreadsheet
that I import from excel of student transcript data, ultimately create
transcripts. The table [Mark] field has values from 0-100, P or F (and some
other text values). I need to ultimately create a report for each student
that changes the 0-100 score to a 4.0 scale using the following calculation
(from 0-64 to 0.5, from 65-94 using the formula [Mark-55/10], from 95-100 to
4.0, and all text fields to stay as they are). Then I want the report to
calculate the average of the new grades (from the 4.0 scale), excluding any
text values.

There are many other fields that need to go on the report, so I apologize
for the excess info from the query (SQL below). But this is it:

SELECT tbl_bios.ACTIVE, tbl_allstudentsallgrades_hsst.StudentID,
tbl_allstudentsallgrades_hsst.LastName,
tbl_allstudentsallgrades_hsst.FirstName, tbl_allstudentsallgrades_hsst.Year,
tbl_allstudentsallgrades_hsst.Term, tbl_allstudentsallgrades_hsst.Course,
DLookUp("Department","tbl_CourseCodes_departments","CourseCodes='" & [Course]
& "'") AS DEPT, tbl_department_requiredcredits.Required_Credits,
tbl_allstudentsallgrades_hsst.Title, tbl_allstudentsallgrades_hsst.Mark,
tbl_allstudentsallgrades_hsst.Credits, DetermineResult([Mark]) AS Vangrade,
[FirstName] & " " & [LastName] AS FullName, tbl_bios.ADVISOR, tbl_bios.DOB,
tbl_bios.SEX, tbl_bios.ADDRESS, tbl_bios.CITY, tbl_bios.STATE, tbl_bios.ZIP,
tbl_bios.GRADE, tbl_bios.PARENT1, tbl_bios.PHONE1, [ADDRESS] & ", " & [CITY]
& ", " & [STATE] & " " & [ZIP] AS FnlAddress, [PARENT1] & ", " & [PHONE1] AS
FullContact, tbl_bios.COHORT, qry_finalGPA.AvgOfMark, qry_finalGPA.MarktoGPA,
tbl_bios.SPEDSTS, tbl_bios.ETHNICITY, tbl_bios.LOWEST_THIRD_CITYWIDE,
tbl_bios.LOWEST_THIRD_ELA_SCHOOL, tbl_bios.LOWEST_THIRD_MATH_SCHOOL,
tbl_allstudentsallgrades_hsst.School
FROM tbl_bios INNER JOIN ((qry_finalGPA INNER JOIN
tbl_allstudentsallgrades_hsst ON
qry_finalGPA.StudentID=tbl_allstudentsallgrades_hsst.StudentID) INNER JOIN
(tbl_coursecodes_departments INNER JOIN tbl_department_requiredcredits ON
tbl_coursecodes_departments.Department=tbl_department_requiredcredits.DepartmentList)
ON
tbl_allstudentsallgrades_hsst.Course=tbl_coursecodes_departments.CourseCodes)
ON tbl_bios.OSIS=tbl_allstudentsallgrades_hsst.StudentID
GROUP BY tbl_bios.ACTIVE, tbl_allstudentsallgrades_hsst.StudentID,
tbl_allstudentsallgrades_hsst.LastName,
tbl_allstudentsallgrades_hsst.FirstName, tbl_allstudentsallgrades_hsst.Year,
tbl_allstudentsallgrades_hsst.Term, tbl_allstudentsallgrades_hsst.Course,
DLookUp("Department","tbl_CourseCodes_departments","CourseCodes='" & [Course]
& "'"), tbl_department_requiredcredits.Required_Credits,
tbl_allstudentsallgrades_hsst.Title, tbl_allstudentsallgrades_hsst.Mark,
tbl_allstudentsallgrades_hsst.Credits, ([Mark]-55)/10, [FirstName] & " " &
[LastName], tbl_bios.ADVISOR, tbl_bios.DOB, tbl_bios.SEX, tbl_bios.ADDRESS,
tbl_bios.CITY, tbl_bios.STATE, tbl_bios.ZIP, tbl_bios.GRADE,
tbl_bios.PARENT1, tbl_bios.PHONE1, [ADDRESS] & ", " & [CITY] & ", " & [STATE]
& " " & [ZIP], [PARENT1] & ", " & [PHONE1], tbl_bios.COHORT,
qry_finalGPA.AvgOfMark, qry_finalGPA.MarktoGPA, tbl_bios.SPEDSTS,
tbl_bios.ETHNICITY, tbl_bios.LOWEST_THIRD_CITYWIDE,
tbl_bios.LOWEST_THIRD_ELA_SCHOOL, tbl_bios.LOWEST_THIRD_MATH_SCHOOL,
tbl_allstudentsallgrades_hsst.School
HAVING (((tbl_allstudentsallgrades_hsst.LastName) Like [Enter a Last Name] &
"*") AND ((tbl_bios.ADVISOR) Like [What Advisor Are You Looking For?] & "*")
AND ((tbl_bios.GRADE) Like [What Grade Are You Looking For?] & "*") AND
((tbl_bios.COHORT) Like [What Cohort Are You Looking For?] & "*"));


The DetermineResult[Mark] is the module that changes the numerical values
from 0-100 to the appropriate 4.0 scale. The code for that is:

Option Compare Database

Option Explicit

Public Function DetermineResult(Mark As Variant) As Variant
On Error Resume Next

If Len(Mark & "") > 0 Then

Select Case Mark
Case "P", "F"
DetermineResult = Mark
Case "0"
DetermineResult = 0.5
Case IsNumeric(Mark) = True
Mark = CCur(Mark)
Select Case Mark
Case 0 To 64
DetermineResult = 0.5
Case 65 To 95
DetermineResult = Format(([Mark] - 55) / 10, "0.0")
Case 96 To 100
DetermineResult = Format(4, "0.0")
Case Else
DetermineResult = "Out of range."
End Select
Case Else
DetermineResult = "Out of range."
End Select
End If

End Function

I'm having a few problems, all depending on the approach to solving the
problem. It seems to me that one solution would be to import a purely text
file so that all the non-numerical values show up. (If the Mark field is set
to number, the non-numerical values do not appear). However, this creates
the problem that now the calculations do not work on the numerical values
(because they are designated as text). So I suppose the question is, how can
I make the query only deal with the numerical items form the table and treat
them as such, even though on the table they are demarcated as text. (note:
the module will not work if the mark field is set to number).

Thanks again.



--
--vanguardaccessman

Please post the SQL of your query, and the code for the custom function
(which is what I assume you mean when you say "module")you are using.
Without this, no-one will be able to help effectively.

Rob

Thanks for your reply. I think that I did a poor job of thinking through
the
issue and explaining, as the more I look at my database the bigger the
problem I think this is...

First, I have a table with the following fields:
[ID]---[Mark]
[Mark] needs to be a text field because it can either be a number (from
1-100) or letter (P or F). It is being imported from an outside excel
spreadsheet (which needs to happen regularly), and if it is numerical the
table excludes the P's or F's.

The query that I'm running refers to a module. It turns the mark column
into a different scale (from 0-64 to 0,5, from 65-95 it applies the
formula
([mark]-55)/10, from 96-100 to 4.0, and P's and F's stay as are).

I see that I'm going to have to redesign elements of my database, which is
okay. But what I need to be able to do is calculate on the text field in
both queries and reports. Is that possible? I would like it to ignore
the
P's and F's, treat the numbers (from 0.5 to 4.0) that result from the
module
as numbers, then calculate averages, for example.

Any help would be greatly appreciated.


--
--vanguardaccessman


:

Hi

This will not cause any problems

SELECT TableName.ID, TableName.Mark
FROM TableName;

Do you have anything else in the query that could make the error



--
Wayne
Manchester, England.



:

I have a table with the following fields:

[ID]---[Mark]

The [Mark] field is set as text, and can have values that are numerical
(from 0-100) or text (P or F).

I have a query that is built of this table with the same two fields.
However, when I run it, the following error occurs:

"The expression is typed incorrectly, or is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Any idea of what is wrong?

 

[vanguardaccess]

Thanks, I think ultimately the problem will be solved in the way that you say
-- importing the data than having it split the field into two -- putting the
numeric records in one and the non-numeric in another. When running a
report, I can then just stack the two fields on top of one another and they
will show. This fits in perfectly to everything else I'm trying to do,
particularly with the module, queried I already have set up, etc.

Understanding the concept, I am having trouble with the nested IIF. (I'm
still new to Access, SQL, and VBA).

How can I import into a query that will split the data into a table in the
way you say?

These are the fields I'm working with:

QUERY (THAT I'M IMPORTING TO)
[STUDENTID]---[MARK]

TABLE (RELATED TO THE QUERY)
[STUDENTID]---[MARK_NUMERIC]---[MARK_TEXT]

Many thanks for your help.
--
--vanguardaccessman

Hi

Sorry about this but you really should cut it down and simplify it.

1st run the inport through an append query - use this to extract the numbers
or text from the field and then place then into different fields (one text
one number) "then" use the data as you need it. You can alocate the scores
within the append very simply - looking at your sql you know how to do this
already so won't go through nested IIf's.

I have looked at your sql and it seems to me that it is way too much for
what you need (not the basic query which looks fine but the add on's you are
trying to run). The trick is to do a little as possible to get the job done.
If you want to run this a some type of test for an exam then fine but if you
just need it to work - cut it all down.



--
Wayne
Manchester, England.

Thanks for looking at this. What I'm trying to do is, from a spreadsheet
that I import from excel of student transcript data, ultimately create
transcripts. The table [Mark] field has values from 0-100, P or F (and some
other text values). I need to ultimately create a report for each student
that changes the 0-100 score to a 4.0 scale using the following calculation
(from 0-64 to 0.5, from 65-94 using the formula [Mark-55/10], from 95-100 to
4.0, and all text fields to stay as they are). Then I want the report to
calculate the average of the new grades (from the 4.0 scale), excluding any
text values.

There are many other fields that need to go on the report, so I apologize
for the excess info from the query (SQL below). But this is it:

SELECT tbl_bios.ACTIVE, tbl_allstudentsallgrades_hsst.StudentID,
tbl_allstudentsallgrades_hsst.LastName,
tbl_allstudentsallgrades_hsst.FirstName, tbl_allstudentsallgrades_hsst.Year,
tbl_allstudentsallgrades_hsst.Term, tbl_allstudentsallgrades_hsst.Course,
DLookUp("Department","tbl_CourseCodes_departments","CourseCodes='" & [Course]
& "'") AS DEPT, tbl_department_requiredcredits.Required_Credits,
tbl_allstudentsallgrades_hsst.Title, tbl_allstudentsallgrades_hsst.Mark,
tbl_allstudentsallgrades_hsst.Credits, DetermineResult([Mark]) AS Vangrade,
[FirstName] & " " & [LastName] AS FullName, tbl_bios.ADVISOR, tbl_bios.DOB,
tbl_bios.SEX, tbl_bios.ADDRESS, tbl_bios.CITY, tbl_bios.STATE, tbl_bios.ZIP,
tbl_bios.GRADE, tbl_bios.PARENT1, tbl_bios.PHONE1, [ADDRESS] & ", " & [CITY]
& ", " & [STATE] & " " & [ZIP] AS FnlAddress, [PARENT1] & ", " & [PHONE1] AS
FullContact, tbl_bios.COHORT, qry_finalGPA.AvgOfMark, qry_finalGPA.MarktoGPA,
tbl_bios.SPEDSTS, tbl_bios.ETHNICITY, tbl_bios.LOWEST_THIRD_CITYWIDE,
tbl_bios.LOWEST_THIRD_ELA_SCHOOL, tbl_bios.LOWEST_THIRD_MATH_SCHOOL,
tbl_allstudentsallgrades_hsst.School
FROM tbl_bios INNER JOIN ((qry_finalGPA INNER JOIN
tbl_allstudentsallgrades_hsst ON
qry_finalGPA.StudentID=tbl_allstudentsallgrades_hsst.StudentID) INNER JOIN
(tbl_coursecodes_departments INNER JOIN tbl_department_requiredcredits ON
tbl_coursecodes_departments.Department=tbl_department_requiredcredits.DepartmentList)
ON
tbl_allstudentsallgrades_hsst.Course=tbl_coursecodes_departments.CourseCodes)
ON tbl_bios.OSIS=tbl_allstudentsallgrades_hsst.StudentID
GROUP BY tbl_bios.ACTIVE, tbl_allstudentsallgrades_hsst.StudentID,
tbl_allstudentsallgrades_hsst.LastName,
tbl_allstudentsallgrades_hsst.FirstName, tbl_allstudentsallgrades_hsst.Year,
tbl_allstudentsallgrades_hsst.Term, tbl_allstudentsallgrades_hsst.Course,
DLookUp("Department","tbl_CourseCodes_departments","CourseCodes='" & [Course]
& "'"), tbl_department_requiredcredits.Required_Credits,
tbl_allstudentsallgrades_hsst.Title, tbl_allstudentsallgrades_hsst.Mark,
tbl_allstudentsallgrades_hsst.Credits, ([Mark]-55)/10, [FirstName] & " " &
[LastName], tbl_bios.ADVISOR, tbl_bios.DOB, tbl_bios.SEX, tbl_bios.ADDRESS,
tbl_bios.CITY, tbl_bios.STATE, tbl_bios.ZIP, tbl_bios.GRADE,
tbl_bios.PARENT1, tbl_bios.PHONE1, [ADDRESS] & ", " & [CITY] & ", " & [STATE]
& " " & [ZIP], [PARENT1] & ", " & [PHONE1], tbl_bios.COHORT,
qry_finalGPA.AvgOfMark, qry_finalGPA.MarktoGPA, tbl_bios.SPEDSTS,
tbl_bios.ETHNICITY, tbl_bios.LOWEST_THIRD_CITYWIDE,
tbl_bios.LOWEST_THIRD_ELA_SCHOOL, tbl_bios.LOWEST_THIRD_MATH_SCHOOL,
tbl_allstudentsallgrades_hsst.School
HAVING (((tbl_allstudentsallgrades_hsst.LastName) Like [Enter a Last Name] &
"*") AND ((tbl_bios.ADVISOR) Like [What Advisor Are You Looking For?] & "*")
AND ((tbl_bios.GRADE) Like [What Grade Are You Looking For?] & "*") AND
((tbl_bios.COHORT) Like [What Cohort Are You Looking For?] & "*"));


The DetermineResult[Mark] is the module that changes the numerical values
from 0-100 to the appropriate 4.0 scale. The code for that is:

Option Compare Database

Option Explicit

Public Function DetermineResult(Mark As Variant) As Variant
On Error Resume Next

If Len(Mark & "") > 0 Then

Select Case Mark
Case "P", "F"
DetermineResult = Mark
Case "0"
DetermineResult = 0.5
Case IsNumeric(Mark) = True
Mark = CCur(Mark)
Select Case Mark
Case 0 To 64
DetermineResult = 0.5
Case 65 To 95
DetermineResult = Format(([Mark] - 55) / 10, "0.0")
Case 96 To 100
DetermineResult = Format(4, "0.0")
Case Else
DetermineResult = "Out of range."
End Select
Case Else
DetermineResult = "Out of range."
End Select
End If

End Function

I'm having a few problems, all depending on the approach to solving the
problem. It seems to me that one solution would be to import a purely text
file so that all the non-numerical values show up. (If the Mark field is set
to number, the non-numerical values do not appear). However, this creates
the problem that now the calculations do not work on the numerical values
(because they are designated as text). So I suppose the question is, how can
I make the query only deal with the numerical items form the table and treat
them as such, even though on the table they are demarcated as text. (note:
the module will not work if the mark field is set to number).

Thanks again.



--
--vanguardaccessman

Please post the SQL of your query, and the code for the custom function
(which is what I assume you mean when you say "module")you are using.
Without this, no-one will be able to help effectively.

Rob

Thanks for your reply. I think that I did a poor job of thinking through
the
issue and explaining, as the more I look at my database the bigger the
problem I think this is...

First, I have a table with the following fields:
[ID]---[Mark]
[Mark] needs to be a text field because it can either be a number (from
1-100) or letter (P or F). It is being imported from an outside excel
spreadsheet (which needs to happen regularly), and if it is numerical the
table excludes the P's or F's.

The query that I'm running refers to a module. It turns the mark column
into a different scale (from 0-64 to 0,5, from 65-95 it applies the
formula
([mark]-55)/10, from 96-100 to 4.0, and P's and F's stay as are).

I see that I'm going to have to redesign elements of my database, which is
okay. But what I need to be able to do is calculate on the text field in
both queries and reports. Is that possible? I would like it to ignore
the
P's and F's, treat the numbers (from 0.5 to 4.0) that result from the
module
as numbers, then calculate averages, for example.

Any help would be greatly appreciated.


--
--vanguardaccessman


:

Hi

This will not cause any problems

SELECT TableName.ID, TableName.Mark
FROM TableName;

Do you have anything else in the query that could make the error



--
Wayne
Manchester, England.



:

I have a table with the following fields:

[ID]---[Mark]

The [Mark] field is set as text, and can have values that are numerical
(from 0-100) or text (P or F).

I have a query that is built of this table with the same two fields.
However, when I run it, the following error occurs:

"The expression is typed incorrectly, or is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Any idea of what is wrong?

 

[Ken Sheridan]

INSERT INTO Destination(StudentID, Mark_Numeric, Mark_Text)
SELECT StudentID, IIF(ISNUMERIC(Mark),Mark,NULL),
IIF(ISNUMERIC(Mark),NULL,Mark)
FROM Source;

Ken Sheridan
Stafford, England

Thanks, I think ultimately the problem will be solved in the way that you say
-- importing the data than having it split the field into two -- putting the
numeric records in one and the non-numeric in another. When running a
report, I can then just stack the two fields on top of one another and they
will show. This fits in perfectly to everything else I'm trying to do,
particularly with the module, queried I already have set up, etc.

Understanding the concept, I am having trouble with the nested IIF. (I'm
still new to Access, SQL, and VBA).

How can I import into a query that will split the data into a table in the
way you say?

These are the fields I'm working with:

QUERY (THAT I'M IMPORTING TO)
[STUDENTID]---[MARK]

TABLE (RELATED TO THE QUERY)
[STUDENTID]---[MARK_NUMERIC]---[MARK_TEXT]

Many thanks for your help.
--
--vanguardaccessman

Hi

Sorry about this but you really should cut it down and simplify it.

1st run the inport through an append query - use this to extract the numbers
or text from the field and then place then into different fields (one text
one number) "then" use the data as you need it. You can alocate the scores
within the append very simply - looking at your sql you know how to do this
already so won't go through nested IIf's.

I have looked at your sql and it seems to me that it is way too much for
what you need (not the basic query which looks fine but the add on's you are
trying to run). The trick is to do a little as possible to get the job done.
If you want to run this a some type of test for an exam then fine but if you
just need it to work - cut it all down.



--
Wayne
Manchester, England.

Thanks for looking at this. What I'm trying to do is, from a spreadsheet
that I import from excel of student transcript data, ultimately create
transcripts. The table [Mark] field has values from 0-100, P or F (and some
other text values). I need to ultimately create a report for each student
that changes the 0-100 score to a 4.0 scale using the following calculation
(from 0-64 to 0.5, from 65-94 using the formula [Mark-55/10], from 95-100 to
4.0, and all text fields to stay as they are). Then I want the report to
calculate the average of the new grades (from the 4.0 scale), excluding any
text values.

There are many other fields that need to go on the report, so I apologize
for the excess info from the query (SQL below). But this is it:

SELECT tbl_bios.ACTIVE, tbl_allstudentsallgrades_hsst.StudentID,
tbl_allstudentsallgrades_hsst.LastName,
tbl_allstudentsallgrades_hsst.FirstName, tbl_allstudentsallgrades_hsst.Year,
tbl_allstudentsallgrades_hsst.Term, tbl_allstudentsallgrades_hsst.Course,
DLookUp("Department","tbl_CourseCodes_departments","CourseCodes='" & [Course]
& "'") AS DEPT, tbl_department_requiredcredits.Required_Credits,
tbl_allstudentsallgrades_hsst.Title, tbl_allstudentsallgrades_hsst.Mark,
tbl_allstudentsallgrades_hsst.Credits, DetermineResult([Mark]) AS Vangrade,
[FirstName] & " " & [LastName] AS FullName, tbl_bios.ADVISOR, tbl_bios.DOB,
tbl_bios.SEX, tbl_bios.ADDRESS, tbl_bios.CITY, tbl_bios.STATE, tbl_bios.ZIP,
tbl_bios.GRADE, tbl_bios.PARENT1, tbl_bios.PHONE1, [ADDRESS] & ", " & [CITY]
& ", " & [STATE] & " " & [ZIP] AS FnlAddress, [PARENT1] & ", " & [PHONE1] AS
FullContact, tbl_bios.COHORT, qry_finalGPA.AvgOfMark, qry_finalGPA.MarktoGPA,
tbl_bios.SPEDSTS, tbl_bios.ETHNICITY, tbl_bios.LOWEST_THIRD_CITYWIDE,
tbl_bios.LOWEST_THIRD_ELA_SCHOOL, tbl_bios.LOWEST_THIRD_MATH_SCHOOL,
tbl_allstudentsallgrades_hsst.School
FROM tbl_bios INNER JOIN ((qry_finalGPA INNER JOIN
tbl_allstudentsallgrades_hsst ON
qry_finalGPA.StudentID=tbl_allstudentsallgrades_hsst.StudentID) INNER JOIN
(tbl_coursecodes_departments INNER JOIN tbl_department_requiredcredits ON
tbl_coursecodes_departments.Department=tbl_department_requiredcredits.DepartmentList)
ON
tbl_allstudentsallgrades_hsst.Course=tbl_coursecodes_departments.CourseCodes)
ON tbl_bios.OSIS=tbl_allstudentsallgrades_hsst.StudentID
GROUP BY tbl_bios.ACTIVE, tbl_allstudentsallgrades_hsst.StudentID,
tbl_allstudentsallgrades_hsst.LastName,
tbl_allstudentsallgrades_hsst.FirstName, tbl_allstudentsallgrades_hsst.Year,
tbl_allstudentsallgrades_hsst.Term, tbl_allstudentsallgrades_hsst.Course,
DLookUp("Department","tbl_CourseCodes_departments","CourseCodes='" & [Course]
& "'"), tbl_department_requiredcredits.Required_Credits,
tbl_allstudentsallgrades_hsst.Title, tbl_allstudentsallgrades_hsst.Mark,
tbl_allstudentsallgrades_hsst.Credits, ([Mark]-55)/10, [FirstName] & " " &
[LastName], tbl_bios.ADVISOR, tbl_bios.DOB, tbl_bios.SEX, tbl_bios.ADDRESS,
tbl_bios.CITY, tbl_bios.STATE, tbl_bios.ZIP, tbl_bios.GRADE,
tbl_bios.PARENT1, tbl_bios.PHONE1, [ADDRESS] & ", " & [CITY] & ", " & [STATE]
& " " & [ZIP], [PARENT1] & ", " & [PHONE1], tbl_bios.COHORT,
qry_finalGPA.AvgOfMark, qry_finalGPA.MarktoGPA, tbl_bios.SPEDSTS,
tbl_bios.ETHNICITY, tbl_bios.LOWEST_THIRD_CITYWIDE,
tbl_bios.LOWEST_THIRD_ELA_SCHOOL, tbl_bios.LOWEST_THIRD_MATH_SCHOOL,
tbl_allstudentsallgrades_hsst.School
HAVING (((tbl_allstudentsallgrades_hsst.LastName) Like [Enter a Last Name] &
"*") AND ((tbl_bios.ADVISOR) Like [What Advisor Are You Looking For?] & "*")
AND ((tbl_bios.GRADE) Like [What Grade Are You Looking For?] & "*") AND
((tbl_bios.COHORT) Like [What Cohort Are You Looking For?] & "*"));


The DetermineResult[Mark] is the module that changes the numerical values
from 0-100 to the appropriate 4.0 scale. The code for that is:

Option Compare Database

Option Explicit

Public Function DetermineResult(Mark As Variant) As Variant
On Error Resume Next

If Len(Mark & "") > 0 Then

Select Case Mark
Case "P", "F"
DetermineResult = Mark
Case "0"
DetermineResult = 0.5
Case IsNumeric(Mark) = True
Mark = CCur(Mark)
Select Case Mark
Case 0 To 64
DetermineResult = 0.5
Case 65 To 95
DetermineResult = Format(([Mark] - 55) / 10, "0.0")
Case 96 To 100
DetermineResult = Format(4, "0.0")
Case Else
DetermineResult = "Out of range."
End Select
Case Else
DetermineResult = "Out of range."
End Select
End If

End Function

I'm having a few problems, all depending on the approach to solving the
problem. It seems to me that one solution would be to import a purely text
file so that all the non-numerical values show up. (If the Mark field is set
to number, the non-numerical values do not appear). However, this creates
the problem that now the calculations do not work on the numerical values
(because they are designated as text). So I suppose the question is, how can
I make the query only deal with the numerical items form the table and treat
them as such, even though on the table they are demarcated as text. (note:
the module will not work if the mark field is set to number).

Thanks again.



--
--vanguardaccessman


:

Please post the SQL of your query, and the code for the custom function
(which is what I assume you mean when you say "module")you are using.
Without this, no-one will be able to help effectively.

Rob

Thanks for your reply. I think that I did a poor job of thinking through
the
issue and explaining, as the more I look at my database the bigger the
problem I think this is...

First, I have a table with the following fields:
[ID]---[Mark]
[Mark] needs to be a text field because it can either be a number (from
1-100) or letter (P or F). It is being imported from an outside excel
spreadsheet (which needs to happen regularly), and if it is numerical the
table excludes the P's or F's.

The query that I'm running refers to a module. It turns the mark column
into a different scale (from 0-64 to 0,5, from 65-95 it applies the
formula
([mark]-55)/10, from 96-100 to 4.0, and P's and F's stay as are).

I see that I'm going to have to redesign elements of my database, which is
okay. But what I need to be able to do is calculate on the text field in
both queries and reports. Is that possible? I would like it to ignore
the
P's and F's, treat the numbers (from 0.5 to 4.0) that result from the
module
as numbers, then calculate averages, for example.

Any help would be greatly appreciated.


--
--vanguardaccessman


:

Hi

This will not cause any problems

SELECT TableName.ID, TableName.Mark
FROM TableName;

Do you have anything else in the query that could make the error



--
Wayne
Manchester, England.



:

I have a table with the following fields:

[ID]---[Mark]

The [Mark] field is set as text, and can have values that are numerical
(from 0-100) or text (P or F).

I have a query that is built of this table with the same two fields.
However, when I run it, the following error occurs:

"The expression is typed incorrectly, or is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Any idea of what is wrong?