Help with Coding to find Count & pass Result to Report 1

[thermalman]

Hi, I am trying to write some coding behind a command Button that will Count a number of WorkOrders that meet a criteria from TxtBoxes and Combo Boxes on a Form. I am accessing a SQLView that has all of the Data and when I enter the search Criteria within SQL it works but upon running the code from the form I get a Message "Data Type Mismatch in Criteria Expression"

Here is the coding behind the Command Button that should pull up the report with the Figure in a Txtbox based on the Criteria Given:-


Dim stDocName, strSQLFix As String
Dim rstFix As ADODB.Recordset
Dim StartDate,EndDate,CustomerName As String

Set rstFix = New ADODB.Recordset

stDocName = "rptCompletedCallsByCustomer"

If(IsNull(txtStartDate))=True Then
MsgBox "Please Enter a Date Range"
Else
StartDate = txtStartDate
EndDate = txtEndDate
CustomerName = cmbCustomer
strSQL = "SELECT COUNT(dbo_ABCDWorkOrderView.CallNum)As C
From dbo_ABCDWorkOrderView
Where(dbo_ABCDWorkOrderView.Offsite BETWEEN '"&Format(StartDate, "d-mmm-yyyy")& "' AND '"&Format(EndDate,"d-mmm-yyyy")& "') AND (dbo_ABCDWorkOrderView.Offsite > dbo_ABCDWorkOrderView.TimeOut)AND(dbo_ABCDWorkOrderView.FixedOrUnresolved ='FIXED')AND (dbo_ABCDWorkOrderView.CallCompleted ='1')AND (CustomerName LIKE '"&cmbCustomer&"');"

rstFix.Open strSQLFix, CurrentProject.Connection,adOpenDynamic,adLockOptimistic
txtMissedFix = rstFix!c
rstFix.Close
Docmd.OpenReport stDocName,acPreview
EndIf


Any help in resolving this is greatly appreciated as I have already spent many days trying to just create 1 report and I have another 18 to go.

Regards
Thermalman

 

[Remou]

Just at a quick glance, you need hash marks for dates (##), if the date fields are date type. Also, SQL needs mm/dd/yy format, as far as I recall. Finally, it may be easier to use Dlookup.



PS I find red writing on a blue background very difficult to read. [[]Code]Your code[[]/Code] is good.

 

[thermalman]

Hi Remou, Where would I enter the # in the Query as I am not actually entering a Date directly into the coding but entering it into a TextBox that then passes it back to the query.

I haven't really used Dlookup before,is there anywhere I can lookup examples of what it does.

Regards
Thermalman

 

[Remou]

They are instead of the single quotes (') that you have at the moment. If CallCompleted is numeric, the sql should read CallCompleted =1, that is, no quotes.
[tt]strSQL = "SELECT COUNT(dbo_ABCDWorkOrderView.CallNum)As C
From dbo_ABCDWorkOrderView
Where(dbo_ABCDWorkOrderView.Offsite BETWEEN #" & Format(StartDate, "mm-dd-yyyy") & "# AND #" & Format(EndDate,"mm-dd-yyyy")& "#) AND (dbo_ABCDWorkOrderView.Offsite > dbo_ABCDWorkOrderView.TimeOut) AND(dbo_ABCDWorkOrderView.FixedOrUnresolved ='FIXED') AND (dbo_ABCDWorkOrderView.CallCompleted ='1') AND (CustomerName LIKE '" & cmbCustomer &"');"[/tt]

Have a look at thread705-1207225 and FAQ705-4978.

 

[thermalman]

Hi Remou, Many Thanks for the Help and Advise that works a treat.

Regards
Thermalman