date criteria in VB Express query

[Daniel]

In Microsoft Access I can write a query that includes the criteria:

Between Date()-7 And Date()

to retrieve the records from a table that fall within the last week.

I'm trying to write a procedure to do this in VB.NET (2005 Express
Edition). But I always get the message that the Jet database engine
does not recognize the syntax (of the Date function I assume). I've
also tried Now() and it works but only by itself. I seem to add or
subtract time from it.

What's the best syntax to use? I'm working in the Query Designer.

Thanks!
Daniel

[Cor Ligthert [MVP]]

Daniel,

Can you show us the query (is it an SQL procedure), than we get probably
better at your problem.

I do by instance not know the statement "Between"

Cor

"Daniel" <da**********@g mail.comschreef in bericht
news:11******** **************@ i42g2000cwa.goo glegroups.com.. .

In Microsoft Access I can write a query that includes the criteria:

Between Date()-7 And Date()

to retrieve the records from a table that fall within the last week.

I'm trying to write a procedure to do this in VB.NET (2005 Express
Edition). But I always get the message that the Jet database engine
does not recognize the syntax (of the Date function I assume). I've
also tried Now() and it works but only by itself. I seem to add or
subtract time from it.

What's the best syntax to use? I'm working in the Query Designer.

Thanks!
Daniel

[Daniel]

Well, in Access I would write this query to get the results I want:

SELECT tblDates.* FROM tblDates
WHERE tblDates.entry_ date BETWEEN Date()-7 And Date();

This uses the built in Date() function which retrieves the current
system date to pull everything from the "tblDates" table that has an
entry_date within the last 7 days.

How do I write this query in VB 2005 Express in the Query Designer? I
am working with an OLEDB connection object linked to my Access 2003
(Microsoft Jet 4.0) database. The problem seems to be that when the
TableAdapter object tries to query the Jet db engine, the Jet engine
returns an error that it doesn't know what the Date() function is. I
thought the whole point of the adapter object was to take your query
and translate it into syntax compatible with the type of database you
are querying. But instead, it seem to be passing the literal value of
my query directly to Jet.

Thanks so much!

On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:

Daniel,

Can you show us the query (is it an SQL procedure), than we get probably
better at your problem.

I do by instance not know the statement "Between"

Cor

"Daniel" <danielcre...@g mail.comschreef in berichtnews:11* *************** ******@i42g2000 cwa.googlegroup s.com...

In Microsoft Access I can write a query that includes the criteria:

Between Date()-7 And Date()

to retrieve the records from a table that fall within the last week.

I'm trying to write a procedure to do this in VB.NET (2005 Express
Edition). But I always get the message that the Jet database engine
does not recognize the syntax (of the Date function I assume). I've
also tried Now() and it works but only by itself. I seem to add or
subtract time from it.

What's the best syntax to use? I'm working in the Query Designer.

Thanks!
Daniel

[rowe_newsgroups]

How about using the following as you're query:

<pseudocode>

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date

>=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and tblDates.entry_ date < " & now().tostring( )

</pseudocode>

Thanks,

Seth Rowe
Daniel wrote:

Well, in Access I would write this query to get the results I want:

SELECT tblDates.* FROM tblDates
WHERE tblDates.entry_ date BETWEEN Date()-7 And Date();

This uses the built in Date() function which retrieves the current
system date to pull everything from the "tblDates" table that has an
entry_date within the last 7 days.

How do I write this query in VB 2005 Express in the Query Designer? I
am working with an OLEDB connection object linked to my Access 2003
(Microsoft Jet 4.0) database. The problem seems to be that when the
TableAdapter object tries to query the Jet db engine, the Jet engine
returns an error that it doesn't know what the Date() function is. I
thought the whole point of the adapter object was to take your query
and translate it into syntax compatible with the type of database you
are querying. But instead, it seem to be passing the literal value of
my query directly to Jet.

Thanks so much!

On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:

Daniel,

Can you show us the query (is it an SQL procedure), than we get probably
better at your problem.

I do by instance not know the statement "Between"

Cor

"Daniel" <danielcre...@g mail.comschreef in berichtnews:11* *************** ******@i42g2000 cwa.googlegroup s.com...

