Prev: Calender to switch to the month
Next: Query to see what percentage of people scored a specific number
From: John W. Vinson on 26 Apr 2010 01:55
On Sun, 25 Apr 2010 22:28:01 -0700, sam <sam(a)discussions.microsoft.com> wrote:

>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 W. Vinson [MVP]
From: John Spencer on 26 Apr 2010 09:24
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

sam wrote:
> 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.
>
> "Duane Hookom" wrote:
>
>> You can use the built-in functions of Date() and Time() in the query.
>>
>> Today: Date()
>> RightNow: Time()
>>
>> --
>> Duane Hookom
>> Microsoft Access MVP
>>
>>
>> "sam" wrote:
>>
>>> Hi All,
>>>
>>> How can I display current date and time in query output?
>>> Is there a way to do this?
>>>
>>> Thanks in advance
First  |  Prev  | 
Pages: 1 2
Prev: Calender to switch to the month
Next: Query to see what percentage of people scored a specific number