text box number help

[Chad]

Hello, I have a text box fomated to currency with a decimal of 2. If I enter
lets say $4.12 and when I go back to the text box to change it, I click on
the text box and it shows a number 4.11999988555908 instead of the 4.12. This
long number only shows when I click in the text box but it shows correctly if
just viewed. How do I get rid of all the extra numbers? Or can I set it to
standard with 2 decimals and have it show a $ sign at the begining with code?
If I use code to show the $ sign what and where would I put it? Thanks...

 

[Jerry Whittle]

The simple fix is to change the data type from number to currency at the
table. Not "formatted" as currency, but the data type set to Currency.

 

[Chad]

Ok, I did what you said and its still coming up with the long number?

 

[Chad]

Sorry Jerry disreguard my last post! I didnt set the data type on one field
to currency. I have another querstion. Im using a search in mt switchboard
and everything works great when I type in a number lets say 1.00 then I get
no records found (But there are) then if I type in 1.19 then it brings up a
record. What im saying is it wont find numbers that are like 1.00 2.00 3.00
but it will find numbers if the 00 was like 17 or 12 ect. What am I doing
wrong in my code for the search for it to be doing this? PurchaseCose and
Price are the ones doing this. Thanks!

Private Sub cmdSearch_Click()
If IsNull(Me.txtSearch) Then
'show all records
DoCmd.OpenForm "frmRBackyardMain"
Else
On Error Resume Next
'filter for keyword
DoCmd.OpenForm "frmRBackyardMain", , , "[Season] Like '*" &
Me.txtSearch & "*' " _
& " OR [Vendor] Like '*" & Me.txtSearch & "*' " _
& " OR [Description] Like '*" & Me.txtSearch & "*' " _
& " OR [Z554] Like '*" & Me.txtSearch & "*' " _
& " OR [OrderNumber] Like '*" & Me.txtSearch & "*' " _
& " OR [PurchaseCost] Like '*" & Me.txtSearch & "*' " _
& " OR [Price] Like '*" & Me.txtSearch & "*' "

End If

End Sub

 

[John Spencer]

The problem is caused by your use of the LIKE operator when you should be
using equals on the number fields.

Using like on a number field will cause Access to convert the number to a
string. If the number is a whole number with no decimal portion, then
Access will not append the trailing zeroes.

Your other option if you really want to continue to use LIKE would be to use
the format function to force the decimals

"OR Format([PurchaseCost],""0.00"") Like '*" & Me.txtSearch & "*' "

You really should use
"OR [PurchaseCost] = " & Me.txtSearch
But that is likely to fail since txtSearch can be a non-numeric value.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Chad]

John, I get an:
Compile Error: Expected: list separator or)
and it outlines the 0.00

Thanks!
--
Newbies need extra loven.........

The problem is caused by your use of the LIKE operator when you should be
using equals on the number fields.

Using like on a number field will cause Access to convert the number to a
string. If the number is a whole number with no decimal portion, then
Access will not append the trailing zeroes.

Your other option if you really want to continue to use LIKE would be to use
the format function to force the decimals

"OR Format([PurchaseCost],""0.00"") Like '*" & Me.txtSearch & "*' "

You really should use
"OR [PurchaseCost] = " & Me.txtSearch
But that is likely to fail since txtSearch can be a non-numeric value.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Sorry Jerry disreguard my last post! I didnt set the data type on one
field
to currency. I have another querstion. Im using a search in mt switchboard
and everything works great when I type in a number lets say 1.00 then I
get
no records found (But there are) then if I type in 1.19 then it brings up
a
record. What im saying is it wont find numbers that are like 1.00 2.00
3.00
but it will find numbers if the 00 was like 17 or 12 ect. What am I doing
wrong in my code for the search for it to be doing this? PurchaseCose and
Price are the ones doing this. Thanks!

Private Sub cmdSearch_Click()
If IsNull(Me.txtSearch) Then
'show all records
DoCmd.OpenForm "frmRBackyardMain"
Else
On Error Resume Next
'filter for keyword
DoCmd.OpenForm "frmRBackyardMain", , , "[Season] Like '*" &
Me.txtSearch & "*' " _
& " OR [Vendor] Like '*" & Me.txtSearch & "*' " _
& " OR [Description] Like '*" & Me.txtSearch & "*' " _
& " OR [Z554] Like '*" & Me.txtSearch & "*' " _
& " OR [OrderNumber] Like '*" & Me.txtSearch & "*' " _
& " OR [PurchaseCost] Like '*" & Me.txtSearch & "*' " _
& " OR [Price] Like '*" & Me.txtSearch & "*' "

