Advice needed

[squatch513]

Good morning!

Advice needed figuring out this query.

I would like the query to pull specific data from a table field, but it may vary a tad. Here's an example:

Field - User, Date, Procedure, Adjustment.

I'm focusing on the adjustment field. Values range from, for example "AB1" to "A1" and even "AB1, CD2, F4"



In this example, I just want the number. so, I'd want it to display in the query as "1", "1", "1" respectively. Note, each of these adjustment field values would be on separate rows. Make sense?

I can't seem to get the criteria/code right. I either get an error or pull nothing.

If there is a different way to go about this I am all ears!

Thanks,

Jeremy


EDIT - added excel example.

So, in this case, I want my query to return the numbers from the first, if there are more than one, Adjustment. So in this case, in order, it would pull 1,109,1,11

User	Date	Procedure	Adjustment
John	4/1/2022	99214	CO1, CO45
Sally	3/5/2022	99214	CO109
Joe	2/28/2022	99204	CO1, CO2, CO45, CO253
Mark	1/17/2022	99214	CO11

[Micron]

If the number is always the last character and there is always only one you can use the Right function in your query.

Note, each of these adjustment field values would be on separate rows.

Would be because they already are, or need to be but are not? Might help to make sense of the data and expected result if you posted sample data. Very easily done by pasting some Excel cells into your post. You might have to edit the resulting table to suit. If your adjustment field is a multi value field e.g.

ANIMALS

dog, cat, bird, fish

that's not so good.
Note the pasted Excel cells with table properties edited to suit.

[Gicu]

Can you show a sample of actual data as not quite sure what you mean? Something like this?
SOURCE TARGET
AB1 1
A1 1
AB1,CD2,F4 1,2,4

Cheers,

[squatch513]

Edited my post with a sample, thanks Vlad!

[squatch513]

Edited my post with a sample, thanks!

[Micron]

Looks to me like you'll need a function that your query can call.
I'm about to go out for a few hours, so take it away Vlad!

[Gicu]

Add this function to a standard module:

Function GetNumber(ByVal pStr As String) As Long
Dim intLen  As Integer
Dim n       As Integer
    pStr = Trim(pStr) 'removes leading & trending spaces
    intLen = Len(pStr) 'stores original length
    n = 1 'consider this a counter & position marker
    If pStr = "" Or IsNull(pStr) Or intLen = 0 Then Exit Function 'validate we didn't get passed an empty/null string
    Do
        If IsNumeric(Mid(pStr, n, 1)) Then 'check if that single character is a number
            GetNumber = GetNumber & Mid(pStr, n, 1) 'if it is add to existing ones if any
            n = n + 1 'add to counter so we know to go to next character on the next pass/loop
        Else
            n = n + 1 'it wasn't a number, add to counter so we know to skip it
        End If
    Loop Until intLen = (n - 1) 'go until we processed all characters. The reason we have to do n-1 is that Len starts at 0 & we told n to start at 1
End Function 'if no numbers function will return default value of data type, in our case long would be 0

Then in your query add a calculated field:
FirstAdjNumeric: IIf(InStr([Adjustment],",")>0,GetNumber(Left([Adjustment],InStr([Adjustment],","))),GetNumber([Adjustment]))

Cheers,

[squatch513]

Add this function to a standard module:

Function GetNumber(ByVal pStr As String) As Long
Dim intLen  As Integer
Dim n       As Integer
    pStr = Trim(pStr) 'removes leading & trending spaces
    intLen = Len(pStr) 'stores original length
    n = 1 'consider this a counter & position marker
    If pStr = "" Or IsNull(pStr) Or intLen = 0 Then Exit Function 'validate we didn't get passed an empty/null string
    Do
        If IsNumeric(Mid(pStr, n, 1)) Then 'check if that single character is a number
            GetNumber = GetNumber & Mid(pStr, n, 1) 'if it is add to existing ones if any
            n = n + 1 'add to counter so we know to go to next character on the next pass/loop
        Else
            n = n + 1 'it wasn't a number, add to counter so we know to skip it
        End If
    Loop Until intLen = (n - 1) 'go until we processed all characters. The reason we have to do n-1 is that Len starts at 0 & we told n to start at 1
End Function 'if no numbers function will return default value of data type, in our case long would be 0

Then in your query add a calculated field:
FirstAdjNumeric: IIf(InStr([Adjustment],",")>0,GetNumber(Left([Adjustment],InStr([Adjustment],","))),GetNumber([Adjustment]))

Cheers,

Good morning!

I added both the module and the calc'd field. But when I run it, I get a popup asking me to select a FirstAdjNumeric. Not sure why, and I'm stuck trying to fix it.

Also, that module doesn't do anything apparent. When I try to run it, it just asks me to select a macro to run. What might I be doing incorrectly?

Last bit - "GetNumber" doesn't exist in Access, that I can see. Should I use 'Val' instead?

Let me know what you can, really appreciate it!

[squatch513]

Better example of the data set


Attachment 47642

[Gicu]

Your latest attachment doesn't work. Please show what you've done. GetNumber is the custom function that I asked you to add to the standard module so it will exist once you add it.

But when I run it, I get a popup asking me to select a FirstAdjNumeric

You say you added the calculated field, can you please show it to us?

FirstAdjNumeric: IIf(InStr([Adjustment],",")>0,GetNumber(Left([Adjustment],InStr([Adjustment],","))),GetNumber([Adjustment]))

[Micron]

I'd use Instr to find , and eliminate the long IIF in the query. If Instr results in 0 Len is just the Len of the string. If > 0 Len is the length from 1 to Instr minus 1.

GetNumber is the name of the function that was given. FirstAdjNumeric is the alias of the query field.

[squatch513]

Your latest attachment doesn't work. Please show what you've done. GetNumber is the custom function that I asked you to add to the standard module so it will exist once you add it.

You say you added the calculated field, can you please show it to us?

FirstAdjNumeric: IIf(InStr([Adjustment],",")>0,GetNumber(Left([Adjustment],InStr([Adjustment],","))),GetNumber([Adjustment]))

Vlad- my work attached.

[Gicu]

Hi,

You have it wrong, remove the expression from the "Criteria:" row and move it to the top (where you have Expr1). So on the first row ("Field:") you should have the expression as shown with nothing in the criteria row below:
FirstAdjNumeric: IIf(InStr([Adjustment],",")>0,GetNumber(Left([Adjustment],InStr([Adjustment],","))),GetNumber([Adjustment]))

Cheers,

[squatch513]

Vlad, my mistake - I had it in the "Field" and must've copy pasted. Ok, so that is fixed - but that module just gives me a Macro popup- there are no macros. Am I inserting a module incorrectly? Can't thank you enough!!

[Gicu]

The reason for the error\popup is that you named the module with the same name as the public function; rename the module modGetNumber and you should be OK.

Cheers,