In Microsoft Access I can write a query that includes the criteria:

Between Date()-7 And Date()

to retrieve the records from a table that fall within the last week.

I'm trying to write a procedure to do this in VB.NET (2005 Express
Edition). But I always get the message that the Jet database engine
does not recognize the syntax (of the Date function I assume). I've
also tried Now() and it works but only by itself. I seem to add or
subtract time from it.

What's the best syntax to use? I'm working in the Query Designer.

Thanks!
Daniel

[rowe_newsgroups]

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date

=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and tblDates.entry_ date < " &

now().tostring( )

Oops, left out an equals sign:

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date

>=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and tblDates.entry_ date <= " & now().tostring( )

Thanks,

Seth Rowe
rowe_newsgroups wrote:

How about using the following as you're query:

<pseudocode>

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date

=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and tblDates.entry_ date < " & now().tostring( )

</pseudocode>

Thanks,

Seth Rowe
Daniel wrote:

Well, in Access I would write this query to get the results I want:

SELECT tblDates.* FROM tblDates
WHERE tblDates.entry_ date BETWEEN Date()-7 And Date();

This uses the built in Date() function which retrieves the current
system date to pull everything from the "tblDates" table that has an
entry_date within the last 7 days.

How do I write this query in VB 2005 Express in the Query Designer? I
am working with an OLEDB connection object linked to my Access 2003
(Microsoft Jet 4.0) database. The problem seems to be that when the
TableAdapter object tries to query the Jet db engine, the Jet engine
returns an error that it doesn't know what the Date() function is. I
thought the whole point of the adapter object was to take your query
and translate it into syntax compatible with the type of database you
are querying. But instead, it seem to be passing the literal value of
my query directly to Jet.

Thanks so much!

On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:

Daniel,
>
Can you show us the query (is it an SQL procedure), than we get probably
better at your problem.
>
I do by instance not know the statement "Between"
>
Cor
>
"Daniel" <danielcre...@g mail.comschreef in berichtnews:11* *************** ******@i42g2000 cwa.googlegroup s.com...
>
In Microsoft Access I can write a query that includes the criteria:
>
Between Date()-7 And Date()
>
to retrieve the records from a table that fall within the last week.
>
I'm trying to write a procedure to do this in VB.NET (2005 Express
Edition). But I always get the message that the Jet database engine
does not recognize the syntax (of the Date function I assume). I've
also tried Now() and it works but only by itself. I seem to add or
subtract time from it.
>
What's the best syntax to use? I'm working in the Query Designer.
>
Thanks!
Daniel

[Cor Ligthert [MVP]]

Rowe,

Only as addition: and than with parameters.

http://www.vb-tips.com/dbpages.aspx?...3-eb8b44af0137

Cor

"rowe_newsgroup s" <ro********@yah oo.comschreef in bericht
news:11******** **************@ b28g2000cwb.goo glegroups.com.. .

>SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date

>=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
tblDates.entry _date < " &

now().tostring ()

Oops, left out an equals sign:

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date

>>=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
tblDates.entr y_date <= " & now().tostring( )

Thanks,

Seth Rowe
rowe_newsgroups wrote:

>How about using the following as you're query:

<pseudocode>

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date

>=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
tblDates.entry _date < " & now().tostring( )

</pseudocode>

Thanks,

Seth Rowe
Daniel wrote:

Well, in Access I would write this query to get the results I want:

SELECT tblDates.* FROM tblDates
WHERE tblDates.entry_ date BETWEEN Date()-7 And Date();

This uses the built in Date() function which retrieves the current
system date to pull everything from the "tblDates" table that has an
entry_date within the last 7 days.

How do I write this query in VB 2005 Express in the Query Designer? I
am working with an OLEDB connection object linked to my Access 2003
(Microsoft Jet 4.0) database. The problem seems to be that when the
TableAdapter object tries to query the Jet db engine, the Jet engine
returns an error that it doesn't know what the Date() function is. I
thought the whole point of the adapter object was to take your query
and translate it into syntax compatible with the type of database you
are querying. But instead, it seem to be passing the literal value of
my query directly to Jet.

Thanks so much!

On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:
Daniel,

Can you show us the query (is it an SQL procedure), than we get
probably
better at your problem.

