Code error, is something simple no doubt

[marky_dp]

Hi all, if any one could help with this problem it would be much appreciated.

My Problem: I would like to be able to display "N/A" in the delivery text fields on my report where no delivery info actually exists. Displaying the necessary info where delivery info does exsist is fine, my code just seizes when it cant find any dates. Below is the code what is trying to carry out the operation. My original idea (probably a stupid one) was to say :
If myrecordsource.item.field (deliveryfield) is not = to Null then

Display delivery info

If it is = to null then

Put N/A into delivery fields on the report

Am I on the right lines or well off the mark?

Cheers


Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim mySQL As String

mySQL = "SELECT DeliveryID, OrderID, DeliveryDate, DeliveryTime"
mySQL = mySQL + " FROM DeliveryTBL"
mySQL = mySQL + " WHERE OrderID = " & Me.OrderID.Value & " "

myRecordSet.Open mySQL

If myRecordSet.Fields.Item("DeliveryID") <> Null Then

Reports![OrderInvoiceRPT]!DeliveryRefText = myRecordSet.Fields.Item("DeliveryID")
Reports![OrderInvoiceRPT]!DeliveryDateText = myRecordSet.Fields.Item("DeliveryDate")
Reports![OrderInvoiceRPT]!DeliveryTimeText = myRecordSet.Fields.Item("DeliveryTime")

Else

Reports![OrderInvoiceRPT]!DeliveryRefText.Value = "N/A"
Reports![OrderInvoiceRPT]!DeliveryDateText.Value = "N/A"
Reports![OrderInvoiceRPT]!DeliveryTimeText.Value = "N/A"

End If

 

[FoFa]

Probably the simplest is to:
If ISNULL(myRecordSet.Fields.Item("DeliveryID")) Then
Reports![OrderInvoiceRPT]!DeliveryRefText.Value = "N/A"
Reports![OrderInvoiceRPT]!DeliveryDateText.Value = "N/A"
Reports![OrderInvoiceRPT]!DeliveryTimeText.Value = "N/A"

ELSE

Reports![OrderInvoiceRPT]!DeliveryRefText = myRecordSet.Fields.Item("DeliveryID")
Reports![OrderInvoiceRPT]!DeliveryDateText = myRecordSet.Fields.Item("DeliveryDate")
Reports![OrderInvoiceRPT]!DeliveryTimeText = myRecordSet.Fields.Item("DeliveryTime")
End If

 

[marky_dp]

Hi yeah, have just tried that, the code now errors (at the highlighted line), saying :Run time error, the value you entered isn't valid for this field.

If IsNull(myRecordSet.Fields.Item("DeliveryID")) Then

Reports![OrderInvoiceRPT]!DeliveryRefText.Value = "N/A"
Reports![OrderInvoiceRPT]!DeliveryDateText.Value = "N/A"
Reports![OrderInvoiceRPT]!DeliveryTimeText.Value = "N/A"

Else

Reports![OrderInvoiceRPT]!DeliveryRefText = myRecordSet.Fields.Item("DeliveryID")
Reports![OrderInvoiceRPT]!DeliveryDateText = myRecordSet.Fields.Item("DeliveryDate")
Reports![OrderInvoiceRPT]!DeliveryTimeText = myRecordSet.Fields.Item("DeliveryTime")

End If

 

[Bat17]

try it this way
Reports![OrderInvoiceRPT]!DeliveryRefText = myRecordSet!DeliveryID

If it does not like that then step through the code checking valuse to see what it is objecting to.

HTH

Peter

 

[marky_dp]

Hi guys, thanks for your help, but things still arn't working. I think the problem is to do with the Null in my, If clause.

The code works fine where there is an orderId (in the deliveryTBL) that is equal to the orderId on my form. But it crashes when there isn't an orderID (in the deliveryTBL) that is the same as one thats on my form.

So, my thought was to say that, if there isn't an orderId in the table that = the orderId on my form, do this:

If myRecordSet 'DeliveryID' is equal to null Then

put N/A into the text values on my report.

But if there is no orderId in the deliveryTBL that is = to the orderID on my form, it cant = Null as it wont exist at all.

So, somehow I need to say:

If myRecordSet (DeliveryID) does not exist Then

put N/A into the text values

Else

make text values = to those of the recordset.

If anyone can sort this out, you are legends, cheers

 

[Bat17]

you could try it this way incase you have an empty string rather than a Null

If NZ(myRecordSet.Fields.Item("DeliveryID"),"") = "" Then


HTH

Peter