D
DMUM via AccessMonster.com
Hello
I am rying to recreate the Find/Replace wizard for a person who needs to be
able to find blank fields within a table and change then to the new data. As
far as I know, you can not o this with the built in Access find/replace -
correct?
Anyway, I built a form that works fine for finding and replacing table fields
containing text data by using an update statement. However, when I try it
with a numerical table field or a yes/no table field, it always gives me the
"Data Type mismatch" error. I tried changing the table field obtained from
the user form to a variant before running through the update query, however,
it works sometimes, and other times it asks for a parameter.
I am guessing my best option is to capture the data type of the table field
and then have different update statements for each data type. This is where
I am running into a problem. Honestly, I can't figure out how to capture the
datatype - not even sure where to start. I did see one posting here for
capture all the data types in a table through a loop, however, I only want to
capture the data type in my find field on my form. Can someone please help
me. I thought that this form would be simple, forgetting all the different
data types that the person could try to change in the table would make it a
bit nore complicated. Here is a copy of my code. If you know a better way
to do this, please advise.
Thank you
*********************************************************************************
Dim tblName As String
Dim colName As String
Dim findfield As Variant
Dim repfield As Variant
Dim tblColumnName As String
Dim emptyfield As String
tblName = Me.cboGettable
colName = Me.cboField
findfield = Me.txtFind
repfield = Me.txtReplace
emptyfield = ""
If Me.txtFind = "" Or IsNull(Me.txtFind) Or Me.txtFind = "Blank" Or Me.
txtFind = "Empty" Or Me.txtFind = "IsNull"
Or Me.txtFind = "Is Null" Or Me.txtFind = "Null" Or Me.txtFind = "null" Or Me.
txtFind = "blank" Then
DoCmd.RunSQL "UPDATE [" & tblName & "]SET [" & tblName & "].[" & colName & "]
= """ & Me.txtReplace & """
WHERE (isNull([" & tblName & "].[" & colName & "])) or ([" & tblName & "].["
& colName & "])=""" & emptyfield
& """ "
ElseIf Me.txtReplace = "" Or IsNull(Me.txtReplace) Or Me.txtReplace = "Blank"
Or Me.txtReplace = "blank" Or Me.txtReplace = "Empty" Or txtReplace =
"IsNull" Or txtReplace = "Is Null" Or txtReplace = "null" Then
DoCmd.RunSQL "UPDATE [" & tblName & "]SET [" & tblName & "].[" & colName & "]
= """ & emptyfield & """
WHERE ([" & tblName & "].[" & colName & "]) = """ & Me.txtFind & """ "
Else
DoCmd.RunSQL "UPDATE [" & tblName & "]SET [" & tblName & "].[" & colName & "]
= """ & Me.txtReplace & """ WHERE ([" & tblName & "].[" & colName & "]) =
""" & Me.txtFind & """ "
End If
Exit_cmdFind_Click:
Exit Sub
Err_cmdFind_Click:
MsgBox Err.Description
MsgBox "There is a problem with your entry into the Find field or your
Replace field. Please try again.", vbCritical, "Find/Replace Error"
Resume Exit_cmdFind_Click
End Sub
I am rying to recreate the Find/Replace wizard for a person who needs to be
able to find blank fields within a table and change then to the new data. As
far as I know, you can not o this with the built in Access find/replace -
correct?
Anyway, I built a form that works fine for finding and replacing table fields
containing text data by using an update statement. However, when I try it
with a numerical table field or a yes/no table field, it always gives me the
"Data Type mismatch" error. I tried changing the table field obtained from
the user form to a variant before running through the update query, however,
it works sometimes, and other times it asks for a parameter.
I am guessing my best option is to capture the data type of the table field
and then have different update statements for each data type. This is where
I am running into a problem. Honestly, I can't figure out how to capture the
datatype - not even sure where to start. I did see one posting here for
capture all the data types in a table through a loop, however, I only want to
capture the data type in my find field on my form. Can someone please help
me. I thought that this form would be simple, forgetting all the different
data types that the person could try to change in the table would make it a
bit nore complicated. Here is a copy of my code. If you know a better way
to do this, please advise.
Thank you
*********************************************************************************
Dim tblName As String
Dim colName As String
Dim findfield As Variant
Dim repfield As Variant
Dim tblColumnName As String
Dim emptyfield As String
tblName = Me.cboGettable
colName = Me.cboField
findfield = Me.txtFind
repfield = Me.txtReplace
emptyfield = ""
If Me.txtFind = "" Or IsNull(Me.txtFind) Or Me.txtFind = "Blank" Or Me.
txtFind = "Empty" Or Me.txtFind = "IsNull"
Or Me.txtFind = "Is Null" Or Me.txtFind = "Null" Or Me.txtFind = "null" Or Me.
txtFind = "blank" Then
DoCmd.RunSQL "UPDATE [" & tblName & "]SET [" & tblName & "].[" & colName & "]
= """ & Me.txtReplace & """
WHERE (isNull([" & tblName & "].[" & colName & "])) or ([" & tblName & "].["
& colName & "])=""" & emptyfield
& """ "
ElseIf Me.txtReplace = "" Or IsNull(Me.txtReplace) Or Me.txtReplace = "Blank"
Or Me.txtReplace = "blank" Or Me.txtReplace = "Empty" Or txtReplace =
"IsNull" Or txtReplace = "Is Null" Or txtReplace = "null" Then
DoCmd.RunSQL "UPDATE [" & tblName & "]SET [" & tblName & "].[" & colName & "]
= """ & emptyfield & """
WHERE ([" & tblName & "].[" & colName & "]) = """ & Me.txtFind & """ "
Else
DoCmd.RunSQL "UPDATE [" & tblName & "]SET [" & tblName & "].[" & colName & "]
= """ & Me.txtReplace & """ WHERE ([" & tblName & "].[" & colName & "]) =
""" & Me.txtFind & """ "
End If
Exit_cmdFind_Click:
Exit Sub
Err_cmdFind_Click:
MsgBox Err.Description
MsgBox "There is a problem with your entry into the Find field or your
Replace field. Please try again.", vbCritical, "Find/Replace Error"
Resume Exit_cmdFind_Click
End Sub