Changing a zero date in a date field append

[richlocus]

Hello:

I have reviewed a number of articles on how to deal with query dates that deliver a value of "12/30/1899". Apparently those dates had a value of zero in the data source. My append query shows a 12/30/1899 date for all of those dates with a value of zero in the source. It doesn't look good in the report.

See the attachment for the append query.

What update can I add to the pikdate field to change any date < 1/1/2010 to 1/1/2010? Would it be at the top of the query or in the Criteria section?
Since I am creating a table with the output of this query, it needs to be compatible with a table field defined as a date.

Thanks!
Rich Locus

[HansV]

Do you just want to change the displayed date in the query? If so:

pikdate: IIf([dbo_orderln].[pikdate]<#01/01/2010#,#01/01/2010#,[dbo_orderln].[pikdate])

Or do you want to change the dates permanently? If so, create an update query based on dbo_orderln.
Add pikdate to the query grid.
In the Update to line, enter #01/01/2010#
In the Criteria line, enter <#01/01/2010#
Execute (Run) the update query.

[richlocus]

Hans:
Thanks... perfect solution - worked first time. I'm a little rusty on my Access since 90% of my client business is Excel.
Best,
Rich Locus