Run-time error 94 Invalid use of Null
Bsunuwar
I have a problem with VBA or VB6 which gives me an error
"runtime error 94 invalid use of null" when I try to print my official
cheque.I know nothing about Microsoft Visual Basic.When I press debugit
highlights the error line with yellow colour which is
dateofcheque = Replace(Trim(rs("dateprinted")), "/", ".")
When I take out this line from scripts it works but keeps on overwriting on
same excel spreadsheet data instead of writing on next line and without the
date.Please HELP.My full script:-
Option Compare Database
Private Sub cmdConsolidate_Click()
Dim db, strsql, rs, intRecCount, qry2, intChqNo, confirm
confirm = MsgBox("Are you sure you wish to consolidate cheques? You will
NOT be able to re-print cheques OR remittance advice slips after!", vbYesNo,
"Confirm")
If confirm = 6 Then
Set db = CurrentDb()
strsql = "SELECT cheques.* FROM cheques WHERE (((cheques.printed) =
False)) ORDER BY cheques.pk"
Set rs = db.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)
If rs.EOF Then
MsgBox "There are no cheques to be consolidated"
Else
rs.MoveLast
intRecCount = rs.RecordCount
rs.MoveFirst
For i = 1 To intRecCount
Set qry2 = db.QueryDefs("qGeneric")
strsql = "UPDATE cheques SET printed=1 WHERE pk=" & CStr(rs("pk"))
qry2.SQL = strsql
db.Execute "qGeneric", dbSeeChanges
rs.MoveNext
Next
MsgBox "Consolidation complete"
DoCmd.Close acForm, "printmenu"
End If
End If
End Sub
Private Sub Command16_Click()
Dim xl
'Dim opendialog As New MSComDlg.CommonDialog
Dim i
Dim i2
Dim cell
Dim rowtouse
Dim qry
Dim rs
Dim intRecCount
Dim db As Database
Set db = CurrentDb()
'opendialog.Filter = "xls"
'opendialog.Filename = "*.xls"
'opendialog.ShowOpen
Set xl = CreateObject("Excel.Application")
'xl.Workbooks.Open (opendialog.File)
xl.Workbooks.Open ("O:\Management Accounts\Accounts
01.04.09-31.03.10.xls")
xl.Sheets("Cheques Written").Select
For i = 3 To 16000
cell = "C" + CStr(i)
If xl.Range(cell).Value = "" Then
rowtouse = i
i = 15999
End If
i = i + 1
Next
Set qry = db.QueryDefs("ChequestoPrint")
Set rs = qry.OpenRecordset(dbOpenDynaset, dbSeeChanges)
rs.MoveLast
intRecCount = rs.RecordCount
rs.MoveFirst
For i2 = 1 To intRecCount
Dim supplier
Dim dateofcheque
Dim chequenumber
Dim details
Dim amount
supplier = Trim(rs("Name"))
dateofcheque = Replace(Trim(rs("dateprinted")), "/", ".")
chequenumber = Trim(rs("chqno"))
details = Trim(rs("description"))
amount = Trim(rs("totalpayable"))
datemonth = Mid(dateofcheque, 4, 2)
dateday = Mid(dateofcheque, 1, 2)
dateyear = Mid(dateofcheque, 9, 2)
dateofcheque = dateday + "." + datemonth + "." + dateyear
xl.Range("A" + CStr(rowtouse)).Value = supplier
xl.Range("B" + CStr(rowtouse)).Value = dateofcheque
xl.Range("C" + CStr(rowtouse)).Value = chequenumber
xl.Range("D" + CStr(rowtouse)).Value = details
xl.Range("E" + CStr(rowtouse)).Value = amount
rowtouse = rowtouse + 1
rs.MoveNext
Next
xl.Visible = True
Set xl = Nothing
End Sub
Private Sub Command16_DblClick(Cancel As Integer)
End Sub
Private Sub Command2_Click()
Dim confirm, db, qry, qry2, rs, rs2, intRecCount, intChqNo, strsql,
strSignedBy
confirm = MsgBox("Are you sure you wish to print the cheques now?",
vbYesNo, "Confirm")
If confirm = 6 Then
strFirstChqNo = Me![txtFirstChqNo]
If IsNull(strFirstChqNo) = False Then
Set db = CurrentDb()
strsql = "SELECT cheques.* FROM cheques WHERE
(((cheques.printed) = False)) ORDER BY cheques.pk"
Set rs = db.OpenRecordset(strsql, dbOpenDynaset, dbSeeChanges)
If rs.EOF Then
MsgBox "There are no cheques to be printed"
Else
rs.MoveLast
intRecCount = rs.RecordCount
rs.MoveFirst
intChqNo = CDbl(Me![txtFirstChqNo])
strSignedBy = CStr(Me![txtSignedBy])
For i = 1 To intRecCount
Set qry2 = db.QueryDefs("qGeneric")
strsql = "UPDATE cheques SET signedby='" &
CStr(strSignedBy) & "' WHERE pk=" & CStr(rs("pk"))
qry2.SQL = strsql
db.Execute "qGeneric", dbSeeChanges
intChqNo = intChqNo + 1
rs.MoveNext
Next
rs.MoveFirst
DoCmd.OpenReport "rptChqReq", acViewPreview
intChqNo = CDbl(Me![txtFirstChqNo])
For i = 1 To intRecCount
Set qry3 = db.QueryDefs("qGeneric")
strsql = "UPDATE cheques SET chqno='" & CStr(intChqNo) &
"', dateprinted=DATE() WHERE pk=" & CStr(rs("pk"))
qry3.SQL = strsql
db.Execute "qGeneric", dbSeeChanges
intChqNo = intChqNo + 1
rs.MoveNext
Next
End If
Else
MsgBox "You must enter the first cheque number to proceed",
vbOKOnly, "Error"
End If
Else
MsgBox "Printing cancelled", vbOKOnly, "Cancelled"
End If
End Sub
Private Sub Command3_Click()
DoCmd.OpenReport "rptChqReqTest", acViewPreview
End Sub
Private Sub cmdPrintRemit_Click()
On Error GoTo Err_cmdPrintRemit_Click
Dim stDocName As String
stDocName = "rptRemitAdvice"
DoCmd.OpenReport stDocName, acPreview
Exit_cmdPrintRemit_Click:
Exit Sub
Err_cmdPrintRemit_Click:
MsgBox Err.description
Resume Exit_cmdPrintRemit_Click
End Sub
Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click
DoCmd.Close
Exit_cmdExit_Click:
Exit Sub
Err_cmdExit_Click:
MsgBox Err.description
Resume Exit_cmdExit_Click
End Sub
Private Sub Detail_Click()
End Sub
Private Sub Form_Load()
End Sub
Private Sub Frame10_BeforeUpdate(Cancel As Integer)
End Sub
Private Sub Frame12_BeforeUpdate(Cancel As Integer)
End Sub
Private Sub txtFirstChqNo_BeforeUpdate(Cancel As Integer)
End Sub
Private Sub txtSignedBy_BeforeUpdate(Cancel As Integer)
End Sub
Jason Keats
> Hi Everybody,
>
>
> I have a problem with VBA or VB6 which gives me an error
> "runtime error 94 invalid use of null" when I try to print my official
> cheque.I know nothing about Microsoft Visual Basic.When I press debugit
> highlights the error line with yellow colour which is
> dateofcheque = Replace(Trim(rs("dateprinted")), "/", ".")
> When I take out this line from scripts it works but keeps on overwriting on
> same excel spreadsheet data instead of writing on next line and without the
> date.Please HELP.My full script:-
Date fields in a database can usually contain Null values.
In your case, rs("dateprinted") is Null. Trimming a Null apparently
returns a null. Therefore, when you try to use a Null in Replace (which
requires a string input) you get an error 94.
The simplest way of preventing that is:
dateofcheque = Replace(Trim(rs("dateprinted") & ""), "/", ".")
Richard Mueller [MVP]
rs("dateprinted") can be Null. This would raise the error because the
Replace function cannot handle Nulls. Since you treat the value as a string,
one solution could be to append a blank string to the value. For example:
dateofcheque = Replace(Trim(rs("dateprinted") & ""), "/", ".")
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--
"Bsunuwar" <bsun...@gmail.com> wrote in message
news:8A1D3311-FBE2-4B86...@microsoft.com...
Jan Hyde
on Fri, 1 May 2009 06:48:02 -0700 bearing the following
fruit:
>Hi Everybody,
>
>
> I have a problem with VBA or VB6 which gives me an error
>"runtime error 94 invalid use of null" when I try to print my official
>cheque.I know nothing about Microsoft Visual Basic.When I press debugit
>highlights the error line with yellow colour which is
> dateofcheque = Replace(Trim(rs("dateprinted")), "/", ".")
>When I take out this line from scripts it works but keeps on overwriting on
>same excel spreadsheet data instead of writing on next line and without the
>date.Please HELP.My full script:-
>
>
To add to the other comments. Use trim$() not trim()
--
Jan Hyde
Bsunuwar
Thank you very much for reply and error is gone but still cannot print date
of cheque in excel spreadsheet and keeps on writing on same line instead of
moving to next ine for next cheque.
Thank you
Bsunuwar
Jeff Johnson
So add an If statement to TEST the value for Null and take appropriate
action.
Jeremiah D. Seitz
wrote:
>Hi Everybody,
>
>
> I have a problem with VBA or VB6 which gives me an error
>"runtime error 94 invalid use of null" when I try to print my official
>cheque.I know nothing about Microsoft Visual Basic.When I press debugit
>highlights the error line with yellow colour which is
> dateofcheque = Replace(Trim(rs("dateprinted")), "/", ".")
>When I take out this line from scripts it works but keeps on overwriting on
>same excel spreadsheet data instead of writing on next line and without the
>date.Please HELP.My full script:-
Building on the other responses you've received, how is your script to
determine a valid date from a purposefully invalid value? Null is
null, without exception. Your code depends on rs!dateprinted being
non-null, so how can you expect it to behave normally when presented
with a null?
Your choices are to either modify your code to handle nulls in a
nonintrusive manner, or better yet, ensure that the nulls aren't
stored in the first place. Perhaps a NOT NULL constraint in the
tabledef?
Otherwise, what you're asking is impossible. For example, what date
would "" be interpreted as in your code? It's, unfortunately, a GIGO
issue.
J.
Jeremiah D. Seitz
Omega Techware
http://www.omegatechware.net
James Hahn
(it's null). You need to examine the data you are using for the cheque
printing and confirm that each cheque to be printed has a date. Perhaps
this happens when you consolidate cheques if the cheque dates for the
different cheques could be different. In this case you need to examine the
database record for the consolidated cheque and find out why there is no
date. The original code for the program did not allow for the possibility
that the date might be null.
If it is OK to print cheques where the cheque record in the database does
not have a date you need to determine a default value for the date (today
perhaps, or just leave it blank) and use that in setting dateofcheque when
you test for the case where the date is null. This will require changing
your program to test for null and insert the default when required.
"Bsunuwar" <bsun...@gmail.com> wrote in message
news:388838C3-A57F-4B8A...@microsoft.com...
Nobody
> Private Sub Form_Load()
>
> End Sub
"Form_Load" in the code you posted indicates VB6, not VBA. VBA doesn't have
"Load" event.
The data to be entered in Excel comes from somewhere, probably an MDB
file(MS Access). Try searching the code for "OpenDatabase" or
"OpenConnection" and post these lines. In some cases it can contain a
password, so make sure that you edit it out. Also, if you find that it's an
MDB file, do not open it in MS Access before making a backup. The MDB file
could be in an old format, such as Access 97, and when you open it with a
newer version, it asks you if you want to convert it, if you press Enter
quickly, it's converted, and the VB app may not be able to read it, so make
sure that you don't convert the file format and that you have a backup.
Bsunuwar
Thank you very much for reply.As you have mentioned in post
we use sql server ,Ms access and excel spreadsheet.I have checked my sql
server and it is using correct date but no cheque number is NULL which was
programmed to use the cheque number by increment of one.
Bsunuwar
But previously same code was working.The problem started when I changed the
filename to new excel spreadsheet file.My point is that why would all code
stop working by just changing the filename.
Jan Hyde
fruit:
>Hi,
You know, that code is garbage. No validation, error
handling , use of variants, badly named variables.
I'd get it rewritten. It shouldn't take more than half an
hour, probably less time than you've spent on this problem
already.
--
Jan Hyde
Bsunuwar
I have never done coding before so I don`t know much about it so I
can`t rewrite the code.Could you please suggest me how to get date and cheque
number printed on my excel spreadsheet.My database is running in sql server
and Ms access.
Thank you
Jan Hyde
fruit:
>Hi Jan,
First fix your 'invalid use of Null' error
Change
dateofcheque = Replace(Trim(rs("dateprinted")), "/", ".")
To
'-- Get the printed date
dateofcheque = Trim$(rs("dateprinted") & "")
'-- Format the date
dateofcheque = format$(dateofcheque,"dd.mm.yy")
And delete this block of code, since you shouldn't need it
anymore (we take care of this when we format the date)
datemonth = Mid(dateofcheque, 4, 2)
dateday = Mid(dateofcheque, 1, 2)
dateyear = Mid(dateofcheque, 9, 2)
dateofcheque = dateday + "." + datemonth + "." + dateyear
Once you've done this, rerun and report any problems here.
We'll work through them one by one. Repost the code though
so we can check what you've done.
--
Jan Hyde
James Hahn
the database? If that item is blank then you will get the error your have
reported. The date that the server is using is not relevant - what's
important is that the data item in the table contains a proper date. The
cheque number is also not relevant.
"Bsunuwar" <bsun...@gmail.com> wrote in message
news:2A4ED128-C39C-4FDE...@microsoft.com...
s.m.abd...@gmail.com
I have a same problem run time error 94, when i try to view my invoice i had saved the invoice which i had not created customer name how can i delete the entry of invoice 222