I have users that threw me a curve and now want to have the query show the 
records that do not have a date in a field.  This query will then be the 
bases for a report that will be used.  The report will exported to excel and 
when it is, I then need to have it so the date exported is filled in the 
field on the table for the exported records.

I am at a loss as to how to have it go thru and place the date in the field 
on the table and ask for help on this or know of any examples/samples to look 
at.

Thank you in advance for any assistance on this.
.... John

John,

What date do you want to fill in in the Excel spreadsheet?  If it is the 
same date for each record, then you need to use the NZ function to convert 
the NULL date value to some date, something like:

SELECT Field1, field2, field3, NZ(yourTable.[Datefield], #2/29/2008#) as 
[DateField],  ...
FROM yourTable
WHERE [DateField] IS NULL

The NZ function will convert a NULL value into a zero if the second 
parameter is left blank, but because the second parameter is a variant, as 
is the function, you can pass it any data type as the 2nd parameter and it 
will return that value if the field is NULL.

HTH
Dale

"JohnE" <JohnE@discussions.microsoft.com> wrote in message 
news:D14E10BC-44EF-4342-B3A1-21ACB7DD9F8D@microsoft.com...
>I have users that threw me a curve and now want to have the query show the
> records that do not have a date in a field.  This query will then be the
> bases for a report that will be used.  The report will exported to excel 
> and
> when it is, I then need to have it so the date exported is filled in the
> field on the table for the exported records.
>
> I am at a loss as to how to have it go thru and place the date in the 
> field
> on the table and ask for help on this or know of any examples/samples to 
> look
> at.
>
> Thank you in advance for any assistance on this.
> ... John
> 

Dale, thanks for the info.  Unfortunately, it is the field in the database 
table that needs to have the export date filled in.  All of the records 
showing in the report (or the query behind the report) will have the date 
field filled in with the exporting date.  
In looking at what you have posted, it might be able to be adapted in this 
situation.  I have a pop-up menu that appears in the report preview that 
allows the user to export to excel by using a button.  I'll see what I can 
do.  
If you have any other thoughts, I am willing to try them out.
Thanks.
.... John

"Dale Fye" wrote:

> John,
> 
> What date do you want to fill in in the Excel spreadsheet?  If it is the 
> same date for each record, then you need to use the NZ function to convert 
> the NULL date value to some date, something like:
> 
> SELECT Field1, field2, field3, NZ(yourTable.[Datefield], #2/29/2008#) as 
> [DateField],  ...
> FROM yourTable
> WHERE [DateField] IS NULL
> 
> The NZ function will convert a NULL value into a zero if the second 
> parameter is left blank, but because the second parameter is a variant, as 
> is the function, you can pass it any data type as the 2nd parameter and it 
> will return that value if the field is NULL.
> 
> HTH
> Dale
> 
> "JohnE" <JohnE@discussions.microsoft.com> wrote in message 
> news:D14E10BC-44EF-4342-B3A1-21ACB7DD9F8D@microsoft.com...
> >I have users that threw me a curve and now want to have the query show the
> > records that do not have a date in a field.  This query will then be the
> > bases for a report that will be used.  The report will exported to excel 
> > and
> > when it is, I then need to have it so the date exported is filled in the
> > field on the table for the exported records.
> >
> > I am at a loss as to how to have it go thru and place the date in the 
> > field
> > on the table and ask for help on this or know of any examples/samples to 
> > look
> > at.
> >
> > Thank you in advance for any assistance on this.
> > ... John
> > 
> 
> 
> 

On Thu, 28 Feb 2008 11:38:01 -0800, JohnE <JohnE@discussions.microsoft.com>
wrote:

>Dale, thanks for the info.  Unfortunately, it is the field in the database 
>table that needs to have the export date filled in.  All of the records 
>showing in the report (or the query behind the report) will have the date 
>field filled in with the exporting date.  

Why?

Are you assuming that you must have the date stored in a table field to export
it? You don't; you can export a query with Date() in a calculated field, just
as easily as the table.

If you want to store today's date in the table, you'll need to do it in two
steps - an Update query updating the records that are to be exported, setting
the field to Date(); followed by a separate operation to export the updated
table.

Of course you'll have complexities if the same record gets exported multiple
times.
-- 
             John W. Vinson [MVP]

John,

It sounds like what you want to do is export those records that have not 
already been exported, and then fill in the field (DateExported) in the 
underlying table with that same value.  Is that the case?  Could you do more 
than one export per day?  If not, I would use an update query to update the 
DateExported field with the current date, then use the current date in the 
query that generates you report/export.  Something like:

   Dim dtToday as Date
    Dim strSQL as string

   dtToday = Date()
   strSQL= "UPDATE yourTable " _
              & "SET [DateExported] = #" & dtToday & "# " _
              & "WHERE [DateExported] IS NULL"
    currentdb.execute strsql, dbfailonerror

If you can do more than one export per day, then I would use the NOW() 
function instead of the Date() function above.  The tricky part then is that 
you won't be able to use the Now() function in your query, so you will have 
to put that value in a control on your form (could be hidden) and reference 
the control in the query.

HTH
Dale

"JohnE" <JohnE@discussions.microsoft.com> wrote in message 
news:4934E735-CE79-4163-90E8-C6108A04F07A@microsoft.com...
> Dale, thanks for the info.  Unfortunately, it is the field in the database
> table that needs to have the export date filled in.  All of the records
> showing in the report (or the query behind the report) will have the date
> field filled in with the exporting date.
> In looking at what you have posted, it might be able to be adapted in this
> situation.  I have a pop-up menu that appears in the report preview that
> allows the user to export to excel by using a button.  I'll see what I can
> do.
> If you have any other thoughts, I am willing to try them out.
> Thanks.
> ... John
>
> "Dale Fye" wrote:
>
>> John,
>>
>> What date do you want to fill in in the Excel spreadsheet?  If it is the
>> same date for each record, then you need to use the NZ function to 
>> convert
>> the NULL date value to some date, something like:
>>
>> SELECT Field1, field2, field3, NZ(yourTable.[Datefield], #2/29/2008#) as
>> [DateField],  ...
>> FROM yourTable
>> WHERE [DateField] IS NULL
>>
>> The NZ function will convert a NULL value into a zero if the second
>> parameter is left blank, but because the second parameter is a variant, 
>> as
>> is the function, you can pass it any data type as the 2nd parameter and 
>> it
>> will return that value if the field is NULL.
>>
>> HTH
>> Dale
>>
>> "JohnE" <JohnE@discussions.microsoft.com> wrote in message
>> news:D14E10BC-44EF-4342-B3A1-21ACB7DD9F8D@microsoft.com...
>> >I have users that threw me a curve and now want to have the query show 
>> >the
>> > records that do not have a date in a field.  This query will then be 
>> > the
>> > bases for a report that will be used.  The report will exported to 
>> > excel
>> > and
>> > when it is, I then need to have it so the date exported is filled in 
>> > the
>> > field on the table for the exported records.
>> >
>> > I am at a loss as to how to have it go thru and place the date in the
>> > field
>> > on the table and ask for help on this or know of any examples/samples 
>> > to
>> > look
>> > at.
>> >
>> > Thank you in advance for any assistance on this.
>> > ... John
>> >
>>
>>
>>