Migration to Postgres

[Hank]

As a background task, I am migrating our back end from Access 2000
to PostgreSQL. It is important to me that the front end code runs on
both Access and PostgreSQL to ease the pain on the day we finally
switch over (And back and over and back)
Every language I have ever used has accepted the Implicit use of
Booleans in an If statement:
If ( rsEmployee("Active")) then

Postgres on the other hand seems to require the Explicit use:
If ( rsEmployee("Active") = True ) then

I realize this may be a Postgres question but because I know many
on the user group have made this transition, it seemed appropriate to
post it here. If you have any ideas, please comment.
Thanks,
Hank Reed

[Larry Linson]

I think most here who have used PostgreSQL have used it strictly as a server
database. I haven't used it at all, but had no idea that it had a front-end
UI capability, too.

But, just for the record, there are certain instances in Access where it is
either necessary or better to use the explicit form:

If SomeBoolVar = True Then

rather than

If SomeBoolVar Then

Larry Linson
Microsoft Access MVP

"Hank" <ha********@aol.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...

As a background task, I am migrating our back end from Access 2000
to PostgreSQL. It is important to me that the front end code runs on
both Access and PostgreSQL to ease the pain on the day we finally
switch over (And back and over and back)
Every language I have ever used has accepted the Implicit use of
Booleans in an If statement:
If ( rsEmployee("Active")) then

Postgres on the other hand seems to require the Explicit use:
If ( rsEmployee("Active") = True ) then

I realize this may be a Postgres question but because I know many
on the user group have made this transition, it seemed appropriate to
post it here. If you have any ideas, please comment.
Thanks,
Hank Reed

[Hank]

Larry,

Thanks for the tip, I didn't mean that Postgres had a front
end but I do refer to table data from Access VBA. I have had a long
carreer spaning many languages and I always wrote and taught that IF
statements evaluated to True or False.

If ( 2 > 1 ) then True
If ( Age < 50 ) then Sadly False
If ( BoolVal ) then To me is already True or False

In all my programming, I have sought to be explicit in every
way, to make functions understandable down the road, and very verbose
in commenting, so I will no longer take the implicit shortcut.
Thanks again,
Hank

[Albert D. Kallal]

The problem is that some sql systems return a DIFFERENT TEXT value for true!

So, if you hit ctrl-g to jump to the debug window, you can test this:

? "true" = true
True
? -1 = true
True

Note how above I have a text string called "true" and I compare it to the
built in Boolean value. The fact of the matter, is that a string of "true"
is NOT equal to the Boolean value of true, but the above still returns a
true value.

? cint(true)
-1

So, what happens is the system "casts" the true variable into a string for
the compare BEFORE the condition.

? cstr(true)
True

So, for "true" = true, we get:

"true" = "true"

The two strings are equal, and the expression resolves to a Boolean

Note that if you convert a number in ms-access to Boolean, then , any NON
zero number is considered true

if 5 then
true!!!

if -5 then
true!!!

So, what ms-access is doing in the above, is CONVERTING the expression to a
Boolean.

eg:
if 2 then
true!!!

but-
? (2 = true)
False

? (cbool(2) = True)
True

? (-1 = true)
true

If "true" Then
true

If "test" then
execution error!!...that string can't resolve to true/false!!

So, check what the server database returns for true, and false (and, for
some sql systems, you can CHANGE what the value returned is).

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Larry Linson]

There are differing opinions as to whether that is a "defect" or a "feature"
of Access. If you are feeling lucky, call it in to Microsoft Tech Support --
if they agree with you that it is a defect, they won't charge you for the
call. As for me, I'm perfectly happy to know a workaround for what I
consider just a "quirk".

There are, as Albert pointed out, differences in the way different databases
represent Yes/No or True/False values. Jet, the default database that
installs with Access, uses the integer value -1. Microsoft SQL Server uses
the value 1. I don't know what physical value PostgreSQL uses. It's not
surprising to me, under the circumstances, that VBA may not translate
exactly.

Larry Linson
Microsoft Access MVP

"Hank" <ha********@aol.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Larry,

Thanks for the tip, I didn't mean that Postgres had a front
end but I do refer to table data from Access VBA. I have had a long
carreer spaning many languages and I always wrote and taught that IF
statements evaluated to True or False.

If ( 2 > 1 ) then True
If ( Age < 50 ) then Sadly False
If ( BoolVal ) then To me is already True or False

In all my programming, I have sought to be explicit in every
way, to make functions understandable down the road, and very verbose
in commenting, so I will no longer take the implicit shortcut.
Thanks again,
Hank

[David W. Fenton]

"Larry Linson" <bo*****@localhost.not> wrote in
news:w%OQe.21927$yv2.13247@trnddc04:

But, just for the record, there are certain instances in Access
where it is either necessary or better to use the explicit form:

If SomeBoolVar = True Then

rather than

If SomeBoolVar Then

I don't know of any.

Boolean tests on controls are completely different than Boolean
tests on variables. With variables, you don't need an explicit
comparison at all, ever, though you may choose to do so for code
clarity.

So far as I know, it's only the checking of controls that requires
either explicit comparison, or parantheses to force evaluation. In
other words:

If Me!chkChecktBox = True THen

OR

If (Me!chkChecktBox) THen

But other than controls, I know of no pitfalls with Boolean tests.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Albert D. Kallal" <ka****@msn.com> wrote in
news:GyYQe.337721$s54.316290@pd7tw2no:

So, check what the server database returns for true, and false
(and, for some sql systems, you can CHANGE what the value returned
is).

This is surely the explanation. When you compare explicitly to True,
then VBA uses the definition of True that is the real one: True is
NOT FALSE. THat is, evern non-zero value is True.

So, if PostgreSQL uses 1 for True and your ODBC driver doesn't
transparently handle this, you're asking:

If 1 Then

And 1 doesn't evaluate as True in that context.

But if you do:

If 1 = True Then

you get the correct example, because 1 evaluates as not being false.

If you instead test:

If Not (Not (rsEmployee("Active"))) then

you should get the correct result, because everyone uses 0 for
False.

If the value is True, it would effectively be:

If Not (Not (1)) then

Not 1 should be False, then when you negate that, you get True in
VBA (instead of PostgreSQL's value for True).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On 29 Aug 2005 05:09:13 -0700, "Hank" <ha********@aol.com> wrote:

As a background task, I am migrating our back end from Access 2000
to PostgreSQL. It is important to me that the front end code runs on
both Access and PostgreSQL to ease the pain on the day we finally
switch over (And back and over and back)
Every language I have ever used has accepted the Implicit use of
Booleans in an If statement:
If ( rsEmployee("Active")) then

Postgres on the other hand seems to require the Explicit use:
If ( rsEmployee("Active") = True ) then

I realize this may be a Postgres question but because I know many
on the user group have made this transition, it seemed appropriate to
post it here. If you have any ideas, please comment.
Thanks,
Hank Reed

There is a PostgreSQL driver setting called Bools As Char that is turned on by
default. You probably need to turn it off. If you don't you can never bind a
booleand field to a check box.