nepotist
02-03-2009, 09:49 AM
Private Sub cmdCreate_Click()
Dim db As Database
Dim strsql As String
Dim SearchWord As String
Dim qreydef As QueryDef
On Error GoTo delquery
'DoCmd.DeleteObject acQuery, "QrySearchKeyWord"
line1:
SearchWord = [Form_Key Word Search]!TxtRoadway.Value
Set db = CurrentDb()
strsql = "SELECT Mitigation_Database.DRICODE, Mitigation_Database.MITIGATION, Mitigation_Database.STATUS, Mitigation_Database.DATE_, DRI_Name_Status_Database.DRINAME, DRI_Name_Status_Database.DRICODE " _
& "FROM Mitigation_Database, DRI_Name_Status_Database " _
& "WHERE Mitigation_Database.MITIGATION Like '" & SearchWord & "*' And DRI_Name_Status_Database!DRICODE=Mitigation_Database!DRICODE;"
Set qreydef = db.CreateQueryDef("QrySearchKeyWord", strsql)
MsgBox "Query Created"
DoCmd.OpenReport "Rdwy Specific Information", acViewPreview
delquery:
If Err = 3021 Then
DoCmd.DeleteObject acQuery, "QrySearchKeyWord"
GoTo line1:
Else
MsgBox Err.Description
End If
End Sub
here is my code to create a report from a table.
What I want is only those records that contain the word/text typed by the user in the text box.
for some reason this code does not give any error but it returns no records. dont know why. I guess I am right with the qoutations after the like syntax... anyways removing the single qoutes doesnt works it gives me a syntax missing error.
Any clue??
Dim db As Database
Dim strsql As String
Dim SearchWord As String
Dim qreydef As QueryDef
On Error GoTo delquery
'DoCmd.DeleteObject acQuery, "QrySearchKeyWord"
line1:
SearchWord = [Form_Key Word Search]!TxtRoadway.Value
Set db = CurrentDb()
strsql = "SELECT Mitigation_Database.DRICODE, Mitigation_Database.MITIGATION, Mitigation_Database.STATUS, Mitigation_Database.DATE_, DRI_Name_Status_Database.DRINAME, DRI_Name_Status_Database.DRICODE " _
& "FROM Mitigation_Database, DRI_Name_Status_Database " _
& "WHERE Mitigation_Database.MITIGATION Like '" & SearchWord & "*' And DRI_Name_Status_Database!DRICODE=Mitigation_Database!DRICODE;"
Set qreydef = db.CreateQueryDef("QrySearchKeyWord", strsql)
MsgBox "Query Created"
DoCmd.OpenReport "Rdwy Specific Information", acViewPreview
delquery:
If Err = 3021 Then
DoCmd.DeleteObject acQuery, "QrySearchKeyWord"
GoTo line1:
Else
MsgBox Err.Description
End If
End Sub
here is my code to create a report from a table.
What I want is only those records that contain the word/text typed by the user in the text box.
for some reason this code does not give any error but it returns no records. dont know why. I guess I am right with the qoutations after the like syntax... anyways removing the single qoutes doesnt works it gives me a syntax missing error.
Any clue??