How to do a join range query

[Elina]

Hi all
I am currently trying to do a range join query in access (not an expert at all with it) but I realized that it’s not easy. It probably needs a VBA code but still not sure how to do it.

I basically have a column with number of days e.g

Number of days
18
1
0
56
8
-19
369

Eventually I would like to do a range join query (similar to excel range vlookup) with the following table

NumberofDays Weeks
0 Same Day
1 1 Week
7 1 Week +
14 2 Week +
21 3 Week +
30 1 Month +
60 2 Month +
90 3 Month +
120 4 Month +
150 5 Month +
180 6 Month +
210 7 Month +
240 8 Month +
270 9 Month +
300 10 Month +
330 11 Month +
360 12 Month +

After joining the two tables with an approximate range (as in excel) the final table should look like below

Number of days Weeks
18 2 Week +
1 1 Week
0 Same Day
56 1 Month +
8 1 Week +
-19
369 12 Month +

Note that value -19 is an error so it should not return anything however i still want to see the value in the final table.

Hope the above explanation makes sense.

I would be very grateful if someone can help me

Thanks

 

[TJPoorman]

A better way to do this would be with a public function called in a query. Something like this should work for you:

Public Function retNumberWeeks(lngDays As Long) As String
    If lngDays = 0 Then
        retNumberWeeks = "Same Day"
    ElseIf lngDays < 7 Then
        retNumberWeeks = "1 Week"
    Else
        retNumberWeeks = (Fix(lngDays / 7)) & " Weeks +"
    End If
End Function

 

[Elina]

Many thanks for your quick reply.

However I am new to VBA so please forgive me if this is a stupid question but do I just copy(changing the variable names of course) and paste your syntax above in an SQL statement in a query?

It seems that I get the following warning:
Invalid SQL statement:expected 'DELETE', 'INSERT','PROCEDURE','SELECT OR 'UPDATE'

Thanks in advance

 

[Galaxiom]

Paste the code into a Standard Module.

Use the function in a query to return the Weeks for [Number of Days]

In the Field cell of a column in the query designer:

Weeks: retNumberWeeks([Number of Days])

 

[Elina]

it works perfectly!

Thanks a lot