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]

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!

 

[rowe_newsgroups]

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

 

[rowe_newsgroups]

SQLstring = "SELECT tblDates.* FROM tblDates WHERE 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

 

[Cor Ligthert [MVP]]

Rowe,

Only as addition: and than with parameters.

http://www.vb-tips.com/dbpages.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

Cor

 

[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!

 

[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]

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

 

[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]

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."