Dynamic quary

[Josh]

I keep getting errors in my search button code. can any one tell me
whats wrong. Error happens clear at the bottom in this code :
"Select * from workorders " & (" where " + Mid(varwhere, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"



Private Sub Search_Click()
Dim varwhere As Variant, vardatesearch As Variant
Dim db As DAO.Database
Dim qd As QueryDef
Set db = CurrentDb()
On Error Resume Next
db.QueryDefs.Delete (dynamic_query)
On Error GoTo 0
varwhere = Null
vardatesearch = Null
'Verifies something in jcn from and that it is a valid date
If Not IsNull(Me.StartDate) Then
If Not IsDate(Me.StartDate) Then
MsgBox "The value in JCN From is not a valid date.", _
vbCritical, gstrapptitle
Exit Sub
End If
'Verifies something in jcn to and that it is a valid date
If Not IsNull(Me.enddate) Then
If Not IsDate(Me.enddate) Then
MsgBox "The value in JCN to is not a valid date.", _
vbCritical, gstrapptitle
Exit Sub
End If
'Have two dates, now make sure to is >= from
If Me.StartDate < Me.enddate Then
MsgBox "JCN To date must be greater than " & _
"or equal to JCN From Date.", _
vbCritical, gstrapptitle
Exit Sub
End If
End If
Else
'NO FROM BUT SEPCIFY A TO?
If Not IsNull(Me.enddate) Then
If Not IsDate(Me.enddate) Then
MsgBox "The value in JCN TO is not a valid date.", _
vbCritical, gstrapptitle
Exit Sub
End If
End If
End If
'Start of filter
'Check JCN
If Not IsNull(Me.JCN) Then
varwhere = (varwhere + " and ") & "[jobcontrolnum] like '" & Me.JCN
& "*'"
End If
'Check Equipment ID
If Not IsNull(Me.equipid) Then
varwhere = "[equipment id] = '" & Me.equipid & "'"
End If
'Check serial number
If Not IsNull(Me.sn) Then
varwhere = "[serialnumber] = '" & Me.sn & "'"
End If
'Check work unit code in linking tabe
If Not IsNull(Me.WUC) Then
varwhere = (varwhere + " and ") & _
"[workunitcode] in (select workunitcode from workorderparts " & _
"WHERE workorderparts.workunitcode like " & Me.WUC & "*')"
End If
If Not IsNull(Me.StartDate) Then
vardatesearch = (vardatesearch + " and ") & _
"workorders.datecomplted >= #" & _
Me.StartDate & "#"
End If
If Not IsNull(Me.enddate) Then
vardatesearch = (vardatesearch + "and ") & _
"workorders.datecompleted < #" & _
CDate(Me.enddate) + 1 & "#"
End If
If Not IsNull(vardatesearch) Then
varwhere = (varwhere + " and ") & _
"[datecompleted[ in ( select datecompleted from workorders " & _
"where " * vardatesearch & ")"
End If
'check filter
If IsNull(varwhere) Then
MsgBox "You must enter at least one search criteria.", _
vbInformation, gstrapptitle
Exit Sub
End If

Set qd = db.CreateQueryDef("Dynamic_Query", _
"Select * from workorders " & (" where " + Mid(varwhere, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"
End Sub

 

[Brendan Reynolds]

Well, you've got two left parentheses and three right parentheses. That's
one right parenthesis too many!

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I keep getting errors in my search button code. can any one tell me whats
wrong. Error happens clear at the bottom in this code :
"Select * from workorders " & (" where " + Mid(varwhere, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"



Private Sub Search_Click()
Dim varwhere As Variant, vardatesearch As Variant
Dim db As DAO.Database
Dim qd As QueryDef
Set db = CurrentDb()
On Error Resume Next
db.QueryDefs.Delete (dynamic_query)
On Error GoTo 0
varwhere = Null
vardatesearch = Null
'Verifies something in jcn from and that it is a valid date
If Not IsNull(Me.StartDate) Then
If Not IsDate(Me.StartDate) Then
MsgBox "The value in JCN From is not a valid date.", _
vbCritical, gstrapptitle
Exit Sub
End If
'Verifies something in jcn to and that it is a valid date
If Not IsNull(Me.enddate) Then
If Not IsDate(Me.enddate) Then
MsgBox "The value in JCN to is not a valid date.", _
vbCritical, gstrapptitle
Exit Sub
End If
'Have two dates, now make sure to is >= from
If Me.StartDate < Me.enddate Then
MsgBox "JCN To date must be greater than " & _
"or equal to JCN From Date.", _
vbCritical, gstrapptitle
Exit Sub
End If
End If
Else
'NO FROM BUT SEPCIFY A TO?
If Not IsNull(Me.enddate) Then
If Not IsDate(Me.enddate) Then
MsgBox "The value in JCN TO is not a valid date.", _
vbCritical, gstrapptitle
Exit Sub
End If
End If
End If
'Start of filter
'Check JCN
If Not IsNull(Me.JCN) Then
varwhere = (varwhere + " and ") & "[jobcontrolnum] like '" & Me.JCN &
"*'"
End If
'Check Equipment ID
If Not IsNull(Me.equipid) Then
varwhere = "[equipment id] = '" & Me.equipid & "'"
End If
'Check serial number
If Not IsNull(Me.sn) Then
varwhere = "[serialnumber] = '" & Me.sn & "'"
End If
'Check work unit code in linking tabe
If Not IsNull(Me.WUC) Then
varwhere = (varwhere + " and ") & _
"[workunitcode] in (select workunitcode from workorderparts " & _
"WHERE workorderparts.workunitcode like " & Me.WUC & "*')"
End If
If Not IsNull(Me.StartDate) Then
vardatesearch = (vardatesearch + " and ") & _
"workorders.datecomplted >= #" & _
Me.StartDate & "#"
End If
If Not IsNull(Me.enddate) Then
vardatesearch = (vardatesearch + "and ") & _
"workorders.datecompleted < #" & _
CDate(Me.enddate) + 1 & "#"
End If
If Not IsNull(vardatesearch) Then
varwhere = (varwhere + " and ") & _
"[datecompleted[ in ( select datecompleted from workorders " & _
"where " * vardatesearch & ")"
End If
'check filter
If IsNull(varwhere) Then
MsgBox "You must enter at least one search criteria.", _
vbInformation, gstrapptitle
Exit Sub
End If

Set qd = db.CreateQueryDef("Dynamic_Query", _
"Select * from workorders " & (" where " + Mid(varwhere, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"
End Sub


--
Joshua T. Campbell - MCP, A+

This posting is provided "AS IS" with no warranties, and confers no
rights.