Using If ElseIf to Evaluate - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Using If ElseIf to Evaluate

Don Gregory

9 months ago

I am totally stumped on this. I have a table into which a user can import data from a spreadsheet. Once the data is imported into the table, I want to ensure that the data is cleaned up for consistency. Here is the VBA code:
If Forms![frm_tbl_EFT_Temp]![Source] = "PAYPAL" Then
        Forms![frm_tbl_EFT_Temp]![Source] = "Paypal"
ElseIf Forms![frm_tbl_EFT_Temp]![Source] = "paypal" Then
        Forms![frm_tbl_EFT_Temp]![Source] = "Paypal"
ElseIf Forms![frm_tbl_EFT_Temp]![Source] = "nfg" Then
        Forms![frm_tbl_EFT_Temp]![Source] = "NFG"
ElseIf Forms![frm_tbl_EFT_Temp]![Source] = "Nfg" Then
        Forms![frm_tbl_EFT_Temp]![Source] = "NFG"
ElseIf Forms![frm_tbl_EFT_Temp]![Source] = "BEVITY" Then
        Forms![frm_tbl_EFT_Temp]![Source] = "Bevity"
ElseIf Forms![frm_tbl_EFT_Temp]![Source] = "bevity" Then
        Forms![frm_tbl_EFT_Temp]![Source] = "Bevity"
End If
What's happening is that only one set of data is cleaned up on the form. "Paypal" is cleaned up, but "Nfg" is not cleaned up.
I have done numerous internet searches and I even tried to use Case to get this to work. Nothing seems to fix the problem.
Do you have any suggestions as to what I am doing wrong?

Adam Schwanz @Reply

9 months ago

You are only editing one record at a time with that setup. What you need is a recordset to go over all the records. Or you can use update queries.

I don't think this is case sensitive either like some other languages. This below should update all your stuff regardless of the capitalization. (Make a backup before you try as it is updating many records)

CurrentDb.Execute "Update TableName Set Source='Paypal' Where Source='paypal'"

CurrentDb.Execute "Update TableName Set Source='NFG' Where Source='nfg'"

CurrentDb.Execute "Update TableName Set Source='Bevity' Where Source='bevity'"

Kevin Yip @Reply

9 months ago

@0[Don-Gregory] If you have "Option Compare Database" at the top of your VBA module (as is usually the case), then string comparisons are not case-sensitive -- "PAYPAL" will always equal "Paypal" or "paypal". You can use update queries as Alex suggested. If you must use VBA, then you can take advantage of the case-insensitive nature of the comparisons to simplify your code to something like:

Dim s As String
s = Forms![frm_tbl_EFT_Temp]![Source] & ""
If s = "paypal" Then s = "Paypal"
If s = "nfg" Then s = "NFG"
If s = "bevity" Then s = "Bevity"
Forms![frm_tbl_EFT_Temp]![Source] = s

No need to use ElseIf to check the various cases of the string.

	John Williams @Reply 9 months ago
Why not simply go upper case for all s=ucase(s) which will convert all entries to uppercase? Then you would not the need for the giant IF/Then.

	Don Gregory OP @Reply 9 months ago
I ended up just creating 3 data queries, much like what Greg proposed. This worked great and avoided that hideous If ElseIf. I went down this road mainly to try to accommodate the users and what they expect to see. I also wanted the data to be consistent. Thank you all for taking the time to reply.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View