Trying to use VBA to write query in Access

Question

I am getting a type mismatch with the following syntax in my Access VBA. I am trying to update my table named "Billing" by seeing if any records have a date that looks at a string value in my "Certs" table like "2012-07-01" corresponding to my form's billYear textbox e.g. 2012 and my billMonth textbox e.g. 07. Is there a better way to write the VBA or see an error - many thanks:

Dim sRecert As String
Dim byear As Integer
Dim bmonth As Integer

byear = Me.billYear
bmonth = Me.billMonth

sRecert = "Update Billing set recertifying = -1 where (select     certificationExpireDate from certs where Left((certificationExpireDate),4) =  " & byear
    & " and Mid((certificationExpireDate),6,2) =  " & bmonth & ")"

DoCmd.RunSQL sRecert

I may not have explained it well. I created a real Query called from my form: DoCmd.OpenQuery "updateRecert"
I set up my SQL below as a test on a real date I’m working with. It is in SQL Server (ODBC linked) My dbo_certs table and my dbo_billing table share only one joinable field peopleID:

UPDATE dbo_Billing AS a INNER JOIN dbo_certs AS b ON a.peopleid = b.peopleid 
SET a.recertifying = -1
WHERE b.certificationExpireDate = '2015-08-31 00:00:00.000';

The above gave a data mismatch error. My bottom line is I have two text boxes on my form to pass in data preferably into my VBA code:

billMonth which in this case is 8 because it is an integer so that is a problem
billYear is 2015

so I need to update my dbo_billing table’s ‘recertifying’ field with -1 if the dbo_cert’s field ‘certificationExpireDate’ is '2015-08-31 00:00:00.000' but only if that can be gotten from the form.

Don Jewett Don Jewett · Accepted Answer · 2015-07-15T21:29:05

Is there a better way to write the VBA or see an Error?

Yes. You need Error Handling

I don't think the issue is in the code, I think it's in the SQL.

To troubleshoot your code, wrap it in an good error handler.

Public Sub MyRoutine()

    On Error GoTo EH

    'put your code here

    GoTo FINISH

EH:

    With Err
        MsgBox "Error" & vbTab & .Number & vbCrLf _
            & "Source" & vbTab & .Source & vbCrLf & vbCrLf _
            & .Description
    End With

    'for use during debugging
    Debug.Assert 0
    GoTo FINISH
    Resume

FINISH:

    'any cleanup code here

End Sub

When the msgbox shows the error, make note of the Source. This should help you determine where the error comes from.

The lines following 'for use during debugging are helpful. Here's how to use them:

execution will stop on the Debug.Assert 0 line
drag the yellow arrow (which determines which line to run next) to the Resume line
hit {F8} on the keyboard (or use the menu Debug > Step Into)

This will go to the line where the error occurred. In your case, it will probably be the last line of your code.

Trying to use VBA to write query in Access

2 Answers