Search Form using Combo box.

[Olufisola]

Hello All,

I am in need of urgent solution to this protracted issue I am having with Search Form using Combo box. The error code 3464 - Data type mismatch in criteria expression. The code is pasted below: the issue is with shaded 4 (line 4)

Private Sub cboYear_AfterUpdate()
Dim myGermplasm As String
myGermplasm = " Select * from tblGermplasm where Year = '" & Me.cboYear & "'"
Me.tblGermplasm_subform.Form.RecordSource = myGermplasm
Me.tblGermplasm_subform.Form.Requery
End Sub

Your prompt support is highly appreciated. Thank you

 

[pbaldy]

What is the data type of the Year field (which is a bad name since there's a Year() function)? If it's numeric you don't want the delimiters:

myGermplasm = " Select * from tblGermplasm where Year = " & Me.cboYear

 

[Olufisola]

Thank you for your quick reply Paul. After applying what you posted, when I select a year e.g. 2006 from the cboYear drop-down, the tblGermplasm_subform became empty instead of selecting all 2006 associated rows.
Please help me out on this, I've been struggling with it for quite a while. Thank you in advance.

 

[bob fitz]

Show us the SQL statement for the combo box.

Please answer the question that Paul asked regarding the data type of the field called Year

 

[Olufisola]

Here is the SQL statement for combo box:

Private Sub cboYear_AfterUpdate()
Dim myGermplasm As String
myGermplasm = " Select * from tblGermplasm where Year = " & Me.cboYear
Me.tblGermplasm_subform.Form.RecordSource = myGermplasm
Me.tblGermplasm_subform.Form.Requery
End Sub

Yes, the data type of the field called Year is numeric.

 

[bob fitz]

That's not what we need to see. The combo box is usually populated by using a query as it's row source property. Please show us the SQL statement of that query.

 

[Olufisola]

Hi Bob,
Here is the row source property:
SELECT [tblGermplasmYear].[ID], [tblGermplasmYear].[Year] FROM tblGermplasmYear ORDER BY [Year];

row source type: Table/Query

 

[bob fitz]

Your reference to combo box needs to be
Me.cboYear.Column(1)

 

[arnelgp]

What fieldtype is Year in your table, numeric or string.

For string:
myGermplasm="SELECT [tblGermplasmYear].[ID], [tblGermplasmYear].[Year] FROM tblGermplasmYear WHERE [ID]=""" & Me.cboYear & """ ORDER BY [Year];"


For numeric:

myGermplasm="SELECT [tblGermplasmYear].[ID], [tblGermplasmYear].[Year] FROM tblGermplasmYear WHERE [ID]=" & Me.cboYear & " ORDER BY [Year];"

 

[bob fitz]

The OP has already confirmed data type as numeric.

 

[Olufisola]

Thanks a great deal everyone that has contributed, especially bob fitz.

The solution worked perfectly. I am so grateful.

 

[Olufisola]

Your reference to combo box needs to be
Me.cboYear.Column(1)

I also have two other tables that their data type is short text e.g. tblLocation and tblColdStore

what would change from your solution above?

 

[pbaldy]

For text you'd include the apostrophe delimiters like you had in your first post. More detailed info and a sample db:

http://www.baldyweb.com/BuildSQL.htm

 

[Olufisola]

It worked fine. Thanks a million Paul.

 

[pbaldy]

Happy to help!