Field exists in table and query but causes an issue in VB6 code.

[wedd]

I have a object in my database that is causing an issue when I upload my database. The strange thing is that the field name is in my query and table; but for a reason not known to me it raises an issue as not being defined. to compile it.
I know it's a spurious error because I haven't made any significant changes to the code. In fact, I've rolled the code back to the last deployed version (which I know works fine) and I still get the error. I've also commented out all the code in the template and I still get the error. I've also removed and re-added all references (same error), and removed all the references and added them back, one by one, until the resultant compile errors are resolved, at which point I'm left with the spurious "User-defined type not defined" error. (I'm going to call this a UDTND error, from now, to avoid driving you all mad.) It only happens with this field called "venue", but I don't see how it can be anything to do with this field because I have other fields which had its name changed an no issues were raised in the code or query.
Interestingly, the error is subtly occurs inthe following ways:

It happens when the database is opened, not just when the code is compiled. I've tried Googling it but I just get a bazillion results from novice developers asking why they get this error, with responses telling them that they either need to declare the missing type, correct the spelling of the offending variable type, or add a reference to a missing library. I've been banging my head against my screen all morning, and that's helped about as much as all the other things I've tried (i.e. not at all). I have a feeling that this is something to do with a messed-up reference, but they're all fine, and I've removed and re-added them, which I would expect to resolve that sort of problem. I am using access 2003.I have very limited experience at trouble shooting such issues. Can this be resolved? If so, what should I do to allow this field "venue" to be recognised as valid within my code? (Code is listed below)


Thanks for your contributions!!!:friends:


Here is the full code...I have also highlighted in red where the issues have been raised

Private Sub cmdSearch_Click()
On Error Resume Next
Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "
'tblDescription qrySearchCriteriaSub
If Me![Location] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Location = """ & Location & """"
End If

If Me![Title] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Title like """ & Title & "*"""
End If

If Me![AreaCode] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.AreaCode = """ & AreaCode & """"
End If

If Me![Venue] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Venue = """ (causes an issue)

& Venue & """"
End If

If Me![StartDate] <> "" And EndDate <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Date of Booking between #" & Format(StartDate, "dd-mmm-yyyy") & "# and #" & Format(EndDate, "dd-mmm-yyyy") & "#"
End If

If Me![Description] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Description like """ & Description & "*"""
End If

sSql = "SELECT DISTINCT [CustomerReservationBookingID], [Location],[Title],[AreaCode],[Venue],[Date of Booking],[Description] from qrySearchCriteriaSub " & sCriteria
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.RecordSource = sSql
Forms![frmSearchCriteriaMain]![frmSearchCriteriaSub].Form.Requery
',[Title],[AreaCode],[Venue],[StartDate],[EndDate],[Description]

'frmSearchCriteriaSub.Requery
End Sub


Private Sub Form_Load() (Highligted in yellow)
StartDate.SetFocus
Venue = -1
frmSearchCriteriaSub.Requery
Venue = Null
StartDate.SetFocus
End Sub

[jrajul]

One of these two changes might be the problem.

1) If Me![Venue] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Venue is Null"


or


2) If Me![Venue] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Venue = """""



or


3) If Me![Venue] <> "" Then
sCriteria = sCriteria & " AND Venue = """""



or


4) If Me![Venue] <> "" and len(scriteria) > 1 Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub.Venue = """""



Maybe the SQL driver prefers "is null" to an empty string.
Remember you need 4 VBA quotation marks to make two in the program.
Sometimes the field name does not include the "tableName." before it.
If the sCriteria string is empty then starting a criteria with "AND" will confuse Access.
I Hope that one of these hit the spot.