Difference between US and UK.

[JamesDeckert]

Using Access 2003 runtime and a store in the UK gets a "This expression is
typed incorrectly, or it is too complex to be evaluated." error when opening
a report based on a query. I have 30+ stores using the same program in the US
which don't get the error. The UK store gets the same error on two different
computers using Windows XP. It is only occurring on one report and not the 9
others.

I had the UK store upload their fe and be to my website. I put it on my
computer and it ran without the error.

The UK store does not have MS Office on either computer.

The UK has a different date format (dd/mm/yy) but I don't see how this is
only affecting one report and not the others. I can't find any hard coded
date checking pertaining to this report. There is a date field included in
the query, but it doesn't appear on the report and there is no criteria set
for the field.

Any ideas what would cause this error on one computer and not another using
identical data?

thanks,
James

 

[david]

the query, but it doesn't appear on the report and there is no criteria

set

Doesn't matter what appears on the report, what matters is "sorting and
grouping"

Still, a plain date field shouldn't cause this problem. Check for
expressions
used in sorting and grouping, check data in all criteria and sort/group
columns,
check for VBA user defined functions in the queries.

(david)

 

[JamesDeckert]

David, thanks for the response.
Here are the items you suggested I check.

Sorting/Grouping within report - Nothing pertaining to the date field.

