Error 3464 on a query execution 1

[zimmer9]

In the QBE grid, I defined the following field:
Field: Check/Cert:Format(nz([SharesAmt],[CashBal],'#.000')
In the criteria for this field I defined the following:
> tblState.Aggreg

The idea was place into the Check/Cert field, the contents of either the field SharesAmt or CashBal. Either the ShareAmt field has a value or the CashBal field has a value depending on the type of record. The user wants to see the contents of these 2 fields in 1 field on a report.

The problem is that when I run the query, I get the message: Data type mismatch in criteria expression
Error 3464. Criteria expression in find method is attempting to compare a field with a value whose data type doesn't match the field's data type.

The resulting query displays only 1 record. All the fields in this 1 record contain the value "#Name?".

If I remove the criteria expression: > tblState.Aggreg
then the query runs fine. However, without this criteria I don't the desired result. I thought maybe my problem was the quotation marks around the format field '#.000' should be double quotes instead of single quotes. That makes no difference and furthermore the query runs fine with the
field as I defined it as long as I don't include the criteria statement.

The data type of the 3 fields in this field comparison are as follows:

1) SharesAmt
Data type: Number
Field size: Decimal
Format: Fixed
Precision: 18
Scale: 5
Decimal Places: 5

2) CashBal
Data type: Number
Field size: Decimal
Format: Fixed
Precision: 18
Scale: 3
Decimal Places: 3

3) Aggreg
Data type: Number
Field size: Decimal
Format: Fixed
Precision: 18
Scale: 2
Decimal Places: 2

Any thoughts on how I can resolve this error message ?

 

[PHV]

And this ?
Field: Check/Cert:CDec(Format(nz([SharesAmt],[CashBal],'#.000'))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer1]

It just looks like a sytax error to me. You left out a ")". Try:

Field: Check/Cert:Format(nz([SharesAmt],[CashBal]),"#.000")

rather than

Field: Check/Cert:Format(nz([SharesAmt],[CashBal],'#.000')

 

[traingamer1]

It just looks like a syntax error to me. You left out a ")". Try:

Field: Check/Cert:Format(nz([SharesAmt],[CashBal]),"#.000")

rather than

Field: Check/Cert:Format(nz([SharesAmt],[CashBal],'#.000')

 

[zimmer9]

Actually what caused my error was the data I was executing the query on. There were null values in both fields
CashBalance and ShareAmount in a couple of records.

How could I modify the Field: statement below with an IF condition to ensure that if both of these fields have null values, that I won't make a mathematical comparison to see if the Field value: >[tblStatesAll].[AggregFS] ? The presence of a null value in both these fields caused a Data Type mismatch when I made the greater than comparison.

Field:Check/Cert Amount: Format(nz([CashBalance],[SharesAmount]),"#.000")

Criteria: >[tblStatesAll].[AggregFS] (a number field)

 

[PHV]

And this ?
Field:Check/Cert Amount: Format(Nz([CashBalance],Nz([SharesAmount],0)),"#.000")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[zimmer9]

I get the messge "Wrong number of arguments used with function in query expression"

Format(Nz([CashBalance],Nz([SharesAmount],0),"#.000"))

 

[PHV]

Oops, sorry for the typo:
Format(Nz([CashBalance],Nz([SharesAmount],0)),"#.000")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244