PLEASE HELP: Display current Date & Time in Query Output

[sam]

Hi All,

How can I display current date and time in query output?
Is there a way to do this?

Thanks in advance

 

[Duane Hookom]

You can use the built-in functions of Date() and Time() in the query.

Today: Date()
RightNow: Time()

 

[John W. Vinson]

Hi All,

How can I display current date and time in query output?
Is there a way to do this?

Thanks in advance

In each record of the query? in a report header? on a form? What's the
context?

The builtin function Now() returns the current date and time; you could put a
calculated field in the query by typing

TheTime: Now()

in a vacant Field cell.

In a report or form, you can set the control source of a textbox to

=Now()

for the same purpose.

 

[sam]

Hi Duane,

So my query will look like: SELECT FName, LName, PhNo FROM Customer_Table
WHERE LName = "Smith" AND CurrentDate = Time();

The issue here is, I dont have Time or Date field in Customer_Table
How can I display Time or Date in query output if there is no such field as
Time or Date in Customer_Table?

Thanks in advance.

 

[sam]

Hi John,

I have a Query thats displays results in a table view, the query looks like
this.

SELECT FName, LName, PhNo FROM Customer_Table
WHERE LName = "Smith";

Now, I want current date to display in the output of this query for each
result displayed. So basically like having the query output with consists of
the following columns.

FName, LName, PhNo, CurrentDate

Is this possible?

How can I do this?

Thanks in advance

 

[John W. Vinson]

Hi John,

I have a Query thats displays results in a table view, the query looks like
this.

SELECT FName, LName, PhNo FROM Customer_Table
WHERE LName = "Smith";

Now, I want current date to display in the output of this query for each
result displayed. So basically like having the query output with consists of
the following columns.

FName, LName, PhNo, CurrentDate

Is this possible?

How can I do this?

Well, you can do this using a query

SELECT FName, LName, PhNo, Date() FROM Customer_Table
WHERE LName = "Smith";

But this will display

Smith Zachary 232-232-2323 4/25/2010
Smith David 323-323-2323 4/25/2010
Smith Margaret 232-333-2222 4/25/2010

and so on. You're not, I hope, using the *query datasheet* to view data, are
you? That's not a Query's function; you should instead base a Form (for
onscreen display) or Report (for printing) - or both - on the query, and open
THAT.

On a form or report you can put a textbox with a control source property

=Date()

to display the date the form or report is opened, rather than having the same
date repeated over and over.

In your other response in this thread you say

So my query will look like: SELECT FName, LName, PhNo FROM Customer_Table
WHERE LName = "Smith" AND CurrentDate = Time();

That's not what you want: it will find all records where the field in your
table named CurrentDate is equal to the time that you run the query, accurate
to the second, on December 30, 1899 (the zero point of the date/time field):
in other words, you'll see no records at all. And I'm guessing that there is
no field named CurrentDate in the table anyway!

If you JUST want to include the current date in each record in the query, just
use the syntax above, calling the builtin Date() function as a calculated
field in the query. To show the time use Time() instead (it will show 11:54:21
PM if you run it as I'm sending this). You can also use the Now() function; it
will show both (4/25/2010 11:54:21 PM).

BUt again... don't use query datasheets for display. That's not their purpose.

 

[John Spencer]

SELECT FName, LName, PhNo
, Now() as CurrentDateAndTime
FROM Customer_Table
WHERE LName = "Smith"

If you want the date and the time in separate fields

SELECT FName, LName, PhNo
, Date() as CurrentDate
, Time() as CurrentTime
FROM Customer_Table
WHERE LName = "Smith"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County