Parameter query not working with combo box

[JonathanT]

I have a parameter query that I want to get a value from a single combo box on a form. In the combo box I want the values to come from a field in a table. The query returns no results when I run it. If I use a combo box that has a value list the query works fine. It doesn’t work when the combo box gets it’s values from a table. Here is the row source information from the combo box

SELECT [AllDataTBL].[ID], [AllDataTBL].[PropertyDate] FROM [AllDataTBL] ORDER BY [PropertyDate] DESC; The row source type is 'Table/Query'

Any help would be appreciated. I'm new to this. Happy to provide more information.

[ssanfu]

And the parameter query SQL looks like....??????

If I use a combo box that has a value list the query works fine

What are the value list members?

[JonathanT]

Here is the Query and the Criteria is [Forms]![DisplayDataFrm]![Testing] .

The value list works. It's when I try to have the combo box get it's values from a table that the query doesn't work. Examples of the Table values are 10/20/17 Denver, 10/20/17 Houston etc. The table field is formatted as short text

[Micron]

Unless you're specifying column 2 as the bound column, you are returning the ID value to the PropertyDate field. The combo needs to have 2 columns with the first width being 0" and move the expression to the ID field of the query.

[ssanfu]

Another option is to change the combo box row source: The "ID" field is not being used, so change from

SELECT [AllDataTBL].[ID], [AllDataTBL].[PropertyDate] FROM [AllDataTBL] ORDER BY [PropertyDate] DESC;

to

SELECT DISTINCT [AllDataTBL].[PropertyDate] FROM [AllDataTBL] ORDER BY [PropertyDate] DESC;

------------------------------------------------------------------------

I do see other issues:

- Why are tables "AllDataTBL" and "PropertyDataqry" joined by the "ID" field? Or is "PropertyDataqry" a query? Is "PropertyDataqry" already filtered?

- "Property" is a reserved word in Access and shouldn't be used as an object name (in this case a field name).
Also, "Property" is not very descriptive.... "Property" of what?? Color of a house? It is a commercial building/residential home? A location??

- If field "PropertyDate" values are like "10/20/17 Denver", "10/20/17 Houston", you are violating normalization rules - specifically the First Normal Form (1NF)

A table is said to be in First Normal Form if:
1) there is no row or column order
2) each row (record) is unique
3) each row by column value (field) contains exactly one value
4) there are no repeating columns

So field "PropertyDate" is violating rule #3; you have a date and a city as data in one field. There should be two fields - one for the date and one for the city.



Just something to consider.......

[JonathanT]

Stupid mistake on my part. I changed the name of the combo box and forgot to change the name in the criteria of the query

[ssanfu]

Sometimes you can't see the forest for the trees...

Been there, done that (alot)......

Good luck with your project.