Formatting a field based on an If criteria 1

[TammyT]

You helped me so much on my "easier" question; let's give you a slightly tougher (& longer - sorry) one!

I have a "little" Db with some personnel info for when we reset passwords in Novell (it's used to verify SSNs). The data fields consist of (properly named!) first name, last name, middle initial, ssn, phone, ext, & county.

I have the main form set for the user to type in the first & last name of the person, then click on Search. The Search button opens a report with that person's info. Here's what I want to add: a field called Novell Logon ID that formats their name according to the standard for the District the
person is in. Unfortunately, there is not a standard for the state, so I'm guessing it'll take a bunch of If-Then statements? I have the list of which counties are in which district, so I can format the ID based on the county
field.

There are several Districts that use the same format, so I'm thinking there must be something akin to If-Or-Or-Then?

Here's an example in "English":

If the County = 02 then the field called NovellLogonID = lastname-firstname all in lower case letters.

There are a few Districts that have several variations within them, so I'll just want to return a text message in the ID field that says something about there not being a standard for that District.

Does this make sense?! VBA is not my strenght - it's been "learn as you go" - no formal training in the codes, but I do understand the concepts.

Thanks again!

 

[LonnieJohnson]

I would put a field on the report called NovellLogonID.

Then I would put code in the report detail's On Format event procedure that used a select case statement to evaluate the county like so...

Select Case County

Case Is = "xx"
NovellLogonID = UCase(lname-fname)
Case Is = "yy"
NovellLogonID = LCase(lname-fname)
Case Else
NovellLogonID = "No Format"

End Select

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!

 

[TammyT]

Here's what the code is actually reading (& it's not working!):

Private Sub Report_Open(Cancel As Integer)

Dim fldNovellLogon As String
Dim fldFirstName As String
Dim fldLastName As String
Dim fldMI As String
Dim County As String

Select Case County

Case Is = "02" Or "05" Or "08" Or "10" Or "11" Or "14" Or "16" Or "22" Or "26" _
Or "29" Or "36" Or "41" Or "43" Or "45" Or "47" Or "48" Or "49" Or "50" Or "51" _
Or "52" Or "56" Or "57" Or "58" Or "59"
fldNovellLogon = LCase(LastName - FirstName)
Case Is = "yy"
NovellLogonID = LCase(lname - fname)
Case Else
NovellLogonID = "No Format"

End Select

End Sub

(I haven't gotten past the first part of the select stmt; I was testing it).

Also: how do I get the spaces out of the portion that reads LCase(LastName - FirstName) ? the format is LastName-FirstName (all in lower case).

Thanks!

 

[dhookom]

Try change your Select Case:
Select Case County
Case "02","05","08","10","11","14","16","22","26", _
"29","36","41","43","45","47","48","49","50","51", _
"52","56","57","58","59"
fldNovellLogon = LCase(LastName - FirstName)
Case "yy"
NovellLogonID = LCase(lname - fname)
Case Else
NovellLogonID = "No Format"
End Select

I hate hard-coding stuff like this. I would store something in a country lookup table to make this more data driven.

Duane
MS Access MVP

 

[TammyT]

Nope - it's still not working; still no data in the field.

Tell me about your county lookup table idea??

 

[dhookom]

I would create a table with each County as a field and include a field that described the formatting. Then add this table to your query and join the County code fields. You would then not hard code your counties.

Looking back at your code,
fldNovellLogon = LCase(LastName - FirstName)
should be
fldNovellLogon = LCase(LastName & FirstName)


Duane
MS Access MVP

 

[LonnieJohnson]

You may try removing the quoates from around the numbers if county is a numeric field.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!