Subform RecordSource Error 3008

[thelonelyghost]

Wow, that was a blonde-moment right there. I forgot about my ability to attach files to posts and I will most definitely archive it before I post. Maybe it's because most forums I troll are vehemently against filehosting on the same server as the forum...

ANYWAY! Attached below is the database with all of the sensitive data deleted. There are some tables, forms, and reports that are obsolete since I picked up work from an inexperienced coworker's template. I tried to delete any unused ones as I came across them too.

The form I'm messing with is called SearchView.

[patjones]

OK...

When I make a selection from the combo box and hit Search, I get Error 3464: Data type mismatch. Are you having this problem or is there something else I need to be doing?

[thelonelyghost]

OK...

When I make a selection from the combo box and hit Search, I get Error 3464: Data type mismatch. Are you having this problem or is there something else I need to be doing?

I wasn't having that issue because I tried inputting only one bit of data into one of the text boxes. I think I put "6" in for CTSLogNumber (which should have shown a single record) and it yelled at me for having a zero-length value for Make.

EDIT: I just tried it in the exact same way as before and it throws a different error. Error 2001: "You canceled the previous operation."

EDIT: Re-checked to make sure the fields allowed for zero-length strings and discovered not all of them were. Fixed that issue, but the error 2001 persists.

[patjones]

I see one problem already. I don't know if you are experiencing Error 3464, but this issue should be corrected nonetheless...

When I breakpoint your code and Debug.Print BuildSQLStr, I get this:

SELECT * 
FROM [tblReturnLog] 
WHERE DateReceived>= '' OR 
      DateReceived<= '' OR  
      CompletionDate>= '' OR 
      CompletionDate<= '' OR 
      FaultCode LIKE '**' OR 
      Make= '11' OR 
      Model= '47' OR 
      Mileage>= '0' OR 
      Mileage<= '999999' OR 
      MiscIDNumber LIKE '**' OR 
      Misc LIKE '**' 
ORDER BY [tblReturnLog].[Customer]

One problem here is that DateReceivedand CompletionDate are date type fields in the table, but you're trying to compare them to strings, which is why the data type mismatch error is popping up.

You'll have to build the date tests differently, perhaps using IIf( ), Nz( ), Switch( ) or some other function that will allow you to conditionally select the comparison based on whether or not dates were entered in the search form.

Pat

[patjones]

I wasn't having that issue because I tried inputting only one bit of data into one of the text boxes. I think I put "6" in for CTSLogNumber (which should have shown a single record) and it yelled at me for having a zero-length value for Make.

EDIT: I just tried it in the exact same way as before and it throws a different error. Error 2001: "You canceled the previous operation."

EDIT: Re-checked to make sure the fields allowed for zero-length strings and discovered not all of them were. Fixed that issue, but the error 2001 persists.

When I do that same search as you (6 for CTSLogNumber), I get Error 2467: The expression you entered refers to an object that is closed or doesn't exist.

When I output the SQL for that case, I see that the first test in the WHERE clause is WHERE CTSLogNumber= '6'. This is another data type mismatch; in your table, CTSLogNumber is a Number data type, but you're comparing it to a string.

I'd focus on getting the SQL correct before moving forward. At least then we can eliminate the SQL as the cause of your problems (should the problems still exist).

Pat

[thelonelyghost]

It sounds like a majority of my problems stem from datatypes (specifically strings) not being able to be compared with other datatypes. Although I still have no clue why I'm getting error 2001 and you're not, I'll look into making sure the datatypes re-cast once the information is collected from the controls. Is that what you were referring to by 'getting the SQL correct'?

[patjones]

Yes. Numbers don't need to be wrapped in anything; dates need to be surrounded by #'s: WHERE CTSLogNumber = 6 AND DateReceived >= #1/1/2003#. To test this out, I hardcoded the sub-form recordsource as such:

Me.frmSubForm.Form.RecordSource = 
"SELECT * 
 FROM [tblReturnLog]   
 WHERE CTSLogNumber = 6 OR 
       DateReceived IS NULL OR 
       DateReceived IS NULL OR 
       CompletionDate IS NULL OR 
       CompletionDate IS NULL OR 
       FaultCode LIKE '**' OR 
       Mileage>= 0 OR 
       Mileage <= 999999 OR 
       MiscIDNumber LIKE '**' OR 
       Misc LIKE '**' 
ORDER BY [tblReturnLog].[CTSLogNumber]"

When this ran, no errors came up. When I tried a simpler query:

Me.frmSubForm.Form.RecordSource = 
"SELECT * 
 FROM [tblReturnLog] 
 WHERE CTSLogNumber = 6 OR 
       DateReceived IS NULL 
 ORDER BY [tblReturnLog].[CTSLogNumber]"

The sub-form properly refreshed with nothing in it.

Pat

[NeoPa]

It sounds like Pat is steering you well here.

