ทำฟอร์มค้นหาข้อมูลโดยใช้ MS Access ค่ะ ปัญหาคือโค้ดในส่วนของปุ่ม Search เกิด Run-time Error
ข้อความบอกว่า "Data type mismatch in criteria expression" ค่ะ
และขึ้นแถบสีเหลืองตรงโค้ดส่วน Me.subfrm.Form.RecordSource = "SELECT * FROM qryEquipment " & BuildFilter
อันนี้คือโค้ดทั้งหมดในฟอร์มค้นหาค่ะ เลยอยากให้ช่วยตรวจสอบให้หน่อย
ปุ่ม Clear ข้อมูล Code (VB.NET)
Private Sub cmdClear_Click()
Dim intIndex As Integer
Me.cbBitRate = 0
Me.cbNetwork = 0
Me.cbStatus = 0
Me.txtNodeName = ""
Me.txtE1 = ""
Me.txtSlot = ""
Me.txtDestination = ""
Me.txtCustomer = ""
Me.txtSO = ""
End Sub
ปุ่ม Search
Code (VB.NET)
Private Sub cmdSearch_Click()
Me.subfrm.Form.RecordSource = "SELECT * FROM qryEquipment " & BuildFilter
Me.subfrm.Requery
End Sub
Code (VB.NET)
Private Sub Form_Load()
cmdClear_Click
End Sub
Code (VB.NET)
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varItem As Variant
Dim intIndex As Integer
varWhere = Null ' Main filter
' Check for BitRateID
If Me.cbBitRate > 0 Then
varWhere = varWhere & "[BitRate] = " & Me.cbBitRate & " AND "
End If
' Check for NetworkID
If Me.cbNetwork > 0 Then
varWhere = varWhere & "[Network] = " & Me.cbNetwork & " AND "
End If
' Check for StatusID
If Me.cbNetwork > 0 Then
varWhere = varWhere & "[Status] = " & Me.cbStatus & " AND "
End If
' Check for LIKE Node Name
If Me.txtNodeName > "" Then
varWhere = varWhere & "[NodeName] LIKE """ & Me.txtNodeName & "*"" AND "
End If
' Check for LIKE E1
If Me.txtE1 > "" Then
varWhere = varWhere & "[E1] LIKE " & Me.txtE1 & " AND "
End If
' Check for LIKE Slot
If Me.txtSlot > "" Then
varWhere = varWhere & "[Slot] LIKE " & Me.txtSlot & " AND "
End If
' Check for LIKE Destination
If Me.txtDestination > "" Then
varWhere = varWhere & "[Destination] LIKE """ & Me.txtDestination & "*"" AND "
End If
' Check for LIKE Customer
If Me.txtCustomer > "" Then
varWhere = varWhere & "[Customer] LIKE """ & Me.txtCustomer & "*"" AND "
End If
' Check for LIKE Service Order
If Me.txtSO > "" Then
varWhere = varWhere & "[ServiceOrder] LIKE """ & Me.txtSO & "*"" AND "
End If
' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function
ตรงส่วนที่ Error พอเปลี่ยน query เป็น table ไม่ขึ้น Error ค่ะ แต่ว่าตรง field ที่เป็น FK
มันขึ้นเป็น Number อ่ะค่ะ แต่พอดูใน Query ก็ขึ้นเป็น Text นะคะ
รบกวนช่วยดูให้หน่อยนะคะ ขอบคุณค่ะ
มันน่าจะไม่มีค่าครับ ปกติใน Access ถ้าเป็น String จะต้องใส่ WHERE = 'xxx' แต่ถ้า Integer ให้ใช้ WHERE = 123 ลองดูดี ๆ ครับ Error นี้มีอยู่แค่นี้ครับ
Date :
2012-08-08 17:40:28
By :
mr.win
No. 2
Guest
ลองแก้แล้วค่ะ ... ก็ยังแก้ไม่ได้ ลองแก้ให้เป็น
Code (VB.NET)
Private Sub cmdSearch_Click()
If BuildFilter = "" Then
Me.subfrm.Form.RecordSource = "SELECT * FROM qryEquipment " & BuildFilter
Else
Me.subfrm.Form.RecordSource = "SELECT * FROM qryEquipment WHERE " & BuildFilter
End If
Me.subfrm.Requery
End Sub
Private Sub cmdClear_Click()
Dim intIndex As Integer
' Clear all search items
Me.cbBitRate = 0
Me.cbNetwork = 0
Me.cbStatus = 0
Me.txtNodeName = ""
Me.txtE1 = ""
Me.txtSlot = ""
Me.txtDestination = ""
Me.txtCustomer = ""
Me.txtSO = ""
End Sub
Private Sub cmdSearch_Click()
Me.subfrmSearch.Form.RecordSource = "SELECT * FROM qryEquipment " & BuildFilter
Me.subfrmSearch.Requery
End Sub
Private Sub Form_Load()
' Clear the search form
cmdClear_Click
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varItem As Variant
Dim intIndex As Integer
varWhere = Null ' Main filter
' Check for Street
If Me.cbBitRate > 0 Then
varWhere = varWhere & " [BitRateID] " & Me.cbBitRate & " AND "
End If
' Check for Area
If Me.cbNetwork > 0 Then
varWhere = varWhere & "[NetworkID] " & Me.cbNetwork & " AND "
End If
' Check for Town/City
If Me.cbStatus > 0 Then
varWhere = varWhere & "[StatusID] " & Me.cbStatus & " AND "
End If
' Check for First Name/Initial
If Me.txtNodeName > "" Then
varWhere = varWhere & "[NodeName] LIKE """ & Me.txtNodeName & "*"" AND "
End If
' Check for Surname
If Me.txtE1 > "" Then
varWhere = varWhere & "[E1] " & Me.txtE1 & " AND "
End If
' Check for Business Name
If Me.txtSlot > "" Then
varWhere = varWhere & "[Slot] " & Me.txtSlot & " AND "
End If
' Check for Email Address
If Me.txtDestination > "" Then
varWhere = varWhere & "[Destination] LIKE """ & Me.txtDestination & "*"" AND "
End If
' Check for House/Flat Number
If Me.txtCustomer > "" Then
varWhere = varWhere & "[Customer] LIKE """ & Me.txtCustomer & "*"" AND "
End If
If Me.txtSO > "" Then
varWhere = varWhere & "[ServiceOrder] LIKE """ & Me.txtSO & "*"" AND "
End If
' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function