Error 3464 Data type mismatch

[mistera]

I'm stumped and need some help! I understand what this error message means, but I don't know why I am getting it. Below is my code.

The strFilter value I'm sending to this sub is "11/27/2011". The Period Start field is a date field, so I'm wrapping the passed date in hash marks (#). When I copy the value of strSQL to the SQL of a new query in Access (outside of VBA), it works just fine without any changes and returns several records.

When I removed the hash marks from the code, I did not receive an error. However, my query resulted in no records being selected.

[FONT=Calibri][SIZE=3]Public Sub MoveDeferredToExtract(Optional strFilter As String)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  Dim strMsg As String, strSQL As String, db As Database, strWhereClause As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  On Error GoTo Err_MoveDeferredToExtract[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  Set db = CurrentDb[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  If IsNull(strFilter) Then[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]      strWhereClause = ";"[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  Else[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]      strWhereClause = " WHERE tbl_Deferred.[Period Start] = #" & strFilter & "#;"[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  End If[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  strSQL = CurrentDb.QueryDefs("qry_DeferredToExtract").SQL[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  strSQL = Replace(strSQL, ";", strWhereClause)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  db.Execute strSQL, dbFailOnError[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Exit_MoveDeferredToExtract:[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  Set db = Nothing[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  Exit Sub[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Err_MoveDeferredToExtract:[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  'Build an error message.[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  strMsg = ("An error within the program has occurred.  Error number:  " & _[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]    Err.Number & "; Error description: " & Err.Description & vbNewLine & _[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]     vbNewLine & "Error occurred at: '" & strProc & ", " & strCodeLocn & "'.")[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  ' Display the error message.[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  MsgBox strMsg, vbCritical, "Critical Error"[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]  Resume Exit_MoveDeferredToExtract[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]

 

[Mile-O]

Been a while since I was doing this sort of thing but a couple of things to ask:

1) 'db As Database' -should you not define the type of database object (DAO, ADO?)?
2) Rather than IsNull for an Optional parameter, should it not be IsMissing?
3) #" & strFilter & "#;" Don't know where you are, but I always recall having problems with a UK Access install and Access' love of the US date format, so would often ensure that had something like this in the code:

#" & Format(strFilter,"mm/dd/yyyy") & "#;"

 

[vbaInet]

On Error GoTo Err_MoveDeferredToExtract
  Dim strMsg As String, strSQL As String, db As Database, strWhereClause As String
  Set db = CurrentDb
  
  If IsNull(strFilter) Then
      strWhereClause = ";"
  Else
      strWhereClause = " WHERE tbl_Deferred.[Period Start] = " & Format(strFilter, "\#mm\/dd\/yyyy\#") & ";"
  End If
  
  strSQL = Replace(CurrentDb.QueryDefs("qry_DeferredToExtract").SQL, ";", strWhereClause)
  db.Execute strSQL, dbFailOnError
Exit_MoveDeferredToExtract:

Sorry Mile-O, I'm really not following you around.

@mistera: I don't understand why you're replacing the semi-colon with the WHERE clause you just built up. What would happen if you run the code a second time? The WHERE clause will appear twice in the querydef and hence, fail.

 

[mistera]

Thanks for your responses. While I never really found why I was getting the runtime error, after playing around a bit, I found another way to tackle what I was trying to achieve in the code. I'm still stumped as to what the issue was. To address your responses ...

Mile-O: I've never defined the database object as DAO or ADO. I thought it defaults to ADO and I've never had a problem with that before and I don't see how that would have an impact on the data type issue.

As for the IsMissing idea, I never considered using that before. However, I don't really think that was the issue because as I debugged the code by stepping through each line of the code as it executed, the If statement processed as I expected it to whether the parameter was there or not.

I'm in the US, so my date was passed in mm/dd/yyyy format. I don't think that was the problem.

vbaInet: The WHERE clause would never appear twice in the SQL. The string variable called strSQL is initialized to the query's SQL first (see code below). After that initialization, the replacement takes place in the variable. I'm not replacing the query's SQL as I wanted to keep the base SQL and then just add the WHERE clause the optional parameter exists.

[FONT=Calibri][SIZE=3]strSQL = CurrentDb.QueryDefs("qry_DeferredToExtract").SQL[/SIZE][/FONT]
[SIZE=3][FONT=Calibri][FONT=Calibri][SIZE=3]strSQL = Replace(strSQL, ";", strWhereClause)[/SIZE][/FONT][/FONT][/SIZE]

 

[vbaInet]

Thanks for your responses. While I never really found why I was getting the runtime error, after playing around a bit, I found another way to tackle what I was trying to achieve in the code. I'm still stumped as to what the issue was.

Did you try using the revised code provided?

vbaInet: The WHERE clause would never appear twice in the SQL. The string variable called strSQL is initialized to the query's SQL first (see code below). After that initialization, the replacement takes place in the variable. I'm not replacing the query's SQL as I wanted to keep the base SQL and then just add the WHERE clause the optional parameter exists.

[FONT=Calibri][SIZE=3]strSQL = CurrentDb.QueryDefs("qry_DeferredToExtract").SQL[/SIZE][/FONT]
[SIZE=3][FONT=Calibri][FONT=Calibri][SIZE=3]strSQL = Replace(strSQL, ";", strWhereClause)[/SIZE][/FONT][/FONT][/SIZE]

Alright, got you! I thought you were replacing the base sql. Have you considered saving it in a Const variable instead?

 

[mistera]

I suppose I could have saved the SQL in a constant, but I prefer saving it as a query. That way I can easily use it in Access without having to copy/paste SQL from VBA into a new query to use it when I'm not running the code.

 

[Galaxiom]

It is always a good idea to format the date whereever you are located. Formatting it also removes any Time component that could be inadvertantly added. Also one never knows when the database might be used elswhere and it is far easier to include it during the original development. Use the literal to force the forward slash regardless of the local date separator.

Format({date expression}, "\#mm\/dd\/yyyy\#")

Relying on defaults between DAO and ADO is a poor programming practice. They should always be specified. The order of the references can make a difference.

Note that when using IsMissing the argument must be a Variant.

 

[ChrisO]

With the octothorpes "11/27/2011" is treated as a Date.
Without the octothorpes "11/27/2011" is treated as a Double number.

In the SQL string without the octothorpes "11/27/2011" becomes 11 / 27 / 2011 which is 0.000202589.

As a Date 0.000202589 is 18 seconds past midnight on the 30-Dec-1899.

Chris.

 

[Galaxiom]

Back to the original issue.

The symptoms strongly suggest that the field [Period Start] is not a date type but text.

BTW. The comments in your code are probably two of the most pointless I have seen in a long time.

 

[ChrisO]

Test case with dates in US month day year format…

Date of "10/1/100" matches a Date/Time field with a displayed value of 2:24:00 AM

10 / 1 / 100 = 0.1

0.1 days = 2 hours and 24 minutes.

2 hours 24 minutes past midnight 30-Dec-1899.
Since the Date part is 0 Access does not display it and only displays the Time part.

Without the octothorpes the Date is being regarded as a division.

Chris.

 

[mistera]

Thanks go out to everyone for your insightful comments. Just to respond to some of them ...

• I double-checked and [Period Start] is indeed defined in the table as a date data type.
• I had included the hash marks in the SQL in VBA, but that's when I received the runtime error. I even put in a breakpoint just before the Execute statement, used the immediate window to query the strSQL variable, and then copied the generated SQL into a new query in Access. It ran just fine, so I don't know why there was a problem when it ran in the code.
• Feedback regarding the Format and IsMissing functions as well as the DAO vs. ADO specification for the database object is very useful. Thank you!
• I agree that the two comments included in the error routine are useless. I had copied that routine from someone else and never really paid attention to the comments.

Again, thank you all for your valuable feedback. It's nice to know that there are folks like you out there to help when I'm in need of it.