Capture Data Type

[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

 

[Allen Browne]

You can read the data type of the Field in the RecordsetClone of the form,
e.g.:
Me.RecordsetClone.Fields("MyField").Type

The values will be one of the type DataTypeEnum values. You can see them in
the object browser (F2 in a code window), or use the middle column from this
reference table:
http://allenbrowne.com/ser-49.html
or the FieldTypeName() function from this page:
http://allenbrowne.com/func-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[DMUM via AccessMonster.com]

thank you for responding, but sorry, I still can't figure out how to just
identify the data type of the one field. The TableInfo()function loops
through each field in the table - I don't want to do this. The first
suggestion would probably work, but I have not used cloned recordsets yet, so
I am at a loss as to how to set it up.

Basicly I want the user to enter the field name they want to find the data
they want to change, then on click, have the code identify what data type the
data field is and then process the update statement applicable to the data
type. If the data type is text I have to use three quotes around the
variable, i.e """ & Me.txtFind & """, if it is numerical I need to use single
quotes or one quote ( I think) - i.e ' & Me.txtFind & ' etc.

Can you please provide more info. I tried to figure it out myself, but I
keep running into a wall.

Thank you

You can read the data type of the Field in the RecordsetClone of the form,
e.g.:
Me.RecordsetClone.Fields("MyField").Type

The values will be one of the type DataTypeEnum values. You can see them in
the object browser (F2 in a code window), or use the middle column from this
reference table:
http://allenbrowne.com/ser-49.html
or the FieldTypeName() function from this page:
http://allenbrowne.com/func-06.html

Hello

[quoted text clipped - 90 lines]

End Sub

 

[Allen Browne]

Assuming you have a bound form, Access gives you a RecordsetClone. There's
nothing you need to do do get one. The single line will give you the type.

So, if you have an unbound combo named cboFilterField, with its
RowSourceType set to FieldList so that it gives you a list of fields, you
can determine the delimiter you need like this:

Dim strDelim As String
If Not IsNull(cboFilterField) Then
Select Case Me.RecordsetClone.Fields(Me.cboFilterField).Type
Case dbText, dbMemo
strDelim = """"
Case dbText
strDelim = "#"
End Select
End If

If you really want to get it from the table instead:
CurrentDb.TableDefs("Table1").Fields("Field1").Type

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

thank you for responding, but sorry, I still can't figure out how to just
identify the data type of the one field. The TableInfo()function loops
through each field in the table - I don't want to do this. The first
suggestion would probably work, but I have not used cloned recordsets yet,
so
I am at a loss as to how to set it up.

Basicly I want the user to enter the field name they want to find the data
they want to change, then on click, have the code identify what data type
the
data field is and then process the update statement applicable to the data
type. If the data type is text I have to use three quotes around the
variable, i.e """ & Me.txtFind & """, if it is numerical I need to use
single
quotes or one quote ( I think) - i.e ' & Me.txtFind & ' etc.

Can you please provide more info. I tried to figure it out myself, but I
keep running into a wall.

Thank you

You can read the data type of the Field in the RecordsetClone of the form,
e.g.:
Me.RecordsetClone.Fields("MyField").Type

The values will be one of the type DataTypeEnum values. You can see them
in
the object browser (F2 in a code window), or use the middle column from
this
reference table:
http://allenbrowne.com/ser-49.html
or the FieldTypeName() function from this page:
http://allenbrowne.com/func-06.html

Hello

[quoted text clipped - 90 lines]

End Sub

 

[DMUM via AccessMonster.com]

When I attempt to use your first option I get an error which states:

I don't have a bound form. The fields are populated after the user chooses a
table to search and then a field the table to search.

I did try the second example. I keep getting an error on the .Type

This gives me a compile error - invalid use of property.

Must the form be a bound form in order to use this as well?

Any other oprtions. It doesn't seem like it should be this difficult to get
the data type.

Thank you

Assuming you have a bound form, Access gives you a RecordsetClone. There's
nothing you need to do do get one. The single line will give you the type.

So, if you have an unbound combo named cboFilterField, with its
RowSourceType set to FieldList so that it gives you a list of fields, you
can determine the delimiter you need like this:

Dim strDelim As String
If Not IsNull(cboFilterField) Then
Select Case Me.RecordsetClone.Fields(Me.cboFilterField).Type
Case dbText, dbMemo
strDelim = """"
Case dbText
strDelim = "#"
End Select
End If

If you really want to get it from the table instead:
CurrentDb.TableDefs("Table1").Fields("Field1").Type

thank you for responding, but sorry, I still can't figure out how to just
identify the data type of the one field. The TableInfo()function loops

[quoted text clipped - 35 lines]

 

[Allen Browne]

Yes, the form must be bound for it to have a RecordsetClone.

The 2nd example works here. You may need a reference to the DAO library, as
described here:
http://allenbrowne.com/ser-38.html
and you do need to use the actual names of your table and field in the
quotes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

When I attempt to use your first option I get an error which states:

I don't have a bound form. The fields are populated after the user
chooses a
table to search and then a field the table to search.

I did try the second example. I keep getting an error on the .Type

This gives me a compile error - invalid use of property.

Must the form be a bound form in order to use this as well?

Any other oprtions. It doesn't seem like it should be this difficult to
get
the data type.

Thank you

Assuming you have a bound form, Access gives you a RecordsetClone. There's
nothing you need to do do get one. The single line will give you the type.

So, if you have an unbound combo named cboFilterField, with its
RowSourceType set to FieldList so that it gives you a list of fields, you
can determine the delimiter you need like this:

Dim strDelim As String
If Not IsNull(cboFilterField) Then
Select Case Me.RecordsetClone.Fields(Me.cboFilterField).Type
Case dbText, dbMemo
strDelim = """"
Case dbText
strDelim = "#"
End Select
End If

If you really want to get it from the table instead:
CurrentDb.TableDefs("Table1").Fields("Field1").Type

thank you for responding, but sorry, I still can't figure out how to
just
identify the data type of the one field. The TableInfo()function loops

[quoted text clipped - 35 lines]

End Sub

 

[DMUM via AccessMonster.com]

The DAO statement is already refereneced in my code. Unfortuneatly, becuase
I am creating a dynamic update where the user can choose the table and the
field to be updated, I can't use this code. There has to be a way to do this
since Access's find and replace form does this. Please help!

Yes, the form must be bound for it to have a RecordsetClone.

The 2nd example works here. You may need a reference to the DAO library, as
described here:
http://allenbrowne.com/ser-38.html
and you do need to use the actual names of your table and field in the
quotes.

When I attempt to use your first option I get an error which states:

[quoted text clipped - 40 lines]

 

[Allen Browne]

I don't understand why you can't concatenate the names of your table and
field into the string, but search for other alternatives if you prefer.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The DAO statement is already refereneced in my code. Unfortuneatly,
becuase
I am creating a dynamic update where the user can choose the table and the
field to be updated, I can't use this code. There has to be a way to do
this
since Access's find and replace form does this. Please help!

Yes, the form must be bound for it to have a RecordsetClone.

The 2nd example works here. You may need a reference to the DAO library,
as
described here:
http://allenbrowne.com/ser-38.html
and you do need to use the actual names of your table and field in the
quotes.

When I attempt to use your first option I get an error which states:

[quoted text clipped - 40 lines]

End Sub

 

[DMUM via AccessMonster.com]

Not sure what you mean here. I finally got it to work, but if you wouldn't
mind I would still like to know what you mean either for future reference or
it still may be better then what I came up with. I still have the challenge
of building all the if statments to run depending upon the data type the user
wants to update. Not really looking forward to it since i thought this would
be a simple feat.

Thanks

I don't understand why you can't concatenate the names of your table and
field into the string, but search for other alternatives if you prefer.

The DAO statement is already refereneced in my code. Unfortuneatly,
becuase

[quoted text clipped - 17 lines]