I do by instance not know the statement "Between"

Cor

"Daniel" <danielcre...@g mail.comschreef in
berichtnews:11* *************** ******@i42g2000 cwa.googlegroup s.com...

In Microsoft Access I can write a query that includes the criteria:

Between Date()-7 And Date()

to retrieve the records from a table that fall within the last
week.

I'm trying to write a procedure to do this in VB.NET (2005 Express
Edition). But I always get the message that the Jet database engine
does not recognize the syntax (of the Date function I assume). I've
also tried Now() and it works but only by itself. I seem to add or
subtract time from it.

What's the best syntax to use? I'm working in the Query Designer.

Thanks!
Daniel

[Daniel]

Thanks. I could do this from code. But my question is: How do you enter
parameters that are created on the fly by a function call in the query
designer?

I know if Access can do it, .NET can do it too...somehow!


On Oct 13, 12:50 pm, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:

Rowe,

Only as addition: and than with parameters.

http://www.vb-tips.com/dbpages.aspx?...ff-aaa3-eb8b44...

Cor

"rowe_newsgroup s" <rowe_em...@yah oo.comschreef in berichtnews:11* *************** ******@b28g2000 cwb.googlegroup s.com...

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date
=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
tblDates.entry_ date < " &
now().tostring( )

Oops, left out an equals sign:

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date

>=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
tblDates.entry _date <= " & now().tostring( )

Thanks,

Seth Rowe

rowe_newsgroups wrote:

How about using the following as you're query:

<pseudocode>

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date
=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
tblDates.entry_ date < " & now().tostring( )

</pseudocode>

Thanks,

Seth Rowe

Daniel wrote:
Well, in Access I would write this query to get the results I want:

SELECT tblDates.* FROM tblDates
WHERE tblDates.entry_ date BETWEEN Date()-7 And Date();

This uses the built in Date() function which retrieves the current
system date to pull everything from the "tblDates" table that has an
entry_date within the last 7 days.

How do I write this query in VB 2005 Express in the Query Designer? I
am working with an OLEDB connection object linked to my Access 2003
(Microsoft Jet 4.0) database. The problem seems to be that when the
TableAdapter object tries to query the Jet db engine, the Jet engine
returns an error that it doesn't know what the Date() function is. I
thought the whole point of the adapter object was to take your query
and translate it into syntax compatible with the type of database you
are querying. But instead, it seem to be passing the literal value of
my query directly to Jet.

Thanks so much!

On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:
Daniel,

Can you show us the query (is it an SQL procedure), than we get
probably
better at your problem.

I do by instance not know the statement "Between"

Cor

"Daniel" <danielcre...@g mail.comschreef in
berichtnews:11* *************** ******@i42g2000 cwa.googlegroup s.com...

In Microsoft Access I can write a query that includes the criteria:

Between Date()-7 And Date()

to retrieve the records from a table that fall within the last
week.

I'm trying to write a procedure to do this in VB.NET (2005 Express
Edition). But I always get the message that the Jet database engine
does not recognize the syntax (of the Date function I assume). I've
also tried Now() and it works but only by itself. I seem to add or
subtract time from it.

What's the best syntax to use? I'm working in the Query Designer.

Thanks!
Daniel

[Cor Ligthert [MVP]]

Daniel,

In the same way, but not in the designer of course.

Use the intelisence for that, to find the names of your paremeters.

Cor

"Daniel" <da**********@g mail.comschreef in bericht
news:11******** *************@f 16g2000cwb.goog legroups.com...

Thanks. I could do this from code. But my question is: How do you enter
parameters that are created on the fly by a function call in the query
designer?

I know if Access can do it, .NET can do it too...somehow!


On Oct 13, 12:50 pm, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:

>Rowe,

Only as addition: and than with parameters.

http://www.vb-tips.com/dbpages.aspx?...ff-aaa3-eb8b44...

Cor

"rowe_newsgrou ps" <rowe_em...@yah oo.comschreef in
berichtnews:11 *************** *******@b28g200 0cwb.googlegrou ps.com...

>SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date
=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
tblDates.entry _date < " &
now().tostring ()

Oops, left out an equals sign:

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date
=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
tblDates.entr y_date <= " & now().tostring( )

