Using Function as Criteria in Query (1 Viewer)

[Wolfz91]

Hi,
This may need to go in the queries section but does involve a bit of both.

Basically I am making a query which needs the criteria to be based on information in another table. Note this is the only filter on the column and the reason for the function is this information will vary over time and to save time later on when we add or remove criteria.

The query exists of a field called 'Depots' which contains depot numbers and will have a large number of records.
I need this to be filter by the list of depot numbers within a separate table so I build a function to run through the recordset and create the criteria, however if the depots field is set to number I get an error message 'Data Mismatch' (Hate this error) and as text I just get no Results.

Here the code for the function


Function CDepots()
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strsql As String
Dim i As Integer
Dim DepotList As String
Set rs = New ADODB.Recordset
Set cnn = CurrentProject.Connection
i = 0
strsql = "SELECT [Concerto Depots].* " & _
"FROM [Concerto Depots];"

rs.Open strsql, cnn, adOpenDynamic, adLockReadOnly
rs.MoveFirst
Do Until rs.BOF = True Or rs.EOF = True
If i = 0 Then
DepotList = "" & rs.Fields("Depot Number") & ""
rs.MoveNext
Else
DepotList = DepotList & " Or " & rs.Fields("Depot Number") & ""
rs.MoveNext
End If
i = 1
Loop
Set rs = Nothing
Set cnn = Nothing

ConcertoDepots = DepotList
Debug.Print CDepots


The output is:
20 Or 30 Or 41 Or 42 Or 45 Or 52

I have tried this with ' around the numbers and ". either way I get the same results even if I change the field type

First issue I ever had with number fields in a long time.

Many thanks

 

[pbaldy]

I don't think this will work as you expect, unless you build the query in code. Joining the tables would be more efficient and accomplish the same goal.

SELECT Field1, Field2,...
FROM OtherTable INNER JOIN [Concerto Depots] ON OtherTable.FieldName = [Concerto Depots].[Depot Number]

 

[MarkK]

I would expect to see something more like this as your result . . .

Field 1 = 20 Or Field1 = 30 Or Field1 = 41 Or Field1 = 42 . . .

Also, consider this pattern for your loop . . .

Do Until rs.EOF
[COLOR="Green"]   'construct your string with a leading "Or "[/COLOR]
   DepotList = DepotList & "Or Field1 = " & rs.Fields("Depot Number") & " "
   rs.MoveNext
Loop
[COLOR="Green"]'drop the leading "Or ," if it's present, here[/COLOR]
If DepotList <> "" then DepotList = Mid(DepotList, 4)

. . . so no If...Else...End If block in the loop, we just remove the leading "Or " after the loop completes.

 

[GohDiamond]

Your data comes from where? If you're getting data mismatch then your data is not all of the same datatype. This can occur when an excel column field is used for numbers, references, formulas and strings all in the same column. Your optic may appear to be all numbers but it's not.
If your data is coming from an Access table then you need to set your process to deal with to the same datatype as the field in which it's contained.
Just because your field name says Depot Number, it may actually be an ID String not real numbers.
Look thru your data, something's wrong in there most likely. GIGO

Cheers
Goh

 

[Wolfz91]

I think I was trying to over complicate things just a little bit. doing an inner join would work just fine in this case.

Thank you

 

[pbaldy]

No problem and welcome to the site by the way!