Data Type Conversion Error (1 Viewer)

NearImpossible · Sep 20, 2019

Wondering if someone can help me out with the following: I am getting the Data Type Conversion Error on the Rs([Equipment Order Due]) part

[Equipment Order Due] = "DateTime" type in SQL and formatted to MM/DD/YYYY text box on the Access side
[Equipment Order Complete] = "Bit" type in SQL and a checkbox on the Access side

Code:

Dim Rs As Recordset
Dim s As String
        
Set Rs = CurrentDb.OpenRecordset("Select [Equipment Order Due] from [dbo_FacilitySetupChecklist] Where [Equipment Order Complete] = False", dbOpenDynaset, dbSeeChanges)
             
s = "Equipment Order - Due " & Rs([Equipment Order Due])
MsgBox s

arnelgp · Sep 20, 2019

If Not (rs.BOF and rs.EOF) Then _
s = "Equipment Order - Due " & Rs(0)

Cronk · Sep 20, 2019

The correct syntax is
s = "Equipment Order - Due " & Rs("[Equipment Order Due]")

Alternatively
s = "Equipment Order - Due " & Rs![Equipment Order Due]

What is supposed to happen if there is more than one order incomplete?

NearImpossible · Sep 20, 2019

thanks for the responses, it will be a day or 2 before I can test them.

What is supposed to happen if there is more than one order incomplete?

This is only 1 record that will have multiple "Completed" check boxes, however I was just testing on one of them.

i.e. Orders Complete, Paperwork Complete, Orders Shipped, etc.

I am putting this on the form "On Load" event to compile all tasks that are not completed to display on 1 message box with the Task name and due date for each when the form is opened.

Currently I have the table setup similar to Orders Due, Orders Notes, Orders complete, Paperwork Due, Paperwork Notes, Paperwork complete, etc, with the "Notes" columns having default values set via ADD CONSTRAINTS on the SQL side.

I was thinking of just redoing my table so the columns are Task, Due Date, Notes, Completed, however I don't know how I would be able to differentiate between the different tasks for the default values in that setup.

[EDIT] Maybe an If/Then statement would be a better solution since its only 1 record, guess I don't need to search a record set for only 1 record !!:banghead:

Code:

If [Orders Complete] = False Then
     Let Orders = [Orders Due Date]
End If

If [Paperwork Complete] = False Then
     Let Paperwork = [Paperwork Due]
End If

If [Orders Shipped] = False Then
     Let Shipped = [Shipped Due Date]
End If

    MsgBox "Orders  Due " & Orders & vbCrLf & "Paperwork Due " & Paperwork & vbCrLf & "Shipping Due " & Shipped

If you have a better way to accomplish this task, I am open to suggestions.

Thank you
Kevin

Data Type Conversion Error (1 Viewer)

NearImpossible

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Cronk

Registered User.

NearImpossible

Registered User.

Similar threads

Users who are viewing this thread