CODEWITHSUNDEEP
sqlvbams-access
Envertex
Envertex

Reputation: 5

Access VBA SQL Complie Error: Type Mismatch

This following bit of code is placed in the On Load event of a form:

Dim LAOQWeekdaySQL As String
Dim LAOQWeekday As QueryDef
Dim EnqIDTest As String
EnqIDTest = Me.txt_EnquiryID.Value

LAOQWeekdaySQL = "SELECT tbl_Costing_Labour.EnquiryID, tbl_Costing_Labour.PhaseLabour, tbl_Costing_Labour.TotalPhaseLabourHours " & _
                 "FROM tbl_Costing_Labour " & _
                 "WHERE (((tbl_Costing_Labour.EnquiryID)= " & EnqIDTest & ") AND ((tbl_Costing_Labour.PhaseLabour) Like ""*weekday*""));"

Set LAOQWeekday = CurrentDb.QueryDefs("LAOQWeekdayQRY").SQL = LAOQWeekdaySQL

Me.txt_LAOQ_Weekday.Value = DSum("TotalPhaseLabourHours", "LAOQWeekdayQRY")

Essentially it returns records matching the Inquiry Number and sums the final column in the query and outputs it to a textbox on the form.

However, when I got run the form it gives me a

compile error: type mismatch

Prior to this I did try and use a temporary query but then the Dsum has no name to reference when performing its function so I changed it .CreateQueryDef, which work fine the first time. Thereafter, obviously, it threw an error saying the query already exists so I went down the route of .QueryDefs to update the SQL instead and now I get the mismatch error.

I cannot see anything obvious, what am I missing here?

Thank you in advance for any help you can provide!

Upvotes: 0

Views: 328

Answers (1)

Brian M Stafford
Brian M Stafford

Reputation: 8868

The following line evaluates to a Boolean:

Set LAOQWeekday = CurrentDb.QueryDefs("LAOQWeekdayQRY").SQL = LAOQWeekdaySQL

Try changing it to this:

Set LAOQWeekday = CurrentDb.QueryDefs("LAOQWeekdayQRY")
LAOQWeekday.SQL = LAOQWeekdaySQL

Upvotes: 2

Related Questions