SQL Functions Help

  • Thread starter Thread starter vjp2.at
  • Start date Start date
V

vjp2.at

THings like Iif and Left$ don't seem to be documented in the main help.
You have to try them one by one in BUILD. Are they VB functions? Why?

- = -
Vasos Panagiotopoulos, Columbia'81+, Reagan, Mozart, Pindus, BioStrategist
http://ourworld.compuserve.com/homepages/vjp2/vasos.htm
---{Nothing herein constitutes advice. Everything fully disclaimed.}---
[Homeland Security means private firearms not lazy obstructive guards]
[Urb sprawl confounds terror] [Remorse begets zeal] [Windows is for Bimbos]
 
THings like Iif and Left$ don't seem to be documented in the main help.
You have to try them one by one in BUILD. Are they VB functions? Why?
Click to expand...

They are VBA functions, and the Help file for them becomes available if you
open the VBA editor (you can do so by typing Ctrl-G among other ways).

John W. Vinson [MVP]
 
They are VBA functions,
Click to expand...

Actually, they are VBA scalar *expressions* :)
the Help file for them becomes available if you
open the VBA editor (you can do so by typing Ctrl-G among other ways).
Click to expand...


Not all VBA expressions run in Jet SQL e.g. because their arguments
and/or return values are not do not map to Jet types (e.g. VBA
arrays). Some expressions behave differently in SQL Jet and the Help
does not reveal this e.g. Help tells us "IIf always evaluates both
truepart and falsepart, even though it returns only one of them" but
that is not the behaviour of IIf() in Jet SQL e.g.

VBA:
? IIf(0=1, 2/0, 1/1)
"Division by zero" error because truepart is evaluated.

Jet SQL:
SELECT IIf(0=1, 2/0, 1/1)
returns 1, therefore truepart is not evaluated.

But I had to discover this, rather than reading it in the Help, so I
have some sympathy for the OP's point.

Another consideration is whether sandbox mode is enabled or the Jet
SQL is being run from outside of the Access user interface when only a
subset of VBA expressions are available (broadly the VBA5 scalar
expressions). See:

How to configure Jet 4.0 to prevent unsafe functions from running in
Access 2003
http://support.microsoft.com/kb/294698

Jamie.

--
 
Back
Top