Error no: 6 Overflow

ankrups

Board Regular
Joined
Mar 14, 2006
Messages
105
Hi there,

I am trying to filtered the data by Invoice no. in my one of the field and I get the following overflow error...the coding is

Code:
Private Sub cboInvoiceNumber_AfterUpdate()
  
   Dim intInvoiceNumber As Integer
      
   intInvoiceNumber = Nz(Me![cboInvoiceNumber])
    
   strFilter = "[InvoiceNumber] =" & intInvoiceNumber
   Me![FilterString] = strFilter
   DoCmd.RunCommand acCmdSaveRecord
   strRecordSource = "qryMainSearch"
   strQuery = "qryFilteredMainSearch"
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & strFilter & ";"
   Debug.Print "SQL Statement: " & strSQL
   Debug.Print CreateAndTestQuery(strQuery, strSQL) _
      & " records found"
   Me![subSearchResults].Form.RecordSource = strQuery
   

End Sub


your help is appreciated...


thnks

ankit
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi ankit,

Integer values overflow around 32K. Try

Code:
Dim intInvoiceNumber As Long

Denis
 

ankrups

Board Regular
Joined
Mar 14, 2006
Messages
105
Hi Denis,

Now the error turns into no.13 : Type Mismatch. i declare this variable on start of the database.pls have a look and reply me...

Code:
Option Explicit
Option Compare Database


Dim strTitle As String
Dim strPrompt As String
Dim strFilter As String
Dim strQuery As String
Dim strRecordSource As String
Dim strSQL As String

--------------
Private Sub cboInvoiceNumber_AfterUpdate()
   
   Dim intInvoiceNumber As Long
      
   intInvoiceNumber = Nz(Me![cboInvoiceNumber])
     
   strFilter = "[InvoiceNumber] =" & intInvoiceNumber
   Me![FilterString] = strFilter
   DoCmd.RunCommand acCmdSaveRecord
   strRecordSource = "qryMainSearch"
   strQuery = "qryFilteredMainSearch"
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & strFilter & ";"
   Debug.Print "SQL Statement: " & strSQL
   Debug.Print CreateAndTestQuery(strQuery, strSQL) _
      & " records found"
   Me![subSearchResults].Form.RecordSource = strQuery
  

End Sub

waiting for your reply...


thanks ...ankit
 

ankrups

Board Regular
Joined
Mar 14, 2006
Messages
105

ADVERTISEMENT

Hi Denis,

it is Text...
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

OK, try this instead. If the InvoiceNumber field is text, and that is the first field in the combo, you need to declare InvoiceNumber as a String and surround any occurrences with quotes.

Code:
Private Sub cboInvoiceNumber_AfterUpdate()
 
   Dim strInvoiceNumber As String
     
   strInvoiceNumber = Me![cboInvoiceNumber]
   
   strFilter = "[InvoiceNumber] ='" & strInvoiceNumber & "'"
   Me![FilterString] = strFilter
   DoCmd.RunCommand acCmdSaveRecord
   strRecordSource = "qryMainSearch"
   strQuery = "qryFilteredMainSearch"
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & strFilter & ";"
   Debug.Print "SQL Statement: " & strSQL
   Debug.Print CreateAndTestQuery(strQuery, strSQL) _
      & " records found"
   Me![subSearchResults].Form.RecordSource = strQuery
   

End Sub
Denis
 

ankrups

Board Regular
Joined
Mar 14, 2006
Messages
105
Hi Dennis,

Now I get the Error no.13 : Type Mismatch any suggestin...and yest data type in table where invoicenumber stored is Integer...not a text,,,


wating for your reply.

thanks

ankit
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Ankit,

maybe you are making this too complex by passing the combo value to a variable first. Why not use it directly?
Code:
Dim strTitle As String 
Dim strPrompt As String 
Dim strFilter As String 
Dim strQuery As String 
Dim strRecordSource As String 
Dim strSQL As String 

-------------- 
Private Sub cboInvoiceNumber_AfterUpdate() 
    
   strFilter = "[InvoiceNumber] =" & Me!cboInvoiceNumber 
   Me![FilterString] = strFilter 
   DoCmd.RunCommand acCmdSaveRecord 
   strRecordSource = "qryMainSearch" 
   strQuery = "qryFilteredMainSearch" 
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _ 
      & strFilter & ";" 
   Debug.Print "SQL Statement: " & strSQL 
   Debug.Print CreateAndTestQuery(strQuery, strSQL) _ 
      & " records found" 
   Me![subSearchResults].Form.RecordSource = strQuery 
  
End Sub
Denis
 

Forum statistics

Threads
1,144,376
Messages
5,723,997
Members
422,530
Latest member
Badpoisondwarf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top