End If

End Sub

 

[Douglas J. Steele]

Did you put two double quotes in front, and two double quotes after 0.00,
like John had?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

John, I get an:
Compile Error: Expected: list separator or)
and it outlines the 0.00

Thanks!
--
Newbies need extra loven.........

The problem is caused by your use of the LIKE operator when you should be
using equals on the number fields.

Using like on a number field will cause Access to convert the number to a
string. If the number is a whole number with no decimal portion, then
Access will not append the trailing zeroes.

Your other option if you really want to continue to use LIKE would be to
use
the format function to force the decimals

"OR Format([PurchaseCost],""0.00"") Like '*" & Me.txtSearch & "*' "

You really should use
"OR [PurchaseCost] = " & Me.txtSearch
But that is likely to fail since txtSearch can be a non-numeric value.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Sorry Jerry disreguard my last post! I didnt set the data type on one
field
to currency. I have another querstion. Im using a search in mt
switchboard
and everything works great when I type in a number lets say 1.00 then I
get
no records found (But there are) then if I type in 1.19 then it brings
up
a
record. What im saying is it wont find numbers that are like 1.00 2.00
3.00
but it will find numbers if the 00 was like 17 or 12 ect. What am I
doing
wrong in my code for the search for it to be doing this? PurchaseCose
and
Price are the ones doing this. Thanks!

Private Sub cmdSearch_Click()
If IsNull(Me.txtSearch) Then
'show all records
DoCmd.OpenForm "frmRBackyardMain"
Else
On Error Resume Next
'filter for keyword
DoCmd.OpenForm "frmRBackyardMain", , , "[Season] Like '*" &
Me.txtSearch & "*' " _
& " OR [Vendor] Like '*" & Me.txtSearch & "*' " _
& " OR [Description] Like '*" & Me.txtSearch & "*' " _
& " OR [Z554] Like '*" & Me.txtSearch & "*' " _
& " OR [OrderNumber] Like '*" & Me.txtSearch & "*' " _
& " OR [PurchaseCost] Like '*" & Me.txtSearch & "*' " _
& " OR [Price] Like '*" & Me.txtSearch & "*' "

End If

End Sub

 

[Chad]

I looked it over and I typed "*" instead of "*'
It works great! Thanks everyone for the help!

--
Newbies need extra loven.........

Did you put two double quotes in front, and two double quotes after 0.00,
like John had?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

John, I get an:
Compile Error: Expected: list separator or)
and it outlines the 0.00

Thanks!
--
Newbies need extra loven.........

The problem is caused by your use of the LIKE operator when you should be
using equals on the number fields.

Using like on a number field will cause Access to convert the number to a
string. If the number is a whole number with no decimal portion, then
Access will not append the trailing zeroes.

Your other option if you really want to continue to use LIKE would be to
use
the format function to force the decimals

"OR Format([PurchaseCost],""0.00"") Like '*" & Me.txtSearch & "*' "

You really should use
"OR [PurchaseCost] = " & Me.txtSearch
But that is likely to fail since txtSearch can be a non-numeric value.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Sorry Jerry disreguard my last post! I didnt set the data type on one
field
to currency. I have another querstion. Im using a search in mt
switchboard
and everything works great when I type in a number lets say 1.00 then I
get
no records found (But there are) then if I type in 1.19 then it brings
up
a
record. What im saying is it wont find numbers that are like 1.00 2.00
3.00
but it will find numbers if the 00 was like 17 or 12 ect. What am I
doing
wrong in my code for the search for it to be doing this? PurchaseCose
and
Price are the ones doing this. Thanks!

Private Sub cmdSearch_Click()
If IsNull(Me.txtSearch) Then
'show all records
DoCmd.OpenForm "frmRBackyardMain"
Else
On Error Resume Next
'filter for keyword
DoCmd.OpenForm "frmRBackyardMain", , , "[Season] Like '*" &
Me.txtSearch & "*' " _
& " OR [Vendor] Like '*" & Me.txtSearch & "*' " _
& " OR [Description] Like '*" & Me.txtSearch & "*' " _
& " OR [Z554] Like '*" & Me.txtSearch & "*' " _
& " OR [OrderNumber] Like '*" & Me.txtSearch & "*' " _
& " OR [PurchaseCost] Like '*" & Me.txtSearch & "*' " _
& " OR [Price] Like '*" & Me.txtSearch & "*' "

End If

End Sub