building where query!

yessir

yessir

Saved By Grace
Local time
Today, 11:28
Joined
May 29, 2003
Messages
349
I am trying to build a dynamic query using :
Code: 
        'build a dynamic search clause to retrieve the information.
            where = Null
            
            If Not (Nz(rec!Matrix, "") = "") Then
                where = where & " AND tblWater_Sample_Temp.Matrix = '" & rec!Matrix & "'"
            End If
            
            If Not (Nz(rec!Outlet_ID, "") = "") Then
                where = where & " AND tblWater_Sample_Temp.Outlet_ID = " & rec!Outlet_ID & ""
            End If
            
            If Not (Nz(rec!ClientID, "") = "") Then
                where = where & " AND tblWater_Sample_Temp.ClientID = '" & strClientID & "'"
            End If
            
            If Not (Nz(rec!ProjectNo, "") = "") Then
                where = where & " AND tblWater_Sample_Temp.ProjectNo =  '" & strProjectNo & "'"
            End If
            
            If Not (Nz(rec!LabID, "") = "") Then
                where = where & " AND tblWater_Sample_Temp.LabID = '" & strLabID & "'"
            End If
            
            If Not (Nz(rec!SampleDate, "") = "") Then
                where = where & " AND tblWater_Sample_Temp.SampleDate = #" & strDate & "#"
            End If
            
            where = where & " AND tblWater_Sample_Temp.Duplicate = " & rec!Duplicate & ""
            
            If Not (Nz(rec!DuplicateOf, "") = "") Then
                where = where & " AND tblWater_Sample_Temp.DuplicateOf = " & rec!DuplicateOf & ""
            End If
            
            If Not (Nz(rec!Elevation, "") = "") Then
                where = where & " AND tblWater_Sample_Temp.Elevation = " & strElevation & ""
            End If
            
            If Not (Nz(rec!Unit_ID, "") = "") Then
                where = where & " AND tblWater_Sample_Temp.Unit_ID = " & strUnitID & ""
            End If
            
            If Not (Nz(rec!DataSource, "") = "") Then
                where = where & " AND tblWater_Sample_Temp.DataSource = '" & strDataSource & "'"
            End If
            
            If Not (Nz(rec!Note, "") = "") Then
                where = where & " AND tblWater_Sample_Temp.Note = '" & strNote & "'"
            End If
            
        'Select the relevant information into the second recordset
            Set orec = dbs.OpenRecordset("SELECT [Analyte], [Method], [Result], [Units], [DL],  " & _
                "[LOQ], [FlowValue], [FlowUnit_ID], [Notes] " & _
                "FROM tblWater_Sample_Temp  " & _
                "WHERE " & Mid(where, 6) & ";")

here is a sample output for where:

" AND tblWater_Sample_Temp.Matrix = 'Water' AND tblWater_Sample_Temp.Outlet_ID = EN-01 AND tblWater_Sample_Temp.ClientID = 'HZ539 EN-01' AND tblWater_Sample_Temp.ProjectNo = 'H2539' AND tblWater_Sample_Temp.LabID = '04-H005638' AND tblWater_Sample_Tep"

where it should be:

" AND tblWater_Sample_Temp.Matrix = 'Water' AND tblWater_Sample_Temp.Outlet_ID = EN-01 AND tblWater_Sample_Temp.ClientID = 'HZ539 EN-01' AND tblWater_Sample_Temp.ProjectNo = 'H2539' AND tblWater_Sample_Temp.LabID = '04-H005638' AND
tblWater_Sample_Temp.SampleDate = #1/30/04# AND tblWater_Sample_Temp.Duplicate = False"

~ REALLY :confused:
 
I think your string is hitting some kind of length limit. Try useing two varibles.


???
ken
 
I am now using 4 variables.

but my q now is how do i combine these in the query?

ie:

Code: 
if not(nz(where, "")="") and not(nz(where_cont1, "")="") and not(nz(where_cont2, "")="") and not(nz(where_cont3, "")="") then
  & mid(where, 6) & mid(where_cont1, 6) & mid(where_cont2, 6) & mid(where_cont3, 6) 
else if nz(where, "")="") and not(nz(where_cont1, "")="") and not(nz(where_cont2, "")="") and not(nz(where_cont3, "")="") then
  & mid(where_cont1, 6) & mid(where_cont2, 6) & mid(where_cont3, 6) 
...
 
into the sql


Code: 
Set orec = dbs.OpenRecordset("SELECT [Analyte], [Method], [Result], [Units], [DL],  " & _
                "[LOQ], [FlowValue], [FlowUnit_ID], [Notes] " & _
                "FROM tblWater_Sample_Temp  " & _
                "WHERE " & Mid(where, 6) & ";")
 
all of the dynamic built strings begin with " AND " because you do not know which is the first one to contain a value and therefore which is the first line in the dynamic string.

therefore you have to use Mid(where, 6) to remove the first " AND "..

here is the condition i came up with, what do you think?:

