Using IFF statement in Access

[Bob]

Currently I am using this statement to translate 3 fields in my db
thru Visual Basic. I import the data from one table to another then
call the IFF statements and the NewDate to translate the fields. Can
this be done in an Access db? I am trying to learn how to do these
things in Access vs Visual

Dim strIIF As String
Dim sstrIIF As String

Dim strNewDate As Date
Dim strDate As String

strDate = "20030207153015"

strDate = Mid$(strDate, 5, 2) & "/" & Mid$(strDate, 7, 2) & "/" &
Left$(strDate, 4)

strNewDate = DateValue(strDate)

strIIF = "IIF(calShipment.m_serviceType=2,'Next Day Air',
IIF(calShipment.m_serviceType=19,'Next Day Air
AM',IIF(calShipment.m_serviceType=13,'Next Day Air
Saver',IIF(calShipment.m_serviceType=59,'2nd Day Air
AM',IIF(calShipment.m_serviceType=3,'2nd Day
Air',IIF(calShipment.m_serviceType=12,'3 Day
Select',IIF(calShipment.m_serviceType=17,'Worldwid e
Expedited',IIF(calShipment.m_serviceType=8,'Worldw ide
Express',IIF(calShipment.m_serviceType=7,'Standard ',IIF(calShipment.m_serviceType=4,'Ground')))))))) ))"

sstrIIF = "IIF(calPackage.m_isPkgVoid=0,'Sent',
IIF(calPackage.m_isPkgVoid=1,'Voided'))"

Thanks
Bob

[Larry Linson]

What you've shown is how you build the IIF statements, not how you use them,
and I don't understand what you mean by "call the IFF statements and the
NewDate to translate the fields".

Fact is, all that you've shown can be done in VBA (it is, after all, not
only the language of Access and other office applications, but the core
language of classic VB, as well). So chances are, if you wish, you can
likely do it exactly the same.

However, if you'd clarify just what it is that you have and what you want to
accomplish, rather than code details of only part of the way you have been
doing it, someone here might be able to suggest a simpler, easier way to do
what you want. For example, with the long, nested IIF, and the other for
that matter, are you creating a new Field value, or are you updating one
that already exists?

I would imagine that running an update query can accomplish what you need to
do, and if you've been reading the table record by record with either ADO or
DAO, and updating each, then that almost certainly will be both easier and
more efficient. In fact, an Access/Jet query can call a user-defined Access
function in the Access application that issues it, and that might be a good
deal easier to read than a nested IIF.

Larry Linson
Microsoft Access MVP

"Bob" <rt********@comcast.net> wrote in message
news:93********************************@4ax.com...

Currently I am using this statement to translate 3 fields in my db
thru Visual Basic. I import the data from one table to another then
call the IFF statements and the NewDate to translate the fields. Can
this be done in an Access db? I am trying to learn how to do these
things in Access vs Visual

Dim strIIF As String
Dim sstrIIF As String

Dim strNewDate As Date
Dim strDate As String

strDate = "20030207153015"

strDate = Mid$(strDate, 5, 2) & "/" & Mid$(strDate, 7, 2) & "/" &
Left$(strDate, 4)

strNewDate = DateValue(strDate)

strIIF = "IIF(calShipment.m_serviceType=2,'Next Day Air',
IIF(calShipment.m_serviceType=19,'Next Day Air
AM',IIF(calShipment.m_serviceType=13,'Next Day Air
Saver',IIF(calShipment.m_serviceType=59,'2nd Day Air
AM',IIF(calShipment.m_serviceType=3,'2nd Day
Air',IIF(calShipment.m_serviceType=12,'3 Day
Select',IIF(calShipment.m_serviceType=17,'Worldwid e
Expedited',IIF(calShipment.m_serviceType=8,'Worldw ide
Express',IIF(calShipment.m_serviceType=7,'Standard ',IIF(calShipment.m_servic
eType=4,'Ground'))))))))))"
sstrIIF = "IIF(calPackage.m_isPkgVoid=0,'Sent',
IIF(calPackage.m_isPkgVoid=1,'Voided'))"

