Data Type problem

M

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!
 
P

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.)
 
M

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?)
 
P

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?
 
F

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'"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top