MS Access Query - SQL

[Askeladden]

I am trying, by using SQL in my VB 6.0 program, in combination with Access, to read a field Forfall, which has a datatype of DATETIME. In my program I need to list all dates (but, just the date!) that are equal to the systemdate (of host machine) and excludes the field Kvitter ut with an occurance of 'x' in it.

The SQL code i have so far is:

SELECT Forfall, Ansvarlig, Eiendom, Bygg, Anlegg, [Ordre Beskrivelse], Merknader, [Interval Nummer], [Interval Gang], Fritekst2, Fritekst3, [Kvitter ut], ID
FROM [Vedlikeholds Rutiner]
WHERE [Kvitter ut] <> 'x'
ORDER BY Forfall ASC

This works, but only by listing entries that doesn't have x in the Kvitter ut field. I need to code it so that it also limits the entries listing to those that have todays date (i.e. the host computers systemdate).
This code I am not sure of since I am not very familiar with SQL.
Can someone please help in this?
Thanks!

Christian

 

[RoyVidar]

Hei Askeladden!

You'd be doing this in code? Perhaps something like this might do? I suppose the format function is available also in VB? Perhaps something like this might work?

[tt] ...
"FROM [Vedlikeholds Rutiner] " & _
"WHERE [Kvitter ut] <> 'x' AND " & _
"format(forfall,'mm/dd/yyyy') = #" & format(date,"mm/dd/yyyy") & _
"# ORDER BY Forfall ASC"[/tt]

To only display the "date" part of Forfall, try some formatting, there too (the select clause).

Roy-Vidar

 

[RoyVidar]

Just forgot, to format the Forfall field in the select, the format function would probably return a string, so perhaps:

[tt]"select cdate(int([Forfall])), ..."[/tt]

- or the datevalue function, which both should return a date according to regional settings

Roy-Vidar

 

[r937]

use this:

where Forfall >= date()
  and Forfall < dateadd("d",1,date())

this will ensure that the index on Forfall (if any) will be used



rudy

http://r937.com/

SQL Consulting