Thanks
Bob

[Bob]

I am still trying to learn how to use Access for other than storing
the data

What I am doing in Visual is

I have one db that I am using a query to pull data from 3 tables based
on the primary and foreign keys. In the db 2 of the fields in the
calShipment table and 1 in the calPackage table are the ones I am
addressing.

calShipment m_shipDateTime field stores the date/time as a string
20030923102015 or yyyymmddhhmmss
calShipment m_serviceType field stores the data as a number
2 = Next Day Air and so forth
calPackage m_isPkgVoid field stores the data as a number
1 = Voided and 0 = Sent

In my sql statement to pull the data into a temp table and use the
date statement, and the strIFF and sstrIIF to translate the other
fields

sql = "SELECT calPkgAgent.Sm_customerId, calPkgAgent.Sm_businessName,
calPkgAgent.m_Attention, calPkgAgent.m_StreetAddress,
calPkgAgent.m_RoomFloor, calPkgAgent.m_Department, calPkgAgent.m_City,
calPkgAgent.m_StateProv, calPkgAgent.m_PostalCode, " & strIIF & ",
DateValue(Mid$(calShipment.m_shipDateTime, 5, 2) & '/' &Mid$(
calShipment.m_shipDateTime,7, 2) & '/' & Left$(
calShipment.m_shipDateTime,4)) As NewDate, (calPackage.m_pkgCost/100)
as Expr1, (calPackage.m_weight/10) as Expr2, calPackage.Sm_trackingNo,
calPackage.Sm_referenceText0, calPackage.Sm_referenceText1,
calShipment.m_shipDataSet, " & sstrIIF & " INTO Table1 IN
'C:\DataBackUp\Temp.mdb' FROM calPkgAgent, calPackage, calShipment
WHERE calPkgAgent.m_primaryKey = calShipment.m_foreignKey00 AND
calPackage.m_foreignKey = calShipment.m_primaryKey"

the NewDate gives me 09/23/2003
the strIFF gives me the correct service
the sstrIFF gives me the correct Void or Sent

I hope you can understand what I am trying to do. I am very new to
access and most of the books don't really show me things of thios
nature.

Thanks
Bob
On Wed, 24 Sep 2003 01:28:48 GMT, "Larry Linson"
<bo*****@localhost.net> wrote:

What you've shown is how you build the IIF statements, not how you use them,
and I don't understand what you mean by "call the IFF statements and the
NewDate to translate the fields".

Fact is, all that you've shown can be done in VBA (it is, after all, not
only the language of Access and other office applications, but the core
language of classic VB, as well). So chances are, if you wish, you can
likely do it exactly the same.

However, if you'd clarify just what it is that you have and what you want to
accomplish, rather than code details of only part of the way you have been
doing it, someone here might be able to suggest a simpler, easier way to do
what you want. For example, with the long, nested IIF, and the other for
that matter, are you creating a new Field value, or are you updating one
that already exists?

I would imagine that running an update query can accomplish what you need to
do, and if you've been reading the table record by record with either ADO or
DAO, and updating each, then that almost certainly will be both easier and
more efficient. In fact, an Access/Jet query can call a user-defined Access
function in the Access application that issues it, and that might be a good
deal easier to read than a nested IIF.

Larry Linson
Microsoft Access MVP

"Bob" <rt********@comcast.net> wrote in message
news:93********************************@4ax.com.. .

Currently I am using this statement to translate 3 fields in my db
thru Visual Basic. I import the data from one table to another then
call the IFF statements and the NewDate to translate the fields. Can
this be done in an Access db? I am trying to learn how to do these
things in Access vs Visual

Dim strIIF As String
Dim sstrIIF As String

Dim strNewDate As Date
Dim strDate As String

strDate = "20030207153015"

strDate = Mid$(strDate, 5, 2) & "/" & Mid$(strDate, 7, 2) & "/" &
Left$(strDate, 4)

