By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,689 Members | 1,787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,689 IT Pros & Developers. It's quick & easy.

date criteria in VB Express query

P: n/a
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

Oct 12 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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**********@gmail.comschreef in bericht
news:11**********************@i42g2000cwa.googlegr oups.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

Oct 13 '06 #2

P: n/a
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...@gmail.comschreef in berichtnews:11**********************@i42g2000cwa.g ooglegroups.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
Oct 13 '06 #3

P: n/a
How about using the following as you're query:

<pseudocode>

SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date
>=" & DateAdd(DateInterval.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...@gmail.comschreef in berichtnews:11**********************@i42g2000cwa.g ooglegroups.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
Oct 13 '06 #4

P: n/a
SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date
=" & DateAdd(DateInterval.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(DateInterval.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(DateInterval.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...@gmail.comschreef in berichtnews:11**********************@i42g2000cwa.g ooglegroups.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
Oct 13 '06 #5

P: n/a
Rowe,

Only as addition: and than with parameters.

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

Cor

"rowe_newsgroups" <ro********@yahoo.comschreef in bericht
news:11**********************@b28g2000cwb.googlegr oups.com...
>SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date
>=" & DateAdd(DateInterval.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(DateInterval.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(DateInterval.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...@gmail.comschreef in
berichtnews:11**********************@i42g2000cwa.g ooglegroups.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

Oct 13 '06 #6

P: n/a
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_newsgroups" <rowe_em...@yahoo.comschreef in berichtnews:11**********************@b28g2000cwb.g ooglegroups.com...
SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date
=" & DateAdd(DateInterval.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(DateInterval.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(DateInterval.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...@gmail.comschreef in
berichtnews:11**********************@i42g2000cwa.g ooglegroups.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
Oct 13 '06 #7

P: n/a
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**********@gmail.comschreef in bericht
news:11*********************@f16g2000cwb.googlegro ups.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_newsgroups" <rowe_em...@yahoo.comschreef in
berichtnews:11**********************@b28g2000cwb. googlegroups.com...
>SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date
=" & DateAdd(DateInterval.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(DateInterval.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(DateInterval.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...@gmail.comschreef in
berichtnews:11**********************@i42g2000cwa.g ooglegroups.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

Oct 13 '06 #8

P: n/a
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...@gmail.comschreef in berichtnews:11*********************@f16g2000cwb.go oglegroups.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_newsgroups" <rowe_em...@yahoo.comschreef in
berichtnews:11**********************@b28g2000cwb.g ooglegroups.com...
SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date
=" & DateAdd(DateInterval.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(DateInterval.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(DateInterval.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...@gmail.comschreef in
berichtnews:11**********************@i42g2000cwa.g ooglegroups.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
Oct 13 '06 #9

P: n/a
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...@gmail.comschreef in berichtnews:11*********************@f16g2000cwb.go oglegroups.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_newsgroups" <rowe_em...@yahoo.comschreef in
>berichtnews:11**********************@b28g2000cwb. googlegroups.com...
>SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date
>=" & DateAdd(DateInterval.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(DateInterval.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(DateInterval.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...@gmail.comschreef in
berichtnews:11**********************@i42g2000cwa.g ooglegroups.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
Oct 13 '06 #10

P: n/a
There are times when typing out a SQL statement in code is just fine.
But when I'm working with joining several tables or need to enter a lot
of different criteria, I would MUCH rather use a visual tool for query
design. I think a visual method is much more apt to prevent subtle
errors than trying to "picture" the layout and relationships of every
object in your head while you type out SELECT statement after SELECT
statement.

Besides, I think hand coding will become increasingly minimized and
everything will go to a more visual approach in the programming world
as time goes on. We don't feel the need to get into the code that
adjusts pixel depth when editing a picture in Photoshop or do all HTML
coding in Notepad anymore. You don't have to jump in there and work in
Assembly or machine language everytime you need to communicate with
hardware anymore. And there is no real need to make a pie crust from
scratch anymore when you can buy one at the store and cut out all that
manual work (...and know it will taste good!).

As they say on the Ask.com commercial: "Use tools. Feel human."

On Oct 13, 6:35 pm, "rowe_newsgroups" <rowe_em...@yahoo.comwrote:
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...@gmail.comschreef in berichtnews:11*********************@f16g2000cwb.go oglegroups.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_newsgroups" <rowe_em...@yahoo.comschreef in
berichtnews:11**********************@b28g2000cwb.g ooglegroups.com...
SQLstring = "SELECT tblDates.* FROM tblDates WHERE tblDates.entry_date
=" & DateAdd(DateInterval.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(DateInterval.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(DateInterval.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...@gmail.comschreef in
berichtnews:11**********************@i42g2000cwa.g ooglegroups.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
Oct 19 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.