473,323 Members | 1,589 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

date criteria in VB Express query

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
10 3248
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Kenneth | last post by:
I have a Query that consist of a lot of different sales data, and one of the colums are different date. The date goes from 1jan2003 til 31jan2003. in this Query I only want the salesdata for...
1
by: Sunil Korah | last post by:
Hi, My table contains a date field "DateOfVisit" and I have the following query SELECT Patients.PID, Patients.Lastname, Patients.FirstName, Patients.DateOfVisit, Patients.ToReport FROM...
12
by: Steve Elliott | last post by:
I have a query set up to gather together data between two specified dates. Shown in the query column as: Between #24/09/2004# And #01/10/2004# Is it possible to enter several different date...
2
by: Doug1962 | last post by:
I would like to run a query every date to extract only the records that have been added to the database within my criteria for a 24 hour period. I.E. I would like the query to extract records...
3
by: bcaponet | last post by:
I have a form where a user enters a date that I will then base a query on. In the past, I have simply placed Forms!! into the criteria for a query and it runs as long as the form is open. The...
1
by: John Feeley | last post by:
am tring to add a number of years to a dob. im doing this by adding my date+years*365.26 I get a string of numbers. I then convert the number in the next column to actual date again. I'm getting...
2
by: Julie Wardlow | last post by:
Help! I am calculating a future date using the DateAdd function in a query (the calculation also involves an IIf statement), and have managed to get this formula to produce the required result....
1
by: flumpuk | last post by:
Hi My job currently requires me to enter data from 300+ forms a month. The system which we used in Excel was slow , and theprevious guy had three workbooks for this job . I have created...
8
by: Dr Al | last post by:
I have a table with four date fields, some of which may not be filled in based on our data entry needs. I have a criteria set as <date()-180 which is supposed to pull dates older than 180 days ago....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.