convert text field to numeric field being used in Selection Formula 
Author Message
 convert text field to numeric field being used in Selection Formula

I'm using VB6.0 SP4 and CR 7.0.
Problem:
Invoice# was defined as an alphanumeric field. When users enter a range of
invoice#, say 10001 - 14000, this would include all invoices with #1100 -
1399! Is there anyway to fix this problem without changing Invoice# data
type(in MS Access) to numeric? For example, can I modify the following to
make it work?

If Len(Trim(strSelection)) > 0 Then
        strSelection = strSelection & " and "
    End If
    strSelection = strSelection & "{fr_invoice1.invoice_no} >= " & "'" &
Trim(txtInvFrom.Text) & "'" & _
     " and {fr_invoice1.invoice_no} <= " & "'" & Trim(txtInvTo.Text) & "'"

Thanks
Bill



Sat, 30 Aug 2003 07:44:31 GMT  
 convert text field to numeric field being used in Selection Formula
Hi Bill,
    I don't know if this will help, but CR7 has a built-in function called
"tonumber()" that will treat numeric text strings as numbers.  If you can
create a formula field that uses "tonumber(invoice#)" and use this instead
of the actual invoice #, you should be able to see only those invoices in
the desired range.

Bob Holmes


Quote:
> I'm using VB6.0 SP4 and CR 7.0.
> Problem:
> Invoice# was defined as an alphanumeric field. When users enter a range of
> invoice#, say 10001 - 14000, this would include all invoices with #1100 -
> 1399! Is there anyway to fix this problem without changing Invoice# data
> type(in MS Access) to numeric? For example, can I modify the following to
> make it work?

> If Len(Trim(strSelection)) > 0 Then
>         strSelection = strSelection & " and "
>     End If
>     strSelection = strSelection & "{fr_invoice1.invoice_no} >= " & "'" &
> Trim(txtInvFrom.Text) & "'" & _
>      " and {fr_invoice1.invoice_no} <= " & "'" & Trim(txtInvTo.Text) & "'"

> Thanks
> Bill



Sat, 30 Aug 2003 23:41:43 GMT  
 convert text field to numeric field being used in Selection Formula
Hi Bob;
Assuming that this will work, what's the syntax for the .Selection clause in
VB? It's currently pointing to a datasource column, not a formula field.
Thanks
Bill

Quote:

> Hi Bill,
>     I don't know if this will help, but CR7 has a built-in function called
> "tonumber()" that will treat numeric text strings as numbers.  If you can
> create a formula field that uses "tonumber(invoice#)" and use this instead
> of the actual invoice #, you should be able to see only those invoices in
> the desired range.

> Bob Holmes



> > I'm using VB6.0 SP4 and CR 7.0.
> > Problem:
> > Invoice# was defined as an alphanumeric field. When users enter a range
of
> > invoice#, say 10001 - 14000, this would include all invoices with
#1100 -
> > 1399! Is there anyway to fix this problem without changing Invoice# data
> > type(in MS Access) to numeric? For example, can I modify the following
to
> > make it work?

> > If Len(Trim(strSelection)) > 0 Then
> >         strSelection = strSelection & " and "
> >     End If
> >     strSelection = strSelection & "{fr_invoice1.invoice_no} >= " & "'" &
> > Trim(txtInvFrom.Text) & "'" & _
> >      " and {fr_invoice1.invoice_no} <= " & "'" & Trim(txtInvTo.Text) &
"'"

> > Thanks
> > Bill



Sun, 31 Aug 2003 04:10:01 GMT  
 convert text field to numeric field being used in Selection Formula
