|  | Data Type problem | | |
| | | magmike |  |
|  Posted: Wed Sep 03, 2008 3:26 am Post subject: Data Type problem |  |
| |  | |
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@hotmail.com | |
| | Posted: Wed Sep 03, 2008 3:31 am Post subject: Re: Data Type problem | |
On Sep 2, 10:26 pm, magmike <magmi...@yahoo.com> wrote:
| Quote: | 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.) |
| |
| | | fredg | |
| | Posted: Wed Sep 03, 2008 3:50 am Post subject: Re: Data Type problem | |
| | | |
On Tue, 2 Sep 2008 20:26:17 -0700 (PDT), magmike wrote:
| Quote: | 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'"
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail |
| |
| | | magmike | |
| | Posted: Wed Sep 03, 2008 4:31 am Post subject: Re: Data Type problem | |
On Sep 2, 10:31 pm, "pietlin...@hotmail.com" <pietlin...@hotmail.com>
wrote:
| Quote: | On Sep 2, 10:26 pm, magmike <magmi...@yahoo.com> wrote:
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@hotmail.com | |
| | Posted: Wed Sep 03, 2008 4:40 am Post subject: Re: Data Type problem | |
| | | |
On Sep 2, 11:31 pm, magmike <magmi...@yahoo.com> wrote:
| Quote: | On Sep 2, 10:31 pm, "pietlin...@hotmail.com" <pietlin...@hotmail.com
wrote:
On Sep 2, 10:26 pm, magmike <magmi...@yahoo.com> wrote:
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? |
| |
|
|