Code: 
            If Not (Nz(where, "") = "") Then
                where_comp = "Mid(where, 6) "
                If Not (Nz(where_cont1, "") = "") Then
                    where_comp = where_comp & " & where_cont1"
                End If
                If Not (Nz(where_cont2, "") = "") Then
                    where_comp = where_comp & " & where_cont2"
                End If
                If Not (Nz(where_cont3, "") = "") Then
                    where_comp = where_comp & " & where_cont3"
                End If
            ElseIf Nz(where, "") = "" And Not (Nz(where_cont1, "") = "") Then
                where_comp = "Mid(where_cont1, 6) "
                If Not (Nz(where_cont2, "") = "") Then
                    where_comp = where_comp & " & where_cont2"
                End If
                If Not (Nz(where_cont3, "") = "") Then
                    where_comp = where_comp & " & where_cont3"
                End If
            ElseIf Nz(where, "") = "" And Nz(where_cont1, "") = "" And Not (Nz(where_cont2, "") = "") Then
                where_comp = "Mid(where_cont2, 6) "
                If Not (Nz(where_cont3, "") = "") Then
                    where_comp = where_comp & " & where_cont3"
                End If
            End If
            Else
                where_comp = "Mid(where_cont3, 6) "
            End If
 
Don't leave me hanging, did it work?

ken
 
sorry i took so long hte computer went to the blue screen of death

Code: 
            If Not (Nz(where, "") = "") Then
                where_comp = "Mid(where, 6) "
                If Not (Nz(where_cont1, "") = "") Then
                    where_comp = where_comp & " & where_cont1"
                End If
                If Not (Nz(where_cont2, "") = "") Then
                    where_comp = where_comp & " & where_cont2"
                End If
                If Not (Nz(where_cont3, "") = "") Then
                    where_comp = where_comp & " & where_cont3"
                End If
            ElseIf Nz(where, "") = "" And Not (Nz(where_cont1, "") = "") Then
                where_comp = "Mid(where_cont1, 6) "
                If Not (Nz(where_cont2, "") = "") Then
                    where_comp = where_comp & " & where_cont2"
                End If
                If Not (Nz(where_cont3, "") = "") Then
                    where_comp = where_comp & " & where_cont3"
                End If
            ElseIf Nz(where, "") = "" And Nz(where_cont1, "") = "" And Not (Nz(where_cont2, "") = "") Then
                where_comp = "Mid(where_cont2, 6) "
                If Not (Nz(where_cont3, "") = "") Then
                    where_comp = where_comp & " & where_cont3"
                End If
            Else
                where_comp = "Mid(where_cont3, 6) "
            End If
            
        'Select the relevant information into the second recordset
            Set orec = dbs.OpenRecordset("SELECT [Analyte], [Method], [Result], [Units], [DL],  " & _
                "[LOQ], [FlowValue], [FlowUnit_ID], [Notes] " & _
                "FROM tblWater_Sample_Temp  " & _
                "WHERE " & where_comp & ";")

this didn't work, it won't use the string where_comp (teh build or the other 3) ..

can u help?
 
KenHigg said:
I think your string is hitting some kind of length limit.
Click to expand...

A string can have millions of characters.

Yessir, please see this.

I help when I have time and when I can and not when somebody PMs me. I do have my own job to worry about - everything else, this forum most certainly included, is secondary.
 
" AND tblWater_Sample_Temp.Matrix = 'Water' AND tblWater_Sample_Temp.Outlet_ID = EN-01 AND tblWater_Sample_Temp.ClientID = 'HZ539 EN-01' AND tblWater_Sample_Temp.ProjectNo = 'H2539' AND tblWater_Sample_Temp.LabID = '04-H005638' AND tblWater_Sample_Tep"
Click to expand...

Is this a direct cut and paste?
Is the last part:

'tblWater_Sample_Tep'
or
'tblWater_Sample_Temp'
Click to expand...

What kind of error message do you get?

???
ken
 
tblWater_Sample_Temp.Outlet_ID = EN-01

This is a String but you are treating the criteria as a number.

For some reason, it is also duplicated within this other field:

tblWater_Sample_Temp.ClientID = 'HZ539 EN-01'
 
so which is the correct way?

and can i go back to a single vara with those changes?
 
which is the correct way?

" "
or
' " " '
 
You leave the single quotes out -

???
ken
 
sorry, final verification

# use quotes
string don't use quotes
 
You must log in or register to reply here.

Similar threads

A
Query Not Visible When Connecting to Excel
Replies
14
Views
504
Gasman
Gasman
K
'Next n' Looping rather than moving to the next record
Replies
16
Views
861
arnelgp
arnelgp
K
strSql errors
2
Replies
26
Views
829
cheekybuddha
cheekybuddha
F
Solved Form with subform filtered by two cascading combo boxes and a checkbox
2
Replies
26
Views
744
Faoineag
F
zelarra821
Solved Loop through records in a table that gives an error
Replies
4
Views
427
zelarra821
zelarra821

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom