Insert fields based on case statement

[vavs]

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

 

[loopa1]

Access queries...

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

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

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

 

[vavs]

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]

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.

 

[vavs]

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]

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:

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