Data Type problem

[magmike]

First thing - I did not create this database! I was however, asked by
my boss to add a search form to it. Here are the details.

The key field is the social security number field. It is set as text.
I am having conflict issues when searching. When the button code below
is run, I get a Data Type error.

There are two forms, [Form1 - MAIN], and [Search]. Search is a
continuous form. From the Search form, a user can select the Go!
button next to a record and it is supposed to find the record in the
first form. Here is the code:

Private Sub Command63_Click()
On Error GoTo Err_Command63_Click

Dim rs As Object
Dim stDocName As String

stDocName = "Form1 - MAIN"
DoCmd.OpenForm stDocName

Set rs = Forms![Form1 - MAIN].Recordset.Clone
rs.FindFirst "[SSNo] = " & Str(Nz(Me![SSNo], 0))
If Not rs.EOF Then Forms![Form1 - MAIN].Bookmark = rs.Bookmark

Forms!search.Visible = False

Exit_Command63_Click:
Exit Sub

Err_Command63_Click:
MsgBox Err.Description
Resume Exit_Command63_Click
End Sub

If I change SSNo to a number field, the above code works fine, however
the social security number "003-55-5555" ends up being stored and
displayed as "3-55-5555" which of course is not cool. Is there a way
to change the above code to represent the results as text, or is there
a way to make the SSNo a number, but display and store properly when
the number begins with a zero?

PS: SSNo stores the data without the mask, in case that is important.

Thanks in advance for your help!

 

[pietlinden]

First thing - I did not create this database! I was however, asked by
my boss to add a search form to it. Here are the details.

The key field is the social security number field. It is set as text.
I am having conflict issues when searching. When the button code below
is run, I get a Data Type error.

There are two forms, [Form1 - MAIN], and [Search]. Search is a
continuous form. From the Search form, a user can select the Go!
button next to a record and it is supposed to find the record in the
first form. Here is the code:


   
    rs.FindFirst "[SSNo] = " & Str(Nz(Me![SSNo], 0))

should be rs.FindFirst "[SSNo] = ' " & Me![SSNo] & " ' "

(but without the extra spaces around the single quotes.)

 

[magmike]

First thing - I did not create this database! I was however, asked by
my boss to add a search form to it. Here are the details.

The key field is the social security number field. It is set as text.
I am having conflict issues when searching. When the button code below
is run, I get a Data Type error.

There are two forms, [Form1 - MAIN], and [Search]. Search is a
continuous form. From the Search form, a user can select the Go!
button next to a record and it is supposed to find the record in the
first form. Here is the code:

   
    rs.FindFirst "[SSNo] = " & Str(Nz(Me![SSNo], 0))

should be rs.FindFirst "[SSNo] = ' " & Me![SSNo] & " ' "

(but without the extra spaces around the single quotes.)

That was easy!
(am I at Staples?)

 

[pietlinden]

First thing - I did not create this database! I was however, asked by
my boss to add a search form to it. Here are the details.
The key field is the social security number field. It is set as text.
I am having conflict issues when searching. When the button code below
is run, I get a Data Type error.
There are two forms, [Form1 - MAIN], and [Search]. Search is a
continuous form. From the Search form, a user can select the Go!
button next to a record and it is supposed to find the record in the
first form. Here is the code:
   
    rs.FindFirst "[SSNo] = " & Str(Nz(Me![SSNo], 0))

should be rs.FindFirst "[SSNo] = ' " & Me![SSNo] & " ' "

(but without the extra spaces around the single quotes.)

That was easy!
(am I at Staples?)

not sure... did you see the big red button?

 

[fredg]

First thing - I did not create this database! I was however, asked by
my boss to add a search form to it. Here are the details.

The key field is the social security number field. It is set as text.
I am having conflict issues when searching. When the button code below
is run, I get a Data Type error.

There are two forms, [Form1 - MAIN], and [Search]. Search is a
continuous form. From the Search form, a user can select the Go!
button next to a record and it is supposed to find the record in the
first form. Here is the code:

Private Sub Command63_Click()
On Error GoTo Err_Command63_Click

Dim rs As Object
Dim stDocName As String

stDocName = "Form1 - MAIN"
DoCmd.OpenForm stDocName

Set rs = Forms![Form1 - MAIN].Recordset.Clone
rs.FindFirst "[SSNo] = " & Str(Nz(Me![SSNo], 0))
If Not rs.EOF Then Forms![Form1 - MAIN].Bookmark = rs.Bookmark

Forms!search.Visible = False

Exit_Command63_Click:
Exit Sub

Err_Command63_Click:
MsgBox Err.Description
Resume Exit_Command63_Click
End Sub

If I change SSNo to a number field, the above code works fine, however
the social security number "003-55-5555" ends up being stored and
displayed as "3-55-5555" which of course is not cool. Is there a way
to change the above code to represent the results as text, or is there
a way to make the SSNo a number, but display and store properly when
the number begins with a zero?

PS: SSNo stores the data without the mask, in case that is important.

Thanks in advance for your help!

You a misstatement of fact here.
1) A Number datatype field cannot store a number with the hyphens as
you show them. 003-55-5555 cannot be a number datatype (even though
numbers make up the data.) What you are seeing is most probably the
effects of an input mask on the data, while the actual stored value
(as a number datatype) is 3555555.

2) The SSN field should be a Text datatype, in which case you must
surround the text value with single quotes.
Try:
rs.FindFirst "[SSNo] = '" & Str(Nz(Me![SSNo], 0) & "'"

which will evaluate to
rs.FindFirst "[SSNo] = '003-55-5555'"