Insert fields based on case statement

vavs

Well-known Member
Joined
Jul 2, 2004
Messages
514
I have three SQL views that are linked to my Access 2007 database. I need to insert three fields into either a query or another table based on a case statement. I am using Excel to accomplish this right now but it is painfully slow.

If someone can help me with the first one, I should be able to replicate the process.

In the first linked table, I have a field Gauge. The field is a text field. I need to create three new fields in the database. DecIn, DecOut and DecAvg. The case statement works like this:

Case 1
If Gauge contains "/" then give me everything left of the "/" in DecIn and change to Integer, everything right of "/" in DecOut and change to Integer and then DecIn+DecOut/2 in DecAvg.

There are several other cases. In Excel I actually create a column called GaugeType to determine which condition I was dealing with. I could do the same thing here if necessary.

I have a few other fields where I will do basically the same thing. One of the fields is going to just link to another table and cross reference the matched field.

I have found 18 different conditions that exist in the field, so the case statement is the only way I know of to accomplish the task. I am hoping that Access can process the information faster than Excel. My goal is to use the table in the same Excel spreadsheet and allow the user to see the information immediately without have to update everything within Excel.

TIA
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

loopa1

Board Regular
Joined
Sep 3, 2006
Messages
156
Access queries...

DecIN: Int(Left([Gauge],InStr([Gauge],"/")-1))

DecOut: Int(Right([Gauge],InStr([Gauge],"/")-1))

DecAvg: ([DecIn]+[DecOut])/2
 

vavs

Well-known Member
Joined
Jul 2, 2004
Messages
514
loopa1
Thanks for the reply. That works if the field is always configured with a /. My problem is the field has a /,-,MIN,NOM and about 13 other conditions. Is there a way to create a case statement to update that field if it finds the / the do those three things. If it finds a - then do three different things.
 

loopa1

Board Regular
Joined
Sep 3, 2006
Messages
156
Ah sorry, I thought it was always a "/".


If your values are always as follows:

12/34
56-78
90MIN12
etc.

ie. ALWAYS integers followed by some text string followed by integers, then I'd imagine there's a way to find the first non-integer value in a cell and the final non-integer value. But I don't know how to do that I'm afraid.

As long as you have the positions of these two characters, then you can just amend the formulas I gave you previously to return the three values you need.

Sorry I couldn't be of further help.


Edit: Unless you can somehow have those three formulas use some kind "or", that's the only other thing I can think of, but I don't know if this is possible either.
 
Last edited:

vavs

Well-known Member
Joined
Jul 2, 2004
Messages
514
I was hoping that I could do some kind of a case statement. I have used it in Excel to loop through the records.

I am combining two things in Excel. First I create the GaugeType using an if statement:

*/
Do While i < (LR + 1)

CellA = Cells(i, 3)
'CellB = Cells(i, 8)


On Error Resume Next
If InStr(1, CellA, "1/4") Then
Cells(i, 8).Value = 15
ElseIf InStr(1, CellA, "5/16") Then
Cells(i, 8).Value = 16
ElseIf InStr(1, CellA, "3/8") Then
Cells(i, 8).Value = 17
ElseIf InStr(1, CellA, "/") Then
Cells(i, 8).Value = 1
ElseIf InStr(1, CellA, "-") Then
Cells(i, 8).Value = 2
ElseIf InStr(1, CellA, "NOM") Then
Cells(i, 8).Value = 3
ElseIf InStr(1, CellA, "MIN") Then
Cells(i, 8).Value = 4
ElseIf InStr(1, CellA, "ACT") Then
Cells(i, 8).Value = 5
ElseIf InStr(1, CellA, "1/4") Then
Cells(i, 8).Value = 6
ElseIf InStr(1, CellA, "M") Then
Cells(i, 8).Value = 7
ElseIf InStr(1, CellA, "GA") Then
Cells(i, 8).Value = 8
ElseIf InStr(1, CellA, " 71") Then
Cells(i, 8).Value = 9
ElseIf InStr(1, CellA, " 0") Then
Cells(i, 8).Value = 10
ElseIf InStr(1, CellA, " 50") Then
Cells(i, 8).Value = 11
ElseIf InStr(1, CellA, "GN45A") Then
Cells(i, 8).Value = 12
ElseIf InStr(1, CellA, "CR") Then
Cells(i, 8).Value = 14
Else: Cells(i, 8).Value = 0

End If

i = i + 1
Loop
/*

Then I do a case statement to update the three columns

*/
Select Case Sorta

Case 0
CellJ.Value = CellC
CellK.Value = CellC
CellL.Value = CellC

Case 1
CellJ.Value = Left(CellC, Application.WorksheetFunction.Search("/", CellC) - 1)
CellK.Value = Mid(CellC, Application.WorksheetFunction.Search("/", CellC) + 1, 5)
CellL = ((CellJ.Value + CellK.Value) / 2)

Case 2
CellJ.Value = Left(CellC, Application.WorksheetFunction.Search("-", CellC) - 1)
CellK.Value = Mid(CellC, Application.WorksheetFunction.Search("-", CellC) + 1, 5)
CellL = ((CellJ.Value + CellK.Value) / 2)

Case 3
CellJ.Value = Left(CellC, Application.WorksheetFunction.Search("NOM", CellC) - 1)
CellK.Value = Left(CellC, Application.WorksheetFunction.Search("NOM", CellC) - 1)
CellL = ((CellJ.Value + CellK.Value) / 2)

Case 4
CellJ = Left(CellC, Application.WorksheetFunction.Search("MIN", CellC) - 1)
CellK = Left(CellC, Application.WorksheetFunction.Search("MIN", CellC) - 1)
Cells(i, 12).Value = ((CellJ + CellK) / 2)

Case 5
CellJ.Value = Left(CellC, Application.WorksheetFunction.Search("ACT", CellC) - 1)
CellK.Value = Left(CellC, Application.WorksheetFunction.Search("ACT", CellC) - 1)
CellL = ((CellJ.Value + CellK.Value) / 2)

Case 7
CellJ.Value = Left(CellC, Application.WorksheetFunction.Search("M", CellC) - 1)
CellK.Value = Left(CellC, Application.WorksheetFunction.Search("M", CellC) - 1)
CellL = ((CellJ.Value + CellK.Value) / 2)

Case 8
CellM = CellC.Value

Case 14
CellJ.Value = 0.0299
CellK.Value = 0.0299
CellL.Value = 0.0299

Case 15
CellJ.Value = 0.25
CellK.Value = 0.25
CellL.Value = 0.25

Case 16
CellJ.Value = 0.3125
CellK.Value = 0.3125
CellL.Value = 0.3125

Case 17
CellJ.Value = 0.375
CellK.Value = 0.375
CellL.Value = 0.375

Case Else
CellJ.Value = 9999
CellK.Value = 9999
CellL.Value = 9999
CellM.Value = 9999


End Select
/*

Does this make sense? I want to do something similar in Access.
 

TFCJamieFay

Active Member
Joined
Oct 3, 2007
Messages
480
Hi,

If you're looking to loop through a text string to find the first non-numeric value try using the IsNumeric function. Try something like:

Code:
Sub Temp()

    Dim strMyVariable As String

    strMyVariable = "12345J7890"

    For i = 1 To Len(strMyVariable)
        If IsNumeric(Mid(strMyVariable, i, 1)) = False Then
            MsgBox ("The first non-numeric value in strMyVariable is at postion " & i)
        End If
    Next i

End Sub

HTH,

Jay
 

Watch MrExcel Video

Forum statistics

Threads
1,122,517
Messages
5,596,620
Members
414,081
Latest member
Subaru_Steve

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top