Unbound Combo Box Default Value Based on Query

[ScottMsp]

Hello,

I have a query based on a table. The query has one field and it is the date
the data was loaded. The query limits it to unique values as I wanted to
only see the four different dates that are loaded on the 50k+ records.

On a form in a combo box, I have been able to display those dates and select
from them which then drives other queries.

My question then is, I want the default value of the combo box to always
default to the most current date in the table. For instance if I have 4
dates (5/4/2008, 6/1/2008, 8/1/2008, and 4/5/2009), I want 4/5/2009 to be the
default value. When I load more records into the table that have a newer
date, I want the default value to be the new date.

Thanks in advance for all your help.

 

[Arvin Meyer MVP]

The rowsource of the combo should read something like:

SELECT Max([DateField]) FROM YourTable Order By [DateField] DESC;

 

[KARL DEWEY]

Try copying the query you are using as source for the combo to the Default
property and edit to something like this --
=SELECT Max([YourDateField]) AS MaxDate FROM YourTable;

 

[Marshall Barton]

I have a query based on a table. The query has one field and it is the date
the data was loaded. The query limits it to unique values as I wanted to
only see the four different dates that are loaded on the 50k+ records.

On a form in a combo box, I have been able to display those dates and select
from them which then drives other queries.

My question then is, I want the default value of the combo box to always
default to the most current date in the table. For instance if I have 4
dates (5/4/2008, 6/1/2008, 8/1/2008, and 4/5/2009), I want 4/5/2009 to be the
default value. When I load more records into the table that have a newer
date, I want the default value to be the new date.

