Data type mismatch 1

[1starr]

I wrote code where a list box pops up and the user gets to select one of many dates which appears as 10-Oct-03 for example. The problem is that once the user selects the date it is pass through the code as 10-10-03 and by the time it is pass to the query where I have set the criteria “FindDate()” which is looking for 10-Oct-03 I get the error “Data type mismatch in criteria expression.” Does anyone know a way of correcting this problem?

 

[Darrylles]

Hi,

Pass FindDate() the Format() function, so that it looks for the FORMATTED date that you give it.

E.g. Findate(Format(mydatefield,'dd/mm/yy') etc

You may have to experiment with the 'dd/mm/yy' entry, but FORMAT is your answer.

Regards,

Darrylle




"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[1starr]

I have tried as you have explain and I still get the same error. Here is an example of my query:
SELECT [CERT June].Category, [CERT June].Weight, [CERT June].Status, [CERT June].Domain, [CERT June].DateofSRR
FROM [CERT June] RIGHT JOIN Status ON [CERT June].Status = Status.Status
GROUP BY [CERT June].Category, [CERT June].Weight, [CERT June].Status, [CERT June].Domain, [CERT June].DateofSRR
HAVING ((([CERT June].Domain)=FindDomain()) AND (([CERT June].DateofSRR)=FindDate()))
ORDER BY [CERT June].Category, [CERT June].Weight;

 

[OnTheFly]

Is your function returning a date and is the field DateofSRR defined as a date/time field? Are you sure this is breaking down at the FindDate() and not at the FindDomain()? If it is, step through the code and find out what the value of the FindDate function is when it returns to the query.

Seeing the code of the function FindDate might also help to figure out where it is breaking down.

 

[1starr]

Below is the code I use. Hopfully this will help:
Option Compare Database

Option Explicit

Public strDom As String
Public strDate As String


Public Function FindDomain()
FindDomain = strDom

End Function

Public Function FindDate()
FindDate = strDate

End Function

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String
Dim stLinkCriteria As String

strDom = InputBox("Enter Domain (like CPK, SSP, HQS, etc).&quot

stDocName = "frmStatusDate"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

Private Sub Command2_Click()
Dim frm As Form, ctl As ListBox, var As Variant
Dim temp As String

Set frm = Forms!frmStatusDate
Set ctl = frm!lstOrg

'If no selection display warning and exit
If ctl.ItemsSelected.Count = 0 Then
MsgBox "Please select a Date."
Exit Sub
'builds sql where clause
'using each of the selected org
Else
For Each var In ctl.ItemsSelected
temp = Chr(39) & ctl.ItemData(var) & Chr(39)
strDate = temp

Next var
End If

'deletes the fina or from the where clause
strDate = Left$(strDate, Len(strDate) - 1)
On Error GoTo err_handler

'opens report, closes form
DoCmd.OpenReport "Status Of Domain", acViewPreview
DoCmd.Close acForm, "frmStatusDate"

Set ctl = Nothing
Set frm = Nothing

Err_exit_sub:
Exit Sub

err_handler:
If Err.Number = 2501 Then
'user cancer report or no data
Resume Next
Else
MsgBox Err.Number & ": " & Err.Description
Resume Err_exit_sub
End If
End Sub

 

[OnTheFly]

I am going to guess that the field [CERT June].DateofSRR is set as a date/time field. Your function FindDate is returning a string value. That is why you are getting a type mismatch. I would suggest making FindDate returns a date rather than a string otherwise you would have to convert [Cert June].DateofSRR to a string in the Where clause.



Hope this helps.

OnTheFly

 

[1starr]

Thank you, that was the problem I had FindDate as a String when it should had been variable type as Date.