strNewDate = DateValue(strDate)

strIIF = "IIF(calShipment.m_serviceType=2,'Next Day Air',
IIF(calShipment.m_serviceType=19,'Next Day Air
AM',IIF(calShipment.m_serviceType=13,'Next Day Air
Saver',IIF(calShipment.m_serviceType=59,'2nd Day Air
AM',IIF(calShipment.m_serviceType=3,'2nd Day
Air',IIF(calShipment.m_serviceType=12,'3 Day
Select',IIF(calShipment.m_serviceType=17,'Worldwid e
Expedited',IIF(calShipment.m_serviceType=8,'Worldw ide

Express',IIF(calShipment.m_serviceType=7,'Standar d',IIF(calShipment.m_servic
eType=4,'Ground'))))))))))"

sstrIIF = "IIF(calPackage.m_isPkgVoid=0,'Sent',
IIF(calPackage.m_isPkgVoid=1,'Voided'))"

Thanks
Bob

[Larry Linson]

I hope that you haven't felt it necessary or desirable to start from scratch
writing SQL in Access. Access gives you the Query Builder which is an
excellent, unpaid SQL-writing Assistant. I'll just assume that you're using
the SQL as a communication vehicle, but that you saved yourself a great deal
of time and effort by using File | Get External Data to link the tables in
the other database. If not, take a look at it, and I think you'll soon
"learn to love it" -- that is, unless you are an SQL hobbyist. <G>

I believe I'd create myself a local table with the numeric values and text
for the service type, and join those to the appropriate fields in
calShipment and calPackage in a MakeTable or Update Query, and do the same
for m_isPkgVoid field. Then again, I might just leave those fields numeric,
and do the join only when I needed the text values for display in a Form or
a Report.

(For a MakeTable or Update Query, create the Query as a Select, and in
Design View in the Query Builder, on the menu Query | Make Table or Update.
However, see below: you may not need to create a temporary table.)

Your expression for manipulating the date looks good to me, but this, too,
can be accomplished without any temporary or intermediate table, in the
Query that you use as RecordSource for the Form or Report where the data is
viewed and/or used.

On the other hand, if the tables from which you are extracting the
information are transitory in nature, and may not be around when you need
the information, bringing that information in to a local table (not
necessarily temporary) would obviously be a good idea. If you maintain past
data in your local table(s), you may be able to get "extra value" by
analyzing it at some future time.

A Service Type table would consist of two fields (or more, if you think you
may need additional description or information): the service type number,
and service type name. You'd join it to the calShipment.m_serviceType field
on service type number, but pull the service type name down into the Query
Builder Grid. The text for m_isPkgVoid could be handled the same way, or
created as a Calculated Field in the Query using the IIf statement you
cited.

If I haven't been clear enough, follow up and I'll try to clarify.

Larry Linson
Microsoft Access MVP

[Reggie]

Have you tried a Select Case statement:

Dim intShipmentType as Integer
Dim intPackageVoid as Integer
intShipmentType = calShipment.m_serviceType
intPackageVoid = calPackage.m_isPkgVoid

Selct Case intShipmentType
Case 2
strIIF = "Next Day Air"
Case 19
strIIF = "Next Day Air AM"
Case Else
strIIF = "whatever"
End Select

Selct Case intPackageVoid
Case 0
sstrIIF = "Sent"
Case 1
sstrIIF = "Voided"
Enb Select

Reggie

"Half this game is 90% mental."

----------
"Larry Linson" <bo*****@localhost.net> wrote in message
news:ty**************@nwrddc02.gnilink.net...

I hope that you haven't felt it necessary or desirable to start from scratch writing SQL in Access. Access gives you the Query Builder which is an
excellent, unpaid SQL-writing Assistant. I'll just assume that you're using the SQL as a communication vehicle, but that you saved yourself a great deal of time and effort by using File | Get External Data to link the tables in
the other database. If not, take a look at it, and I think you'll soon
"learn to love it" -- that is, unless you are an SQL hobbyist. <G>

