|
Home > Archive > MS Access database support > February 2006 > Formatting as Date in a query expression
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Formatting as Date in a query expression
|
|
| igendreau 2006-02-20, 11:24 am |
| Hopefully this is a simple fix. I'm writing a select query, and I want
the user to enter a start date when the query opens, so I have this
expression:
StartDate: [Enter a start date:]
So I open the query, it prompts me and records the input. However,
what I need is for access to recognize that input as a date, which it's
not. I know this because in another query I have an equation involving
my StartDate field:
xVariable: [FutureDate]-[StartDate]
which returns nothing but #Error. If I change my StartDate equation
to:
StartDate: Date()
and run my second query, it gives me the number of days difference
between my FutureDate and Date(), like I want it to. That leads me to
believe that when I change StartDate to an input, the input isn't being
read in as a date.
Easy to fix? Is there a function that will force my input to be
formatted as a date field?
| |
| Anthony England 2006-02-20, 11:24 am |
| "igendreau" < ian_gendreau@hermanm
iller.com> wrote in message
news:1140449547.286834.172340@o13g2000cwo.googlegroups.com...
> Hopefully this is a simple fix. I'm writing a select query, and I want
> the user to enter a start date when the query opens, so I have this
> expression:
>
> StartDate: [Enter a start date:]
>
> So I open the query, it prompts me and records the input. However,
> what I need is for access to recognize that input as a date, which it's
> not. I know this because in another query I have an equation involving
> my StartDate field:
>
> xVariable: [FutureDate]-[StartDate]
>
> which returns nothing but #Error. If I change my StartDate equation
> to:
>
> StartDate: Date()
>
> and run my second query, it gives me the number of days difference
> between my FutureDate and Date(), like I want it to. That leads me to
> believe that when I change StartDate to an input, the input isn't being
> read in as a date.
>
> Easy to fix? Is there a function that will force my input to be
> formatted as a date field?
While looking at your query in design view, select Query>Parameters from the
menu and enter StartDate as DateTime. When you look at the SQL of your
query, you should now have something like:
PARAMETERS [StartDate] DateTime;
SELECT * FROM MyTable WHERE
SomeDate<[StartDate];
Although, this will help letting users enter dates via an input box can lead
to some interesting results.
|
|
|
|
|