Criteria in the query is as follows (the criteria is typed into an unbound
field on a form which opens the report
IIf(IsNull([Forms]![IPR dg]![txtAcct#]),[Customer].[Acct#],[Forms]![IPR
dg]![txtAcct#])
and
IIf(IsNull([Forms]![IPR dg]![txtBatchNo]),[BatchNo],[Forms]![IPR
dg]![txtBatchNo])

User defined functions as called from the query read a value saved in a
table and pass the number to the query
ExtSrp:
IIf([Paid?]=Yes,0,[Qty]*([Srp]+Abs(([CPI].[Bag]*GetBagPrice())+([CPI].[Board]*GetBoardPrice()))))

Public Function GetBagPrice() As Currency
Static varBagPrice As Variant
If IsEmpty(varBagPrice) Then
varBagPrice = getOption("ComicBook Pull File", "Setup", "Bag Price")
End If
GetBagPrice = varBagPrice
End Function

Public Function getBoardPrice() As Currency
Static varBoardPrice As Variant
If IsEmpty(varBoardPrice) Then
varBoardPrice = getOption("ComicBook Pull File", "Setup", "Board Price")
End If
getBoardPrice = varBoardPrice
End Function

Do you see any problems?
thanks,
James

 

[Guest]

Nothing I see, although I wouldn't use # in a field name for two
reasons:

1)It has a special meaning in Access Queries (it stands in for the
"." symbol, which can't be used because it has a special meaning...)

and
2) It is a different ASCII character in England than it is in the US.

I'm not sure what the meaning of the IIF statements is when txtAcct
or txtBatchNo is not Null.

FWIW, 4 times out of ten this is data error rather than a programming
error - sometimes something insane like not setting the date correctly
on the PC, so the select queries don't work as planned.

The next 4 times it's a "references" error. "references" errors can
give you any kind of crazy error message, as well as the common
ones. I don't see anything to suggest that here other than your note
that "office is not installed". Even if it's not an obvious references
problem, compiling on the target machine makes the problem go away.

The other 20%...
UK computers may have different number formatting and currency
symbols as well as different date format, so don't think that it has
to be the date format.

I also suggest compacting the database, so that the pre-compiled
queries are blown away, but I don't expect that to make any difference.

(david)

David, thanks for the response.
Here are the items you suggested I check.

Sorting/Grouping within report - Nothing pertaining to the date field.

Criteria in the query is as follows (the criteria is typed into an unbound
field on a form which opens the report
IIf(IsNull([Forms]![IPR dg]![txtAcct#]),[Customer].[Acct#],[Forms]![IPR
dg]![txtAcct#])
and
IIf(IsNull([Forms]![IPR dg]![txtBatchNo]),[BatchNo],[Forms]![IPR
dg]![txtBatchNo])

User defined functions as called from the query read a value saved in a
table and pass the number to the query
ExtSrp:
IIf([Paid?]=Yes,0,[Qty]*([Srp]+Abs(([CPI].[Bag]*GetBagPrice())+([CPI].[Board
]*GetBoardPrice()))))

Public Function GetBagPrice() As Currency
Static varBagPrice As Variant
If IsEmpty(varBagPrice) Then
varBagPrice = getOption("ComicBook Pull File", "Setup", "Bag Price")
End If
GetBagPrice = varBagPrice
End Function

Public Function getBoardPrice() As Currency
Static varBoardPrice As Variant
If IsEmpty(varBoardPrice) Then
varBoardPrice = getOption("ComicBook Pull File", "Setup", "Board Price")
End If
getBoardPrice = varBoardPrice
End Function

Do you see any problems?
thanks,
James

Doesn't matter what appears on the report, what matters is "sorting and
grouping"

Still, a plain date field shouldn't cause this problem. Check for
expressions
used in sorting and grouping, check data in all criteria and sort/group
columns,
check for VBA user defined functions in the queries.

(david)

 

[JamesDeckert]

Thanks for all the ideas.
I finally figured it out by setting my computer to UK, and debugging the
error.
It had to do with me using a variant in a mathematical statement. Apparently
it automatically converts to currency for US but not UK. I added the cCur()
function to force the conversion and the error went away.

thanks again,
James

Nothing I see, although I wouldn't use # in a field name for two
reasons:

1)It has a special meaning in Access Queries (it stands in for the
"." symbol, which can't be used because it has a special meaning...)

and
2) It is a different ASCII character in England than it is in the US.

I'm not sure what the meaning of the IIF statements is when txtAcct
or txtBatchNo is not Null.

FWIW, 4 times out of ten this is data error rather than a programming
error - sometimes something insane like not setting the date correctly
on the PC, so the select queries don't work as planned.

The next 4 times it's a "references" error. "references" errors can
give you any kind of crazy error message, as well as the common
ones. I don't see anything to suggest that here other than your note
that "office is not installed". Even if it's not an obvious references
problem, compiling on the target machine makes the problem go away.

The other 20%...
UK computers may have different number formatting and currency
symbols as well as different date format, so don't think that it has
to be the date format.

I also suggest compacting the database, so that the pre-compiled
queries are blown away, but I don't expect that to make any difference.

(david)

David, thanks for the response.
Here are the items you suggested I check.

Sorting/Grouping within report - Nothing pertaining to the date field.

Criteria in the query is as follows (the criteria is typed into an unbound
field on a form which opens the report
IIf(IsNull([Forms]![IPR dg]![txtAcct#]),[Customer].[Acct#],[Forms]![IPR
dg]![txtAcct#])
and
IIf(IsNull([Forms]![IPR dg]![txtBatchNo]),[BatchNo],[Forms]![IPR
dg]![txtBatchNo])

User defined functions as called from the query read a value saved in a
table and pass the number to the query
ExtSrp:
IIf([Paid?]=Yes,0,[Qty]*([Srp]+Abs(([CPI].[Bag]*GetBagPrice())+([CPI].[Board
]*GetBoardPrice()))))

Public Function GetBagPrice() As Currency
Static varBagPrice As Variant
If IsEmpty(varBagPrice) Then
varBagPrice = getOption("ComicBook Pull File", "Setup", "Bag Price")
End If
GetBagPrice = varBagPrice
End Function

Public Function getBoardPrice() As Currency
Static varBoardPrice As Variant
If IsEmpty(varBoardPrice) Then
varBoardPrice = getOption("ComicBook Pull File", "Setup", "Board Price")
End If
getBoardPrice = varBoardPrice
End Function

Do you see any problems?
thanks,
James

the query, but it doesn't appear on the report and there is no criteria
set

Doesn't matter what appears on the report, what matters is "sorting and
grouping"

Still, a plain date field shouldn't cause this problem. Check for
expressions
used in sorting and grouping, check data in all criteria and sort/group
columns,
check for VBA user defined functions in the queries.

(david)


Using Access 2003 runtime and a store in the UK gets a "This expression is
typed incorrectly, or it is too complex to be evaluated." error when
opening
a report based on a query. I have 30+ stores using the same program in the
US
which don't get the error. The UK store gets the same error on two
different
computers using Windows XP. It is only occurring on one report and not the
9
others.

I had the UK store upload their fe and be to my website. I put it on my
computer and it ran without the error.

The UK store does not have MS Office on either computer.

The UK has a different date format (dd/mm/yy) but I don't see how this is
only affecting one report and not the others. I can't find any hard coded
date checking pertaining to this report. There is a date field included in
the query, but it doesn't appear on the report and there is no criteria
set
for the field.

Any ideas what would cause this error on one computer and not another
using
identical data?

thanks,
James

 

[Jamie Collins]

I wouldn't use # in a field name [because it] is a different ASCII character in England than it is in the US.

What does ASCII mean?

Jamie.

--

 

[Rob Parker]

Hi Jamie,

I'm sure you wouldn't be intentionally trolling here ;-)

And, given your pedantic history, I'm surprised you don't know that it's
ASCII means "American Standard Code for Information Interchange". It's the
commonly-used relationship between character number and character in
keystroke sets - using the terms fairly loosely, since I'm not being
pedantice here. Some "characters" - in particular, those with an ASCII code
below 32 - don't have a "physical" representation (in any given font), but
instead mean things like "carriage return" or "line feed" or ...

And, for me, the previous poster's statement that the "#" character has a
different mapping in a UK character set than in the ASCII character set is
surprising. But I haven't researched it before posting this reply ;-)

Season's Greetings,

Rob

I wouldn't use # in a field name [because it] is a different ASCII
character in England than it is in the US.

What does ASCII mean?

Jamie.

 

[Jamie Collins]

I'm sure you wouldn't be intentionally trolling here ;-)

A little OT but thanks anyway for stating your level of certainty

given your pedantic history I'm surprised you don't know that it's[sic]
ASCII means

Consider this quote from the ASCII page on Wikipedia (http://
en.wikipedia.org/wiki/Ascii):

"Almost every country needed an adapted version of ASCII since ASCII
only suited the needs of the USA and a few other countries... Although
these encodings are sometimes referred to as ASCII, true ASCII is
strictly defined only by ANSI standard."

If you think I am a pedant, why would you think I'd accept anything
other than the strict definition of ASCII?

Seasons greetings to you too.

Jamie.

--

 

[John W. Vinson]

"Almost every country needed an adapted version of ASCII since ASCII
only suited the needs of the USA and a few other countries... Although
these encodings are sometimes referred to as ASCII, true ASCII is
strictly defined only by ANSI standard."

If you think I am a pedant, why would you think I'd accept anything
other than the strict definition of ASCII?

Well... just remember: if you ASCII a silly question, you'll get a silly ANSI.

John W. Vinson [MVP]