I believe I'd create myself a local table with the numeric values and text
for the service type, and join those to the appropriate fields in
calShipment and calPackage in a MakeTable or Update Query, and do the same
for m_isPkgVoid field. Then again, I might just leave those fields numeric, and do the join only when I needed the text values for display in a Form or a Report.

(For a MakeTable or Update Query, create the Query as a Select, and in
Design View in the Query Builder, on the menu Query | Make Table or Update. However, see below: you may not need to create a temporary table.)

Your expression for manipulating the date looks good to me, but this, too,
can be accomplished without any temporary or intermediate table, in the
Query that you use as RecordSource for the Form or Report where the data is viewed and/or used.

On the other hand, if the tables from which you are extracting the
information are transitory in nature, and may not be around when you need
the information, bringing that information in to a local table (not
necessarily temporary) would obviously be a good idea. If you maintain past data in your local table(s), you may be able to get "extra value" by
analyzing it at some future time.

A Service Type table would consist of two fields (or more, if you think you may need additional description or information): the service type number,
and service type name. You'd join it to the calShipment.m_serviceType field on service type number, but pull the service type name down into the Query
Builder Grid. The text for m_isPkgVoid could be handled the same way, or
created as a Calculated Field in the Query using the IIf statement you
cited.

If I haven't been clear enough, follow up and I'll try to clarify.

Larry Linson
Microsoft Access MVP

[John Winterbottom]

"Bob" <rt********@comcast.net> wrote in message
news:eg********************************@4ax.com...

I am still trying to learn how to use Access for other than storing
the data

What I am doing in Visual is

I have one db that I am using a query to pull data from 3 tables based
on the primary and foreign keys. In the db 2 of the fields in the
calShipment table and 1 in the calPackage table are the ones I am
addressing.

The Switch() function might be what you need.

[Bob]

Where would I use the Select Case in Access?

On Wed, 24 Sep 2003 01:46:45 -0600, "Reggie"
<newsgroups@no_spam.comcast.net> wrote:

Have you tried a Select Case statement:

Dim intShipmentType as Integer
Dim intPackageVoid as Integer
intShipmentType = calShipment.m_serviceType
intPackageVoid = calPackage.m_isPkgVoid

Selct Case intShipmentType
Case 2
strIIF = "Next Day Air"
Case 19
strIIF = "Next Day Air AM"
Case Else
strIIF = "whatever"
End Select

Selct Case intPackageVoid
Case 0
sstrIIF = "Sent"
Case 1
sstrIIF = "Voided"
Enb Select

Reggie

"Half this game is 90% mental."

----------
"Larry Linson" <bo*****@localhost.net> wrote in message
news:ty**************@nwrddc02.gnilink.net...

I hope that you haven't felt it necessary or desirable to start from

scratch

writing SQL in Access. Access gives you the Query Builder which is an
excellent, unpaid SQL-writing Assistant. I'll just assume that you're

using

the SQL as a communication vehicle, but that you saved yourself a great

deal

of time and effort by using File | Get External Data to link the tables in
the other database. If not, take a look at it, and I think you'll soon
"learn to love it" -- that is, unless you are an SQL hobbyist. <G>

I believe I'd create myself a local table with the numeric values and text
for the service type, and join those to the appropriate fields in
calShipment and calPackage in a MakeTable or Update Query, and do the same
for m_isPkgVoid field. Then again, I might just leave those fields

numeric,

and do the join only when I needed the text values for display in a Form

or

a Report.

(For a MakeTable or Update Query, create the Query as a Select, and in
Design View in the Query Builder, on the menu Query | Make Table or

Update.

However, see below: you may not need to create a temporary table.)

Your expression for manipulating the date looks good to me, but this, too,
can be accomplished without any temporary or intermediate table, in the
Query that you use as RecordSource for the Form or Report where the data

is

viewed and/or used.

