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?
CodePudding user response:
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"
CodePudding user response:
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]))"