Thanks,

Seth Rowe

rowe_newsgroups wrote:
How about using the following as you're query:

><pseudocode>

>SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date
=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
tblDates.entry _date < " & now().tostring( )

></pseudocode>

>Thanks,

>Seth Rowe

>Daniel wrote:
Well, in Access I would write this query to get the results I want:

SELECT tblDates.* FROM tblDates
WHERE tblDates.entry_ date BETWEEN Date()-7 And Date();

This uses the built in Date() function which retrieves the current
system date to pull everything from the "tblDates" table that has an
entry_date within the last 7 days.

How do I write this query in VB 2005 Express in the Query Designer?
I
am working with an OLEDB connection object linked to my Access 2003
(Microsoft Jet 4.0) database. The problem seems to be that when the
TableAdapter object tries to query the Jet db engine, the Jet engine
returns an error that it doesn't know what the Date() function is. I
thought the whole point of the adapter object was to take your query
and translate it into syntax compatible with the type of database
you
are querying. But instead, it seem to be passing the literal value
of
my query directly to Jet.

Thanks so much!

On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:
Daniel,

Can you show us the query (is it an SQL procedure), than we get
probably
better at your problem.

I do by instance not know the statement "Between"

Cor

"Daniel" <danielcre...@g mail.comschreef in
berichtnews:11* *************** ******@i42g2000 cwa.googlegroup s.com...

In Microsoft Access I can write a query that includes the
criteria:

Between Date()-7 And Date()

to retrieve the records from a table that fall within the last
week.

I'm trying to write a procedure to do this in VB.NET (2005
Express
Edition). But I always get the message that the Jet database
engine
does not recognize the syntax (of the Date function I assume).
I've
also tried Now() and it works but only by itself. I seem to add
or
subtract time from it.

What's the best syntax to use? I'm working in the Query
Designer.

Thanks!
Daniel

[Daniel]

So you're saying that it's not possible to enter a function call as an
SQL query parameter in VB's Query Designer?

On Oct 13, 1:35 pm, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:

Daniel,

In the same way, but not in the designer of course.

Use the intelisence for that, to find the names of your paremeters.

Cor

"Daniel" <danielcre...@g mail.comschreef in berichtnews:11* *************** *****@f16g2000c wb.googlegroups .com...

Thanks. I could do this from code. But my question is: How do you enter
parameters that are created on the fly by a function call in the query
designer?

I know if Access can do it, .NET can do it too...somehow!

On Oct 13, 12:50 pm, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:

Rowe,

Only as addition: and than with parameters.

>http://www.vb-tips.com/dbpages.aspx?...ff-aaa3-eb8b44...

Cor

"rowe_newsgroup s" <rowe_em...@yah oo.comschreef in
berichtnews:11* *************** ******@b28g2000 cwb.googlegroup s.com...

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date
=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
tblDates.entry_ date < " &
now().tostring( )

Oops, left out an equals sign:

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date
=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
tblDates.entry _date <= " & now().tostring( )

Thanks,

Seth Rowe

rowe_newsgroups wrote:
How about using the following as you're query:

<pseudocode>

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date
=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
tblDates.entry_ date < " & now().tostring( )

</pseudocode>

Thanks,

Seth Rowe

Daniel wrote:
Well, in Access I would write this query to get the results I want:

SELECT tblDates.* FROM tblDates
WHERE tblDates.entry_ date BETWEEN Date()-7 And Date();

This uses the built in Date() function which retrieves the current
system date to pull everything from the "tblDates" table that has an
entry_date within the last 7 days.

How do I write this query in VB 2005 Express in the Query Designer?
I
am working with an OLEDB connection object linked to my Access 2003
(Microsoft Jet 4.0) database. The problem seems to be that when the
TableAdapter object tries to query the Jet db engine, the Jet engine
returns an error that it doesn't know what the Date() function is. I
thought the whole point of the adapter object was to take your query
and translate it into syntax compatible with the type of database
you
are querying. But instead, it seem to be passing the literal value
of
my query directly to Jet.

Thanks so much!

On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:
Daniel,

Can you show us the query (is it an SQL procedure), than we get
probably
better at your problem.

I do by instance not know the statement "Between"

Cor