On the other hand, if the tables from which you are extracting the
information are transitory in nature, and may not be around when you need
the information, bringing that information in to a local table (not
necessarily temporary) would obviously be a good idea. If you maintain

past

data in your local table(s), you may be able to get "extra value" by
analyzing it at some future time.

A Service Type table would consist of two fields (or more, if you think

you

may need additional description or information): the service type number,
and service type name. You'd join it to the calShipment.m_serviceType

field

on service type number, but pull the service type name down into the Query
Builder Grid. The text for m_isPkgVoid could be handled the same way, or
created as a Calculated Field in the Query using the IIf statement you
cited.

If I haven't been clear enough, follow up and I'll try to clarify.

Larry Linson
Microsoft Access MVP

[Reggie]

Bob, You can use it in place of the IIF statement you designed. I'm not
sure from your post if you are doing it in VB or Access. At any rate it
works in both. I'm not exactly sure how/when you're using it so I can't say
for sure where to place it. This was just to give you a cleaner way of
building your string. If you do as Larry suggested and pull/link the table
into access, simply design and update query. You can set the "update to"
value to the return value of a function placed in a standard module.
--
Reggie

"Half this game is 90% mental."

----------
"Bob" <rt********@comcast.net> wrote in message
news:hh********************************@4ax.com...

Where would I use the Select Case in Access?

On Wed, 24 Sep 2003 01:46:45 -0600, "Reggie"
<newsgroups@no_spam.comcast.net> wrote:

Have you tried a Select Case statement:

Dim intShipmentType as Integer
Dim intPackageVoid as Integer
intShipmentType = calShipment.m_serviceType
intPackageVoid = calPackage.m_isPkgVoid

Selct Case intShipmentType
Case 2
strIIF = "Next Day Air"
Case 19
strIIF = "Next Day Air AM"
Case Else
strIIF = "whatever"
End Select

Selct Case intPackageVoid
Case 0
sstrIIF = "Sent"
Case 1
sstrIIF = "Voided"
Enb Select

Reggie

"Half this game is 90% mental."

----------
"Larry Linson" <bo*****@localhost.net> wrote in message
news:ty**************@nwrddc02.gnilink.net...

I hope that you haven't felt it necessary or desirable to start from

scratch

writing SQL in Access. Access gives you the Query Builder which is an
excellent, unpaid SQL-writing Assistant. I'll just assume that you're

using

the SQL as a communication vehicle, but that you saved yourself a great

deal

of time and effort by using File | Get External Data to link the tables in the other database. If not, take a look at it, and I think you'll soon
"learn to love it" -- that is, unless you are an SQL hobbyist. <G>

I believe I'd create myself a local table with the numeric values and text for the service type, and join those to the appropriate fields in
calShipment and calPackage in a MakeTable or Update Query, and do the same for m_isPkgVoid field. Then again, I might just leave those fields

numeric,

and do the join only when I needed the text values for display in a Form

or

a Report.

(For a MakeTable or Update Query, create the Query as a Select, and in
Design View in the Query Builder, on the menu Query | Make Table or

Update.

However, see below: you may not need to create a temporary table.)

Your expression for manipulating the date looks good to me, but this,

too, can be accomplished without any temporary or intermediate table, in the
Query that you use as RecordSource for the Form or Report where the datais

viewed and/or used.

On the other hand, if the tables from which you are extracting the
information are transitory in nature, and may not be around when you

need the information, bringing that information in to a local table (not
necessarily temporary) would obviously be a good idea. If you maintain

past

data in your local table(s), you may be able to get "extra value" by
analyzing it at some future time.

A Service Type table would consist of two fields (or more, if you think

you

may need additional description or information): the service type number, and service type name. You'd join it to the calShipment.m_serviceType

field

on service type number, but pull the service type name down into the Query Builder Grid. The text for m_isPkgVoid could be handled the same way, or created as a Calculated Field in the Query using the IIf statement you
cited.

If I haven't been clear enough, follow up and I'll try to clarify.

Larry Linson
Microsoft Access MVP