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
Hello

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,'Worldwide
Expedited',IIF(calShipment.m_serviceType=8,'Worldwide
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

 

[Duane Hookom]

First of all, I would never concatenate date parts together. My preference
is using DateSerial().

Dim datNewDate As Date 'it's a date value so why use "str"
Dim strDate As String
strDate = "20030207153015"
datNewDate = DateSerial(Val(Left$(strDate, 4)), Val(Mid(strDate, 5,
2)),Val(Mid(strDate, 7, 2)))

It looks like you should have a lookup table for service types rather than
hard-coding the values in a huge nested IIf() expression. Also, the nested
IIf()s are much more difficult to read compared with Switch().

 

[Van T. Dinh]

I think you meant IIf (Immediate If), NOT IFF.

Yes, you can but OTOH, the SELECT CASE ... END SELECT is a
lot clearer. I am fairly sure SELECT CASE is also in VB.

HTH
Van T. Dinh
MVP (Access)

 

[Bob]

Sorry I do mean IIF I keep saying it backwards. I have tried the
select case in Visual but not in VBA. I am still learning access. I am
tying to use it for more than just storing the data.

 

[Bob]

Can a lookup table get data from another DB? I would like to create a
db than import the data from another copy and work on it. The company
gets p'd off when we add different queries or tables to it.

 

[Duane Hookom]

You can link to a table in another database. I would create a table with the
following records:

2 'Next Day Air'
19 'Next Day Air AM'
13 'Next Day Air Saver'
59 '2nd Day Air AM'
3 '2nd Day Air'
12 '3 Day Select'
17 'Worldwide Expedited'
8 'Worldwide Express'
7 'Standard'
4 'Ground'

Then you can maintain data rather than code.