SQL Date help

Using Access...

I need to "select * from tablename where datefield >= date()"

This throws an error, can you help with syntax?

Re: SQL Date help

"Ted Dawson" wrote in message
news:OJ0iTLGgGHA.4172@TK2MSFTNGP04.phx.gbl...
> Using Access...
>
> I need to "select * from tablename where datefield >= date()"
>
> This throws an error, can you help with syntax?


Whats the error?

Is datefield a date type field?

Re: SQL Date help

Ted Dawson wrote:
> Using Access...
>
> I need to "select * from tablename where datefield >= date()"
>
> This throws an error, can you help with syntax?

SELECT * FROM tbl WHERE datefield >= '" & Date() & "'

--
Mike Brind

Re: SQL Date help

Mike Brind wrote on 26 mei 2006 in microsoft.public.inetserver.asp.db:

>
> Ted Dawson wrote:
>> Using Access...
>>
>> I need to "select * from tablename where datefield >= date()"
>>
>> This throws an error, can you help with syntax?
>
> SELECT * FROM tbl WHERE datefield >= '" & Date() & "'
>
> --
> Mike Brind

Or use the Jet-engine internal now()

"SELECT * FROM tbl WHERE datefield >= Now()"

In both cases datefield must be a field of type date/time

What was the text of the error


--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Re: SQL Date help

Evertjan. wrote:
> Mike Brind wrote on 26 mei 2006 in microsoft.public.inetserver.asp.db:
>
> >
> > Ted Dawson wrote:
> >> Using Access...
> >>
> >> I need to "select * from tablename where datefield >= date()"
> >>
> >> This throws an error, can you help with syntax?
> >
> > SELECT * FROM tbl WHERE datefield >= '" & Date() & "'
> >
> > --
> > Mike Brind
>
> Or use the Jet-engine internal now()
>
> "SELECT * FROM tbl WHERE datefield >= Now()"
>
> In both cases datefield must be a field of type date/time
>
> What was the text of the error
>

The OP's syntax should work too. Mine shouldn't - it should throw an
error. If it doesn't, it's because he has a text field where his dates
should be. I should have delimited mine with #'s.

--
Mike Brind

Re: SQL Date help

Ted Dawson wrote:
> Using Access...
>
> I need to "select * from tablename where datefield >= date()"
>
> This throws an error, can you help with syntax?

As the others have said, this is the proper syntax.

This is obviously not the actual sql statement that is throwing the error.
It makes it very difficult for us to help you when you
1) Don't show us the actual text of the error message and
2) Don't show us the actual sql statement that is causing the error and
3) Don't provide details about the datatypes of the fields involved

So now we have to guess. Others have guessed that your datefield is actually
a text field. They may be correct

My guess is that this is a sql statement that works when you run it using
the Access Query Builder (you HAVE tried that I hope - that should be the
first step in your debugging process whenever a problem of this type
occurs), but not when you run it via ADO from an ASP page.
If so, I suspect you have a reserved keyword problem: i.e., you have a field
or table name that is a reserved keyword. See
(http://www.aspfaq.com/show.asp?id=2080)

If your field name is a reserved keyword, then you have two choices:
1. My recommendation is to change the name of the field to a non-reserved
word
2. You can prevent ADO from trying to interpret the word by surrounding it
with brackets whenever you use it in a query run via ADO:
select ... where [Date] >= date()

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: SQL Date help

Bob Barrows [MVP] wrote:
> Ted Dawson wrote:
> > Using Access...
> >
> > I need to "select * from tablename where datefield >= date()"
> >
> > This throws an error, can you help with syntax?
>
> As the others have said, this is the proper syntax.
>
> This is obviously not the actual sql statement that is throwing the error.
> It makes it very difficult for us to help you when you
> 1) Don't show us the actual text of the error message and
> 2) Don't show us the actual sql statement that is causing the error and
> 3) Don't provide details about the datatypes of the fields involved
>
> So now we have to guess. Others have guessed that your datefield is actually
> a text field. They may be correct
>
> My guess is that this is a sql statement that works when you run it using
> the Access Query Builder (you HAVE tried that I hope - that should be the
> first step in your debugging process whenever a problem of this type
> occurs), but not when you run it via ADO from an ASP page.
> If so, I suspect you have a reserved keyword problem:

My money is moving swiftly over to this particular pony...... :-)

--
Mike Brind

Re: SQL Date help

"Mike Brind" wrote in message
news:1148646057.457755.154150@g10g2000cwb.googlegroups.com.. .
>
> Bob Barrows [MVP] wrote:
>> Ted Dawson wrote:
>> > Using Access...
>> >
>> > I need to "select * from tablename where datefield >= date()"
>> >
>> > This throws an error, can you help with syntax?
>>
>> As the others have said, this is the proper syntax.
>>
>> This is obviously not the actual sql statement that is throwing the
>> error.
>> It makes it very difficult for us to help you when you
>> 1) Don't show us the actual text of the error message and
>> 2) Don't show us the actual sql statement that is causing the error and
>> 3) Don't provide details about the datatypes of the fields involved
>>
>> So now we have to guess. Others have guessed that your datefield is
>> actually
>> a text field. They may be correct
>>
>> My guess is that this is a sql statement that works when you run it using
>> the Access Query Builder (you HAVE tried that I hope - that should be the
>> first step in your debugging process whenever a problem of this type
>> occurs), but not when you run it via ADO from an ASP page.
>> If so, I suspect you have a reserved keyword problem:
>
> My money is moving swiftly over to this particular pony...... :-)
>
> --
> Mike Brind
>



Now that I have been properly bitch-slapped, I want to thank all of you for
your help. My original syntax is correct and is working properly. I don't
know why it generated an error last week.