How do I edit the WHERE clause of my VBA SQL-statement so I returns the records from more than 1 field?

user_16859659 0
Viewed 58

I have this basic VBA SQL-statement. I searches an external database and returns all the records where the field [LabNumberPrimary] = [labnummer] in the external database.

My VBA code repeats itself with some minor adjustments. How do I combine the 2 statements so my VBA code gets smaller and more user friendly?

1st statement:

cmdString = "SELECT DISTINCT t.rapportnaam, t.norm, t.analysenaam FROM taken t, monsters m, opdrachten o WHERE o.labnummer = " & [LabNumberPrimary] & " AND m.opdrachtteller = o.opdrachtteller AND t.monsterteller = m.monsterteller"

2nd statement:

cmdString = "SELECT DISTINCT t.rapportnaam, t.norm, t.analysenaam FROM taken t, monsters m, opdrachten o WHERE o.labnummer = " & [LabNumber_4_CT] & " AND m.opdrachtteller = o.opdrachtteller AND t.monsterteller = m.monsterteller"

How do I combine those 2 statements? I tried:

cmdString = "SELECT DISTINCT t.rapportnaam, t.norm, t.analysenaam FROM taken t, monsters m, opdrachten o WHERE o.labnummer = " & [LabNumberPrimary] & " AND o.labnummer = " & [LabNumber_4_CT] & " AND m.opdrachtteller = o.opdrachtteller AND t.monsterteller = m.monsterteller"

But does not seem to work.

LabNumberPrimary and LabNumber_4_CT are column names in my query qry_Administration

EDIT: After some research I think I know why there are no records returning.

This piece of code:

AND m.opdrachtteller = o.opdrachtteller AND t.monsterteller = m.monsterteller"

The 'teller' is different for both numbers. So I think the solution should be something like this: I think you guys know where I am going:

cmdString = "SELECT DISTINCT t.rapportnaam, t.norm, t.analysenaam" & _
" FROM taken t, monsters m, opdrachten o" & _
" WHERE (o.labnummer = " & [LabNumberPrimary] & _
" AND m.opdrachtteller = o.opdrachtteller" & _
" AND t.monsterteller = m.monsterteller")

" AND WHERE (o.labnummer = " & [LabNumber_4_CT] & _
" AND m.opdrachtteller = o.opdrachtteller" & _
" AND t.monsterteller = m.monsterteller")

Is it even possible to combine such a statement?

ms-access vba
2 Answers

Try this:

cmdString = "SELECT DISTINCT t.rapportnaam, t.norm, t.analysenaam" & _
" FROM taken t, monsters m, opdrachten o" & _
" WHERE o.labnummer = " & [LabNumberPrimary] & _
" OR o.labnummer = " & [LabNumber_4_CT] & _
" AND m.opdrachtteller = o.opdrachtteller" & _
" AND t.monsterteller = m.monsterteller"
user_105426450

The solution was more difficult then expected. But I managed to get it working by reverse engineering. I copied the sql-statement in Query-editor, added my 3 fields but then as numbers. Then I went back to SQL-view and copied that code to my VBA code. I replaced the numbers with field names LabNumberPrimary LabNumber_4_CT. I also had to add the Nz( function for the null values

LabNumberPrimary = Nz([LabNumberPrimary])
LabNumber_3_ASB = Nz([LabNumber_3_ASB])
LabNumber_4_CT = Nz([LabNumber_4_CT])

cmdString = "SELECT DISTINCT t.rapportnaam, t.norm, t.analysenaam FROM taken AS t, monsters AS m, opdrachten AS o WHERE (((o.labnummer)='" & LabNumberPrimary & "' Or (o.labnummer)='" & LabNumber_3_ASB & "' Or (o.labnummer)='" & LabNumber_4_CT & "') AND ((m.opdrachtteller)=[o].[opdrachtteller]) AND ((t.monsterteller)=[m].[monsterteller]))"
user_168596590
Related