My experience is with CR and not really with controlling CR from VB.  I
believe that what you need to do is phrase the "Where" clause of your select
statement to read like:
        Where tonumber(invoice#) in 10001 to 14000  or

        (Where tonumber(invoice#) >= 10001 and tonumber(invoice#) <= 14000)

If this doesn't restrict the data entering the report, you can use the
formula field to sort on and suppress records outside the range

Bob


Quote:
> Hi Bob;
> Assuming that this will work, what's the syntax for the .Selection clause
in
> VB? It's currently pointing to a datasource column, not a formula field.
> Thanks
> Bill


> > Hi Bill,
> >     I don't know if this will help, but CR7 has a built-in function
called
> > "tonumber()" that will treat numeric text strings as numbers.  If you
can
> > create a formula field that uses "tonumber(invoice#)" and use this
instead
> > of the actual invoice #, you should be able to see only those invoices
in
> > the desired range.

> > Bob Holmes



> > > I'm using VB6.0 SP4 and CR 7.0.
> > > Problem:
> > > Invoice# was defined as an alphanumeric field. When users enter a
range
> of
> > > invoice#, say 10001 - 14000, this would include all invoices with
> #1100 -
> > > 1399! Is there anyway to fix this problem without changing Invoice#
data
> > > type(in MS Access) to numeric? For example, can I modify the following
> to
> > > make it work?

> > > If Len(Trim(strSelection)) > 0 Then
> > >         strSelection = strSelection & " and "
> > >     End If
> > >     strSelection = strSelection & "{fr_invoice1.invoice_no} >= " & "'"
&
> > > Trim(txtInvFrom.Text) & "'" & _
> > >      " and {fr_invoice1.invoice_no} <= " & "'" & Trim(txtInvTo.Text) &
> "'"

> > > Thanks
> > > Bill



Sun, 31 Aug 2003 04:54:32 GMT  
 convert text field to numeric field being used in Selection Formula
Bob,
I tried it, and it worked beautifully.
Following is the modified .Selection Formula from VB:

If Len(Trim(strSelection)) > 0 Then
        strSelection = strSelection & " and "
    End If
'----------- Old syntax
   ' strSelection = strSelection & "{fr_invoice1.invoice_no} >= " & "'" &
Trim(txtInvFrom.Text) & "'" & _
    ' " and {fr_invoice1.invoice_no} <= " & "'" & Trim(txtInvTo.Text) & "'"
' -------------- New syntax:
      strSelection = strSelection & _
      "tonumber({fr_invoice1.invoice_no}) >= " & Trim(txtInvFrom.Text) & _
     " and tonumber({fr_invoice1.invoice_no}) <= " & Trim(txtInvTo.Text)

' Note that single quotes "'" were removed since txtInvFrom & txtInvTo
fields are now numeric.

Thanks again
Bill

Quote:

> My experience is with CR and not really with controlling CR from VB.  I
> believe that what you need to do is phrase the "Where" clause of your
select
> statement to read like:
>         Where tonumber(invoice#) in 10001 to 14000  or

>         (Where tonumber(invoice#) >= 10001 and tonumber(invoice#) <=
14000)

> If this doesn't restrict the data entering the report, you can use the
> formula field to sort on and suppress records outside the range

> Bob



> > Hi Bob;
> > Assuming that this will work, what's the syntax for the .Selection
clause
> in
> > VB? It's currently pointing to a datasource column, not a formula field.
> > Thanks
> > Bill




- Show quoted text -

Quote:
> > > Hi Bill,
> > >     I don't know if this will help, but CR7 has a built-in function
> called
> > > "tonumber()" that will treat numeric text strings as numbers.  If you
> can
> > > create a formula field that uses "tonumber(invoice#)" and use this
> instead
> > > of the actual invoice #, you should be able to see only those invoices
> in
> > > the desired range.

> > > Bob Holmes



> > > > I'm using VB6.0 SP4 and CR 7.0.
> > > > Problem:
> > > > Invoice# was defined as an alphanumeric field. When users enter a
> range
> > of
> > > > invoice#, say 10001 - 14000, this would include all invoices with
> > #1100 -
> > > > 1399! Is there anyway to fix this problem without changing Invoice#
> data
> > > > type(in MS Access) to numeric? For example, can I modify the
following
> > to
> > > > make it work?

> > > > If Len(Trim(strSelection)) > 0 Then
> > > >         strSelection = strSelection & " and "
> > > >     End If
> > > >     strSelection = strSelection & "{fr_invoice1.invoice_no} >= " &
"'"
> &
> > > > Trim(txtInvFrom.Text) & "'" & _
> > > >      " and {fr_invoice1.invoice_no} <= " & "'" & Trim(txtInvTo.Text)
&
> > "'"

> > > > Thanks
> > > > Bill



Sun, 31 Aug 2003 06:29:01 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Using formula field in selection formula

2. Using formula field in selection formula

3. Using formula field in selection formula

4. How to convert a Yes/No field to a numeric field

5. Date in Text Field..How Do I Covert It Formula Selection

6. Using details of text field in formulas

7. Please help!!Convert text field to date field(newbie)

8. Convert number field to text field.

9. Running Totals, accessing the database fields outside of the selection formula

10. Converting numeric field

11. How Convert A numeric field to string without the formating

12. ADO converting Excel fields to numeric

 

 
Powered by phpBB® Forum Software