Data type Mismatch

[mikelev]

Access 2000 on WIn2K


Have a form with two fields:

Countdate = Date/time (bound textbox) defaulted to Now()
Counttime = Text (Combobox with valuelist)


The following code produces Runtime error 13 'Type Mismatch' after the combo (combo71) is updated?

Private Sub Combo71_AfterUpdate()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM TABLEUTIL WHERE [countDate]=#" & Me.COUNTDATE & "# And [counttime]='" & Me.Combo71 & "'")
If Not rst.EOF Then
   MsgBox "An Entry already exists for this timeslot!", vbOKOnly, "StraightEdge Software"
   Me.Combo71.SetFocus
   Me.Combo71 = Null
Else
   Me.Combo71.SetFocus
End If
Set rst = Nothing
End Sub

Many thanks for any ideas

 

[Remou]

Hi
Have you checked out which line is giving the type mismatch by commenting out different lines or stepping through the code? I would be a little worried about:

Countdate = Date/time (bound textbox) defaulted to Now()

Because Now() includes both date and time, which might give an error in your SELECT statement. It is hard to tell without an exact line.

 

[PHV]

If counttime is defined as numeric in TABLEUTIL, then replace this:
[counttime]='" & Me.Combo71 & "'")
By this:
[counttime]=" & Me.Combo71)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mikelev]

The following line is causing the error:

Set rst = CurrentDb.OpenRecordset("SELECT * FROM TABLEUTIL WHERE [countDate]=#" & Me.COUNTDATE & "# And [counttime]='" & Me.Combo71 & "'")

countime is defined as text in table TABLEUTIL

 

[grande]

Do what PHV said, that should solve the problem.

-------------------------
Just call me Captain Awesome.

 

[RoyVidar]

What kind of date format do you use? Does it differ from US? If so, you'll need to format the date to an unambiguous format, for instance:

[tt]...WHERE [countDate]=#" & format$(Me.COUNTDATE,"yyyy-mm-dd") & "# And...[/tt]

See International Dates in Access and/or Return Dates in US #mm/dd/yyyy# format for more info (NOTE - my above suggestion deals only with DATE, not the time fraction, but look in the last link to find how to deal with both)

But the "Now()" info, is also a bit troubling - are you sure your criterion will have a match on date and time? or could you use my initial suggestion on only date?

Roy-Vidar

 

[mikelev]

Thanks all for your responses.

I changed counttime = Numeric for table TABLEUTIL.

Placed the field on my form as a combo box:
Row Source type: Value List
Row Source: "1030";"1130";"1230";"1330";"1430";"1530";"1630";"1730";"1830";"1930";"2030";"2130";"2230";"2330";"0030";"0130";"0230";"0330";"0430";"0530"

Here is the After Update event code:

Set rst = CurrentDb.OpenRecordset("SELECT * FROM TABLEUTIL WHERE [countDate]=#" & Me.COUNTDATE & "# and [counttime]= " & Me.COUNTTIME)

Same error '13' Mismatch? I must be missing something?

 

[mikelev]

Howdy RoyVidar:

I did change the default value for the countdate = date()

Cheers,