Here are a couple of links to info on how literals should be formatted within a SQL string - Quotes (') and Double-Quotes (") - Where and When to use them & Literal DateTimes and Their Delimiters (#).

[thelonelyghost]

Yes. Numbers don't need to be wrapped in anything; dates need to be surrounded by #'s: WHERE CTSLogNumber = 6 AND DateReceived >= #1/1/2003#. To test this out, I hardcoded the sub-form recordsource as such:

Me.frmSubForm.Form.RecordSource = 
"SELECT * 
 FROM [tblReturnLog]   
 WHERE CTSLogNumber = 6 OR 
       DateReceived IS NULL OR 
       DateReceived IS NULL OR 
       CompletionDate IS NULL OR 
       CompletionDate IS NULL OR 
       FaultCode LIKE '**' OR 
       Mileage>= 0 OR 
       Mileage <= 999999 OR 
       MiscIDNumber LIKE '**' OR 
       Misc LIKE '**' 
ORDER BY [tblReturnLog].[CTSLogNumber]"

When this ran, no errors came up. When I tried a simpler query:

Me.frmSubForm.Form.RecordSource = 
"SELECT * 
 FROM [tblReturnLog] 
 WHERE CTSLogNumber = 6 OR 
       DateReceived IS NULL 
 ORDER BY [tblReturnLog].[CTSLogNumber]"

The sub-form properly refreshed with nothing in it.

Pat

Ah, that dispells a lot of the panic I had going. I was afraid I would have to find a transforming function for each datatype so that it could compare a string to a number! Is that all I would need to do for the datatype errors you think? Just append #'s around the dates?

Here's what I'm thinking of changing in BuildSQLStr:

strSQLContent = strSQLContent & lib.GreaterThanAttachAnd("DateReceived", "#" & Me.dtMinDateR.Value & "#", strDelim)
  strSQLContent = strSQLContent & lib.LessThanAttachAnd("DateReceived", "#" & Me.dtMaxDateR.Value & "#", strDelim)
  strSQLContent = strSQLContent & lib.GreaterThanAttachAnd("CompletionDate", "#" & Me.dtMinDateClosed.Value & "#", strDelim)
  strSQLContent = strSQLContent & lib.LessThanAttachAnd("CompletionDate", "#" & Me.dtMaxDateClosed.Value & "#", strDelim)

Should that be it? I keep running into the Error 2001 and I still don't have a clue why. While we're testing this, I'm using the exact same database file as you too! (-_-)

EDIT: Ironic how the article I commented on just yesterday came up in today's list of helpful resources for a problem I'm stumped on... Also, I'm revising the code above now, since I'm reading through Literal DateTimes and Their Delimiters (#)

EDIT: ... and it looks like I got the format right, although I had to add a little something to the if-null portion of the Greater-/LessThanAttachA nd code so that it ignores '##' strings. Since my version is magically giving me the 2001 error, I can't test this. For a < statement it would matter if it counts the date in MM/DD/YYYY format (which is correct) or DD/MM/YYYY format, so is this a valid correction?

[NeoPa]

Many experts will tell you that all you need do for dates is surround them with #s. This is not actually true, though it will seem so in most situations (especially in the US).

See the linked article for the full SP on this and how to handle it reliably and portably.

[patjones]

Ah, that dispells a lot of the panic I had going. I was afraid I would have to find a transforming function for each datatype so that it could compare a string to a number! Is that all I would need to do for the datatype errors you think? Just append #'s around the dates?

Here's what I'm thinking of changing in BuildSQLStr:

strSQLContent = strSQLContent & lib.GreaterThanAttachAnd("DateReceived", "#" & Me.dtMinDateR.Value & "#", strDelim)
  strSQLContent = strSQLContent & lib.LessThanAttachAnd("DateReceived", "#" & Me.dtMaxDateR.Value & "#", strDelim)
  strSQLContent = strSQLContent & lib.GreaterThanAttachAnd("CompletionDate", "#" & Me.dtMinDateClosed.Value & "#", strDelim)
  strSQLContent = strSQLContent & lib.LessThanAttachAnd("CompletionDate", "#" & Me.dtMaxDateClosed.Value & "#", strDelim)

Should that be it? I keep running into the Error 2001 and I still don't have a clue why. While we're testing this, I'm using the exact same database file as you too! (-_-)

EDIT: Ironic how the article I commented on just yesterday came up in today's list of helpful resources for a problem I'm stumped on... Also, I'm revising the code above now, since I'm reading through Literal DateTimes and Their Delimiters (#)

Well, it's not going to be that simple because there will clearly be a lot of instances where the date fields are empty, and you cannot do a date comparison with ##. As it happens, Access is very finicky about null dates.

I suggest looking into the following functions: Nz(), IIf(), Switch().

Pat

[NeoPa]

thelonelyghost:
EDIT: Ironic how the article I commented on just yesterday came up in today's list of helpful resources for a problem I'm stumped on... Also, I'm revising the code above now, since I'm reading through Literal DateTimes and Their Delimiters (#)

EDIT: ... and it looks like I got the format right, although I had to add a little something to the if-null portion of the Greater-/LessThanAttachA nd code so that it ignores '##' strings. Since my version is magically giving me the 2001 error, I can't test this. For a < statement it would matter if it counts the date in MM/DD/YYYY format (which is correct) or DD/MM/YYYY format, so is this a valid correction?

While this (retrospective editing) is commendably careful of resource, it can lead to a certain difficulty in following the flow. Many members depend on notifications of responses to know that they should recheck the thread. I would advise only making such changes (particularly if pertinent as some of yours are) for a very short period after the post was initially submitted. Changing details like spelling mistakes are not so much of an issue and can still be done this way of course. Otherwise, I'd recommend that you submit a new post then all involved will know there is something further to respond to.

Of course I know this is coming from the best of motives, but I hope I've explained why this is nevertheless a practice you'd do better to avoid in future.

[patjones]

For Error 2001, you should break point the code in the button click event and step through it from there to see where the error is coming from.

[thelonelyghost]

@NeoPa: I read the article and I got confused by a part of it. If I've already got a date in the form MM/DD/YYYY, would I use Format() like this? Format(Me.dtMax DateR, "MM/DD/YYYY")

Also, I'll try to double-post more. ;P Everywhere else I've been people have been double-post nazis and banned users for doing it enough. Apparently this is not the case here.

Well, it's not going to be that simple because there will clearly be a lot of instances where the date fields are empty, and you cannot do a date comparison with ##. As it happens, Access is very finicky about null dates.

I suggest looking into the following functions: Nz(), IIf(), Switch().

Pat

I just changed the SQL building functions to be this instead:

'This code is meant to a string as part of a dynamic SQL statement.
'strField = field (i.e. 'strJusticeLeague')
'strValue = control (i.e. 'Me.strBatman')
'strDelim = 'AND' or 'OR'.

Public Function EqualsAttachAnd(strField As Variant, strValue As Variant, strDelim As Variant) As String

  If strValue = "''" Or strValue = "" Or strValue = "##" Then
  EqualsAttachAnd = ""
  Exit Function
  Else
    strValue = Trim(strValue)
    EqualsAttachAnd = strDelim & strField & "= '" & strValue & "'"
  End If

End Function

'This code is meant to a string as part of a dynamic SQL statement.
'strField = field (i.e. 'strJusticeLeague')
'strValue = control (i.e. 'Me.strBatman')
'strDelim = 'AND' or 'OR'.

Public Function GreaterThanAttachAnd(strField As Variant, strValue As Variant, strDelim As Variant) As String

  If strValue = "''" Or strValue = "" Or strValue = "##" Then
  GreaterThanAttachAnd = ""
  Exit Function
  Else
    strValue = Trim(strValue)
    GreaterThanAttachAnd = strDelim & strField & ">= '" & strValue & "'"
  End If
  
End Function

'This code is meant to a string as part of a dynamic SQL statement.
'strField = field (i.e. 'strJusticeLeague')
'strValue = control (i.e. 'Me.strBatman')
'strDelim = 'AND' or 'OR'.

Public Function LessThanAttachAnd(strField As Variant, strValue As Variant, strDelim As Variant) As String

  If strValue = "''" Or strValue = "" Or strValue = "##" Then
  LessThanAttachAnd = ""
  Exit Function
  Else
    strValue = Trim(strValue)
    LessThanAttachAnd = strDelim & strField & "<= '" & strValue & "'"
  End If
  
End Function

'This code is meant to a string as part of a dynamic SQL statement.
'strField = field (i.e. 'strJusticeLeague')
'strValue = control (i.e. 'Me.strBatman')
'strDelim = 'AND' or 'OR'.

Public Function LikeAttachAnd(strField As Variant, strValue As Variant, strDelim As Variant) As String

  If strValue = "''" Or strValue = "" Or strValue = "##" Then
  LikeAttachAnd = ""
  Exit Function
  Else
    strValue = Trim(strValue)
    LikeAttachAnd = strDelim & strField & " LIKE '" & strValue & "*'"
    If LikeAttachAnd = strDelim & strField & " LIKE '**'" Or LikeAttachAnd = strDelim & strField & " LIKE '*'" Then LikeAttachAnd = ""
  End If
  
End Function

Additionally, I'm still new to the VBA Debugging world so excuse that ignorance. I tried reading up on it but the guides I read didn't help too much. I just needed a place to start stepping through the code.

[thelonelyghost]

It looks like it throws the error (2001) at 'End Function', though I still don't know why.

In order to make it easy to update what fields are processed (hence the AttachAnd functions) I created a function to format the date strings as dates. I then had it transform the date values for Min-/Max- Date Received and Date Closed before they're input into the AttachAnd functions.