cycle thru list adding date

[JohnE]

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 Fye]

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]

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

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

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 W. Vinson]

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.

 

[Dale Fye]

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

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

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

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