"Daniel" <danielcre...@g mail.comschreef in
berichtnews:11* *************** ******@i42g2000 cwa.googlegroup s.com...

In Microsoft Access I can write a query that includes the
criteria:

Between Date()-7 And Date()

to retrieve the records from a table that fall within the last
week.

I'm trying to write a procedure to do this in VB.NET (2005
Express
Edition). But I always get the message that the Jet database
engine
does not recognize the syntax (of the Date function I assume).
I've
also tried Now() and it works but only by itself. I seem to add
or
subtract time from it.

What's the best syntax to use? I'm working in the Query
Designer.

Thanks!
Daniel

[rowe_newsgroups]

It may be possible (I don't know for sure - I don't use the designer),
but why would you? Personally, I like to have direct control over what
goes in to my programs, it increases you knowledge of the language
(instead of the ide) and can also prevent subtle errors from creeping
into your code. The designer doesn't know everything your project does
and might generate conflicting or inefficient code.

Just my thoughts,

Seth Rowe
Daniel wrote:

So you're saying that it's not possible to enter a function call as an
SQL query parameter in VB's Query Designer?

On Oct 13, 1:35 pm, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:

Daniel,

In the same way, but not in the designer of course.

Use the intelisence for that, to find the names of your paremeters.

Cor

"Daniel" <danielcre...@g mail.comschreef in berichtnews:11* *************** *****@f16g2000c wb.googlegroups .com...

Thanks. I could do this from code. But my question is: How do you enter
parameters that are created on the fly by a function call in the query
designer?

I know if Access can do it, .NET can do it too...somehow!

On Oct 13, 12:50 pm, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:
>Rowe,

>Only as addition: and than with parameters.

>>http://www.vb-tips.com/dbpages.aspx?...ff-aaa3-eb8b44...

>Cor

>"rowe_newsgrou ps" <rowe_em...@yah oo.comschreef in
>berichtnews:11 *************** *******@b28g200 0cwb.googlegrou ps.com...

>SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date
>=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
>tblDates.entry _date < " &
>now().tostring ()

Oops, left out an equals sign:

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date
>>=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
>>tblDates.entr y_date <= " & now().tostring( )

Thanks,

Seth Rowe

rowe_newsgroups wrote:
>How about using the following as you're query:

><pseudocode>

>SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_ date
>=" & DateAdd(DateInt erval.Day, -7, Now()).ToString () & " and
>tblDates.entry _date < " & now().tostring( )

></pseudocode>

>Thanks,

>Seth Rowe

>Daniel wrote:
Well, in Access I would write this query to get the results I want:

SELECT tblDates.* FROM tblDates
WHERE tblDates.entry_ date BETWEEN Date()-7 And Date();

This uses the built in Date() function which retrieves the current
system date to pull everything from the "tblDates" table that has an
entry_date within the last 7 days.

How do I write this query in VB 2005 Express in the Query Designer?
I
am working with an OLEDB connection object linked to my Access 2003
(Microsoft Jet 4.0) database. The problem seems to be that when the
TableAdapter object tries to query the Jet db engine, the Jet engine
returns an error that it doesn't know what the Date() function is. I
thought the whole point of the adapter object was to take your query
and translate it into syntax compatible with the type of database
you
are querying. But instead, it seem to be passing the literal value
of
my query directly to Jet.

Thanks so much!

On Oct 12, 11:43 pm, "Cor Ligthert [MVP]" <notmyfirstn... @planet.nl>
wrote:
Daniel,

Can you show us the query (is it an SQL procedure), than we get
probably
better at your problem.

I do by instance not know the statement "Between"

Cor

"Daniel" <danielcre...@g mail.comschreef in
berichtnews:11* *************** ******@i42g2000 cwa.googlegroup s.com...

In Microsoft Access I can write a query that includes the
criteria:

Between Date()-7 And Date()

to retrieve the records from a table that fall within the last
week.

I'm trying to write a procedure to do this in VB.NET (2005
Express
Edition). But I always get the message that the Jet database
engine
does not recognize the syntax (of the Date function I assume).
I've
also tried Now() and it works but only by itself. I seem to add
or
subtract time from it.

What's the best syntax to use? I'm working in the Query
Designer.

Thanks!
Daniel