The DefaultValue property of an unbound control is near
meaningless. Instead use a line of code in the appropriate
event (probably the form's Load event) to copy the latest
date from the combo box's list. If the query sorts the
dates in descending order, the line of code would be like:
Me.thecombobox=Me.thecombobox.ItemData(0)
If the dates are sorted the other way:

Me.thecombobox=Me.thecombobox.ItemData(Me.thecombobox.ListCount-1)

 

[ScottMsp]

Karl,

I tried your solution, but it did not work. I kept getting a syntax error
message saying, "The syntax of the subquery in this expression is incorrect.
Check the subquery's syntax and enclose the subquery in paratheses."

My line in the Default Value was:
=SELECT Max([qPayPeriodDateUnique.PPD]) As MaxDate FROM qPayPeriodDateUnique;

I tried parathesis in a few places, but could not get it to work.

I am sure it works, but it just did not work for me.

Thanks for responding and trying.

-Scott

Try copying the query you are using as source for the combo to the Default
property and edit to something like this --
=SELECT Max([YourDateField]) AS MaxDate FROM YourTable;

Hello,

I have a query based on a table. The query has one field and it is the date
the data was loaded. The query limits it to unique values as I wanted to
only see the four different dates that are loaded on the 50k+ records.

On a form in a combo box, I have been able to display those dates and select
from them which then drives other queries.

My question then is, I want the default value of the combo box to always
default to the most current date in the table. For instance if I have 4
dates (5/4/2008, 6/1/2008, 8/1/2008, and 4/5/2009), I want 4/5/2009 to be the
default value. When I load more records into the table that have a newer
date, I want the default value to be the new date.

Thanks in advance for all your help.

 

[ScottMsp]

Arvin,

I tried your solution, but it did not work. I kept getting a syntax error
message saying, "You tried to execute a query that does not include the
specified expression '[qPayPeriodDateUnquie.PPD]' as part of an aggregate
function."

My line in RowSource was:
SELECT Max([qPayPeriodDateUnique.PPD]) FROM qPayPeriodDateUnique ORDER BY
[qPayPeriodDateUnique.PPD] DESC;

I was not sure how to troubleshoot that error message.

I am sure it works, but it just did not work for me.

Thanks for responding and trying.

-Scott

The rowsource of the combo should read something like:

SELECT Max([DateField]) FROM YourTable Order By [DateField] DESC;

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello,

I have a query based on a table. The query has one field and it is the
date
the data was loaded. The query limits it to unique values as I wanted to
only see the four different dates that are loaded on the 50k+ records.

On a form in a combo box, I have been able to display those dates and
select
from them which then drives other queries.

My question then is, I want the default value of the combo box to always
default to the most current date in the table. For instance if I have 4
dates (5/4/2008, 6/1/2008, 8/1/2008, and 4/5/2009), I want 4/5/2009 to be
the
default value. When I load more records into the table that have a newer
date, I want the default value to be the new date.

Thanks in advance for all your help.

 

[KARL DEWEY]

You are missing brackets -- [ ]
=SELECT Max([qPayPeriodDateUnique].[PPD]) As MaxDate FROM
qPayPeriodDateUnique;

Karl,

I tried your solution, but it did not work. I kept getting a syntax error
message saying, "The syntax of the subquery in this expression is incorrect.
Check the subquery's syntax and enclose the subquery in paratheses."

My line in the Default Value was:
=SELECT Max([qPayPeriodDateUnique.PPD]) As MaxDate FROM qPayPeriodDateUnique;

I tried parathesis in a few places, but could not get it to work.

I am sure it works, but it just did not work for me.

Thanks for responding and trying.

-Scott

Try copying the query you are using as source for the combo to the Default
property and edit to something like this --
=SELECT Max([YourDateField]) AS MaxDate FROM YourTable;

Hello,

I have a query based on a table. The query has one field and it is the date
the data was loaded. The query limits it to unique values as I wanted to
only see the four different dates that are loaded on the 50k+ records.

On a form in a combo box, I have been able to display those dates and select
from them which then drives other queries.

My question then is, I want the default value of the combo box to always
default to the most current date in the table. For instance if I have 4
dates (5/4/2008, 6/1/2008, 8/1/2008, and 4/5/2009), I want 4/5/2009 to be the
default value. When I load more records into the table that have a newer
date, I want the default value to be the new date.

Thanks in advance for all your help.

 

[Arvin Meyer MVP]

I'm sorry you need to Alias the Max expression like:

SELECT Max([DateField]) AS Expr1
FROM tblMyData
ORDER BY Max([DateField]) DESC;

So yours should be:

SELECT Max([qPayPeriodDateUnique.PPD]) As Expr1
FROM qPayPeriodDateUnique
ORDER BY [qPayPeriodDateUnique.PPD] DESC;
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Arvin,

I tried your solution, but it did not work. I kept getting a syntax error
message saying, "You tried to execute a query that does not include the
specified expression '[qPayPeriodDateUnquie.PPD]' as part of an aggregate
function."

My line in RowSource was:
SELECT Max([qPayPeriodDateUnique.PPD]) FROM qPayPeriodDateUnique ORDER BY
[qPayPeriodDateUnique.PPD] DESC;

I was not sure how to troubleshoot that error message.

I am sure it works, but it just did not work for me.

Thanks for responding and trying.

-Scott

The rowsource of the combo should read something like:

SELECT Max([DateField]) FROM YourTable Order By [DateField] DESC;

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello,

I have a query based on a table. The query has one field and it is the
date
the data was loaded. The query limits it to unique values as I wanted
to
only see the four different dates that are loaded on the 50k+ records.

On a form in a combo box, I have been able to display those dates and
select
from them which then drives other queries.

My question then is, I want the default value of the combo box to
always
default to the most current date in the table. For instance if I have
4
dates (5/4/2008, 6/1/2008, 8/1/2008, and 4/5/2009), I want 4/5/2009 to
be
the
default value. When I load more records into the table that have a
newer
date, I want the default value to be the new date.

Thanks in advance for all your help.

 

[ScottMsp]

Karl,

I went ahead and put in brackets as you suggested:
=SELECT Max([qPayPeriodDateUnique].[PPD]) as MaxDate FROM
qPayPeriodDateUnique;

This is put in the Default Value section of the Combo box under the Data Tab

I am still getting error message, "The syntax of the subquery in this
expression is incorrect. Check the subquery's syntax and enclose the
subquery in parentheses."

I am wondering what I am still missing.

Thanks in advance.
-Scott

You are missing brackets -- [ ]
=SELECT Max([qPayPeriodDateUnique].[PPD]) As MaxDate FROM
qPayPeriodDateUnique;

Karl,

I tried your solution, but it did not work. I kept getting a syntax error
message saying, "The syntax of the subquery in this expression is incorrect.
Check the subquery's syntax and enclose the subquery in paratheses."

My line in the Default Value was:
=SELECT Max([qPayPeriodDateUnique.PPD]) As MaxDate FROM qPayPeriodDateUnique;

I tried parathesis in a few places, but could not get it to work.

I am sure it works, but it just did not work for me.

Thanks for responding and trying.

-Scott

Try copying the query you are using as source for the combo to the Default
property and edit to something like this --
=SELECT Max([YourDateField]) AS MaxDate FROM YourTable;

:

Hello,

I have a query based on a table. The query has one field and it is the date
the data was loaded. The query limits it to unique values as I wanted to
only see the four different dates that are loaded on the 50k+ records.

On a form in a combo box, I have been able to display those dates and select
from them which then drives other queries.

My question then is, I want the default value of the combo box to always
default to the most current date in the table. For instance if I have 4
dates (5/4/2008, 6/1/2008, 8/1/2008, and 4/5/2009), I want 4/5/2009 to be the
default value. When I load more records into the table that have a newer
date, I want the default value to be the new date.

Thanks in advance for all your help.

 

[KARL DEWEY]

I would guess the problem is elsewhere. Test by removing the default
statement to see if you still get the error message.

Karl,

I went ahead and put in brackets as you suggested:
=SELECT Max([qPayPeriodDateUnique].[PPD]) as MaxDate FROM
qPayPeriodDateUnique;

This is put in the Default Value section of the Combo box under the Data Tab

I am still getting error message, "The syntax of the subquery in this
expression is incorrect. Check the subquery's syntax and enclose the
subquery in parentheses."

I am wondering what I am still missing.

Thanks in advance.
-Scott

You are missing brackets -- [ ]
=SELECT Max([qPayPeriodDateUnique].[PPD]) As MaxDate FROM
qPayPeriodDateUnique;

Karl,

I tried your solution, but it did not work. I kept getting a syntax error
message saying, "The syntax of the subquery in this expression is incorrect.
Check the subquery's syntax and enclose the subquery in paratheses."

My line in the Default Value was:
=SELECT Max([qPayPeriodDateUnique.PPD]) As MaxDate FROM qPayPeriodDateUnique;

I tried parathesis in a few places, but could not get it to work.

I am sure it works, but it just did not work for me.

Thanks for responding and trying.

-Scott

:

Try copying the query you are using as source for the combo to the Default
property and edit to something like this --
=SELECT Max([YourDateField]) AS MaxDate FROM YourTable;

:

Hello,

I have a query based on a table. The query has one field and it is the date
the data was loaded. The query limits it to unique values as I wanted to
only see the four different dates that are loaded on the 50k+ records.

On a form in a combo box, I have been able to display those dates and select
from them which then drives other queries.

My question then is, I want the default value of the combo box to always
default to the most current date in the table. For instance if I have 4
dates (5/4/2008, 6/1/2008, 8/1/2008, and 4/5/2009), I want 4/5/2009 to be the
default value. When I load more records into the table that have a newer
date, I want the default value to be the new date.

Thanks in advance for all your help.

 

[Marshall Barton]

I would guess the problem is elsewhere. Test by removing the default
statement to see if you still get the error message.

I went ahead and put in brackets as you suggested:
=SELECT Max([qPayPeriodDateUnique].[PPD]) as MaxDate FROM
qPayPeriodDateUnique;

This is put in the Default Value section of the Combo box under the Data Tab

Hold on a minute here guys. AFAIK, you can not use an SQL
statement in the DefaultValue property.

If you must go this route, then use DMax:

DMax("PPD", "qPayPeriodDateUnique")

Did you consider my suggestion in my initial reply. It may
require a few lines of code, but it avoids going back to the
table a second time.