Searching a value in a double field 
Author Message
 Searching a value in a double field
Hello,

Can a field type double in Access 2002 be searched on
using ADO. I have several tables that use the field type
double as an Indexed number, but I can't retrieve any
information when I search on the double value. I even used
the query builder to retrieve the fields, but it always
returns an empty recordset. I read that there was a
problem with the double field and Oracle and that the
field would have to converted to text. Does this problem
also exist within Access.

Thank you.



Fri, 01 Jul 2005 21:04:38 GMT
 Searching a value in a double field

Val,

I have a public function and a class.

Public Function Fill_in_IRB(ByVal ID As Double, ByVal FY
As Integer)
    Dim lookup As New [Check Data]
    Dim cmdSQL As String
    Dim strRecordSource As String
    strRecordSource = "SELECT * FROM internal_review_board
WHERE IRB_ID =" & ID & " AND FY=" & FY & ";"
    If lookup.CheckRecords(strRecordSource, ID) = True Then
        DoCmd.OpenForm "dat_irb", acNormal
    End If
End Function

--CLASS--

Public Function CheckRecords(strSQL As String, ByVal ID As
Double) As Boolean
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Const strProvider = "Provider=Microsoft.JET.OLEDB.4.0;
Data
Source=k:\extramural\program\database\brandon\erms_be.mdb"

    cn.Open strProvider
    rs.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic

    If rs.EOF = True Then
        CheckRecords = True
        rs.AddNew
        rs.Fields("IRB_ID") = ID
        rs.Update
   Else
        CheckRecords = False
    End If

    rs.Close
    cn.Close

    Set rs = Nothing
    Set cn = Nothing
End Function

What I don't understand, is that when I copied the number
and pasted it into the criteria field in the query builder
I still could not get the records that had that number.

Thank you,

Quote:
>-----Original Message-----
>Hi Brandon,

>How do you provide search? Could you post your code here,
please?

>--
>Val Mazur
>Microsoft MVP



>> Hello,

>> Can a field type double in Access 2002 be searched on
>> using ADO. I have several tables that use the field type
>> double as an Indexed number, but I can't retrieve any
>> information when I search on the double value. I even
used
>> the query builder to retrieve the fields, but it always
>> returns an empty recordset. I read that there was a
>> problem with the double field and Oracle and that the
>> field would have to converted to text. Does this problem
>> also exist within Access.

>> Thank you.

>.



Fri, 01 Jul 2005 21:31:36 GMT
 Searching a value in a double field

Hi Brandon,

How do you provide search? Could you post your code here, please?

--
Val Mazur
Microsoft MVP


Quote:
> Hello,

> Can a field type double in Access 2002 be searched on
> using ADO. I have several tables that use the field type
> double as an Indexed number, but I can't retrieve any
> information when I search on the double value. I even used
> the query builder to retrieve the fields, but it always
> returns an empty recordset. I read that there was a
> problem with the double field and Oracle and that the
> field would have to converted to text. Does this problem
> also exist within Access.

> Thank you.



Fri, 01 Jul 2005 21:14:02 GMT
 Searching a value in a double field

Brandod,

Code looks fine, except remove New keyword from declaration part of ADO
connection and recordset. It could lead to some memory leaking problems. It
should be

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

About problem in SQL statement. Does it happen only with doubles? What is
exact type of that field? What decimal separator do you have in
International settings of your PC?

--
Val Mazur
Microsoft MVP


Quote:
> Val,

> I have a public function and a class.

> Public Function Fill_in_IRB(ByVal ID As Double, ByVal FY
> As Integer)
>     Dim lookup As New [Check Data]
>     Dim cmdSQL As String
>     Dim strRecordSource As String
>     strRecordSource = "SELECT * FROM internal_review_board
> WHERE IRB_ID =" & ID & " AND FY=" & FY & ";"
>     If lookup.CheckRecords(strRecordSource, ID) = True Then
>         DoCmd.OpenForm "dat_irb", acNormal
>     End If
> End Function

> --CLASS--

> Public Function CheckRecords(strSQL As String, ByVal ID As
> Double) As Boolean
>     Dim cn As New ADODB.Connection
>     Dim rs As New ADODB.Recordset
>     Const strProvider = "Provider=Microsoft.JET.OLEDB.4.0;
> Data
> Source=k:\extramural\program\database\brandon\erms_be.mdb"

>     cn.Open strProvider
>     rs.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic

>     If rs.EOF = True Then
>         CheckRecords = True
>         rs.AddNew
>         rs.Fields("IRB_ID") = ID
>         rs.Update
>    Else
>         CheckRecords = False
>     End If

>     rs.Close
>     cn.Close

>     Set rs = Nothing
>     Set cn = Nothing
> End Function

> What I don't understand, is that when I copied the number
> and pasted it into the criteria field in the query builder
> I still could not get the records that had that number.

> Thank you,

> >-----Original Message-----
> >Hi Brandon,

> >How do you provide search? Could you post your code here,
> please?

> >--
> >Val Mazur
> >Microsoft MVP



> >> Hello,

> >> Can a field type double in Access 2002 be searched on
> >> using ADO. I have several tables that use the field type
> >> double as an Indexed number, but I can't retrieve any
> >> information when I search on the double value. I even
> used
> >> the query builder to retrieve the fields, but it always
> >> returns an empty recordset. I read that there was a
> >> problem with the double field and Oracle and that the
> >> field would have to converted to text. Does this problem
> >> also exist within Access.

> >> Thank you.

> >.



Fri, 01 Jul 2005 21:39:32 GMT
 Searching a value in a double field

Hi Brandon

What # are you searching for? If it's 2.0 for example, Try

"SELECT * FROM internal_review_board  WHERE IRB_ID < 2.1 AND IRB_ID > 1.9  
AND FY=" & FY & ";"

You get the idea anyway, I'm not a VB guy. Search for a range, not an
absolute value.

I hope this helps,

Russ Gray
Microsoft Developer Support

This posting is provided AS IS with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.



Sat, 02 Jul 2005 09:00:33 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Searching for values in a double field

2. how to search for the same value in several fields

3. Searching fields containing comma separated values

4. how do I look in one field for a truncated value, then search

5. Search on three value list fields

6. Entering search values in to calulated fields with IWP

7. search on value list field

8. Searching for a NULL value in a field

9. Requiring fields values in searches

10. Again: Field Value search

11. Searching for field value through a script

12. field value search


 
Powered by phpBB® Forum Software