Using JOIN and result from function. Will not compile

[Middlemarch]

My attempted sql is

SELECT tblData2.Prefix, tblData2.LineNum, tblData2.Year, tblComments.comment, tblComments.Address
FROM tblData2 LEFT JOIN tblComments ON tblData2.LineNum = (NumbersOnly([tblComments].[Address])
WHERE (((tblData2.Year)<"1990"))
ORDER BY tblData2.LineNum;

Function NumbersOnly(loc) As String
Dim RE As Object, REMatches As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = "\d+"
    If (RE.Test(loc) = True) Then
        Set REMatches = RE.Execute(loc)
         NumbersOnly = REMatches(0)
    End If
Set REMatches = Nothing
Set RE = Nothing
End Function

Trust the above shows the problem. Perhaps I cannot use a function in this manner, but maybe there's a workaround?
Any advice appreciated. Thanks.

[ranman256]

Use a DLOOKUP in the function...not createObject.

why is the Year a text? It should be an integer.

[Middlemarch]

Could you show me what I'd change to use DLOOKUP. I'm new to that, what is the advantage?
That won't change the error will it ?

Year is text to avoid '0' in any fields with no year.

[orange]

Year is a reserved word in Access.
Why not use Date data type for Dates---that's what they are for?
See link in my signature for Access functions and examples.

Good luck.

PS. I don't use regex too often but thought I'd try your function.
IT picks up contiguous numerics at start of value. Similar to Val(loc).

[Middlemarch]

Table design emulated somethings else and there wasn't a lot of choice.
Val("AA22" ) return s 0 instead of 22. My function also returns 33 for "A33D6" and that's what was wanted.
Any more advice on DLOOKUP ? If I should change to that I will but have no idea how to apply it.
Can I refer back to the query again please? It doesn't work, is the concept the problem and/or is there another way?

Thanks

[orange]

I just ran your NumbersOnly again. Seems it picks up contiguous numerics.

Function NumbersOnly(loc) As String
Dim RE As Object, REMatches As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = "\d+"
    If (RE.test(loc) = True) Then
        Set REMatches = RE.Execute(loc)
         NumbersOnly = REMatches(0)
    End If
Set REMatches = Nothing
Set RE = Nothing
End Function

Sub testNumbersOnly()

Dim locn As Variant
locn = "2345rt65"
Debug.Print NumbersOnly(locn)
End Sub

Result is 2345 contiguous numerics
Val() picks up only the leftmost contiguous numerics.

Thanks for posting back.

[Middlemarch]

It's intended to extract a set numbers only from a particular string format e.g xxNNNxxxYY where x is text and N & Y numbers
It will get the leftmost contiguous numbers if there's no leading text. Also ignore YY if preceded by text.

[orange]

OK, back to your original question.
Is tblData2.Year a Date data type? I asked because dates need #1990# (dates encapsulated with octothorpes/hashes)
tblData2.Year is a number then you don't need the "1990". For numerics you use just the number 1990

Re DLookup see http://www.techonthenet.com/access/f...in/dlookup.php

[orange]

Is this you?
http://www.access-programmers.co.uk/...d.php?t=275974

We need to see the data that this query is working with. Can you post some records?

[Rawb]

You might not be able to use a Function like that. If that's the case, then you can get around it by creating a Query of tblComments with NumbersOnly([tblComments].[Address]) as a calculated field.

However, I see an extra open paren in your Query. Try this and see if it solves the problem for you:

SELECT tblData2.[Prefix], tblData2.[LineNum], tblData2.[Year], tblComments.[comment], tblComments.[Address]
FROM tblData2 LEFT JOIN tblComments ON tblData2.[LineNum] = NumbersOnly(tblComments.[Address])
WHERE (((tblData2.[Year])<"1990"))
ORDER BY tblData2.[LineNum];

[Middlemarch]

Many thanks for the responses. The problem was a missing bracket on the FROM clause. And I missed
that completely thinkinbg the join & function were wrong.